How to Create Excel Drop Down List With Color?

(Note: This guide on how to create excel drop down list with color is suitable for all Excel versions including Office 365)

Sometimes in Excel, you will have to deal with a lot of data. You will also have to enter, correct, and perform some operations on them. In most cases, the data will mostly be redundant, and typing them over and over might sound like too much work.

In such cases, you can use a dropdown to easily select the predestined values. But additionally, this too becomes redundant when the data is vast. In these cases, adding a different color to the dropdown options helps you with easy differentiation of the data. In addition to minimizing the effort taken to fill the cell, it also provides additional validation and helps the user select the pre-defined values only. 

More importantly, adding a color grading to each data entry will help provide a visual cue to the reader and ascertain things in a better way when there are multiple data.

In this article, I will tell you how to create an Excel drop down list with color along with an example.

You’ll Learn:

• Example
• How to Create an Excel Drop Down?
• How to Add Colors to the Excel Drop Down List?
• Create an Excel Drop Down List With Colors for All Cells

Related Reads:

How to Add Page Numbers in Excel? 2 Easy with Options

How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps

How to Split and Combine Data in Excel?

Example

There are several instances where a dropdown for values has proven to be very useful in Excel for academic, corporate, and personal data management.

Consider the example, where you are given a list of students who have recently taken a test in one of the four centers in a city. You have to fill in the details which will help the management know about the students’ performance.

The table contains 5 columns – serial number, name, test center, grade, and result. The first two columns are to be manually entered as they will have different data for each entry. But the third, fourth, and fifth columns can only take certain values. 

The test centers are located in four locations in the city and can only be East, West, North, or South. The grading system is only limited to A, B, C, D, E, and F. And, the final result can only be a Pass or a Fail.

How to Create an Excel Drop Down?

Before we learn how to add color to the dropdown list, let us first see how to add a dropdown list in Excel.

• First, select the cell where you want to add the dropdown list.

• Navigate to Data and click on Data Validation.

• This opens up the Data Validation dialog box. From the Allow: dropdown, select List.

• In the Source textbox, you can add the options for the dropdown separated by a comma.

• Or, you can click on the upward arrow and select the options for the dropdown. It is a good practice to keep your options in a separate sheet and link them to the options. Click OK.

• You can now see the selected cell appearing with the dropdown. Once you have added the dropdown list for one column, you can do the same for other columns.

Note: If you want to apply the drop-down to multiple cells either in columns or rows, select the group of cells and then use the Data Validation option to add options in the dropdown.

Also Read:

How to Transpose Excel Data? 2 Easy Methods

Importing and Cleaning Data in Excel

How to Auto Fill Data in Microsoft Excel 2016

How to Add Colors to the Excel Drop Down List?

Now that we have seen how to add a drop-down list in Excel, let us see how to add colors to the drop-down list in Excel.

• Navigate to Home. Under Styles, click on the dropdown from Conditional Formatting. Again, click on Highlight Cell Rules and select Equal To. This means that when the particular value is selected, Excel selects the particular color assigned.

• This opens the Equal To dialog box where you can assign a particular color for a particular text. In the first text box, enter the value to which you want to assign a color. 

• Assign the color to the entered value using the dropdown on the left. Excel shows some default ideas for color schemes in the drop-down. 

• If you are not satisfied with the color schemes Excel offers, you can change them manually using the Custom Format option from the dropdown.

• Selecting Custom Format opens the Format Cells dialog box. 

• To change the background color, navigate to the Fill section and select the background color. 
• To change the color of the text, navigate to the Font section of the Format Cells dialog box and choose your desired color. 

• Click OK.

In this case, when we select North from the dropdown, the text will be in white and the background will be red. 

Note: When you select colors, it is better if you select the cells and then assign the colors using the Conditional Formatting option.

Create an Excel Drop Down List With Colors for All Cells

We now know how to add a drop-down to the cells with colors. But, you cannot do the same process for every cell as it will be more time-consuming and non-constructive. 

• One way to add a drop-down is to select multiple cells you want to add a drop-down to, and then navigate to Data Validation and choose the options for the dropdown. 

• Another way to create an Excel drop-down for the upcoming cells is to create a drop-down list for one cell in each column and convert them into a table.

• To convert the cells into a table and apply the dropdown for all the cells, select the cells which are formatted with dropdown and colors.

• Now, press Ctrl+T. This converts the selected cells into a table and any entry you make after them will have the same formatting and validations.

• In the Create Table dialog box, select the data and check the checkbox for My table has headers and click OK.

• Now, navigate to Table Design in the main menu and uncheck the checkbox for Filter to remove filters.
• Extend the table using the drag handle in the bottom rightmost cell to extend the table.
• Now, you can enter the data in the table one after the other using the dropdown with colors.

Suggested Reads:

How to Save Excel as PDF? 5 Useful Ways

How to Find and Replace in Excel? A Step-by-Step Guide

How to Round Up in Excel(and Round Down)?

Frequently Asked Questions

Closing Thoughts

Data Validation in addition to color coding is a powerful tool in Excel. If you want to know more about dropdowns and data validation, read our article on dynamic dropdowns in Excel. 

Please visit our free resources center for more high-quality Excel guides. 

Ready to take the next step and hone your skills in Excel?

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!