This tutorial shows the steps for adding color to a drop down list in Microsoft Excel.
Color can be a powerful element in an Excel drop down list, and it's easier to add than you might think—you simply add conditional formatting rules to the cell containing the drop down list. Learn how to add a new list and validation control, and then add the conditional format rules to include visual cues. You can work with your own data or download the demonstration .xlsx and .xls files.
To begin, add a new sheet and then add a new list with the text items red, blue, green, and yellow (Figure A). Using the instructions from a previous Excel drop down list article, create a drop down list in E4 using the color list as the source. With the new list in place, you're ready to add the conditional formatting.
We're going to use a pre-defined rule. To add a conditional rule that changes the font color to red when the selected list value is red, follow these steps.
- With E4 (in the new sheet with the color list) selected, click Conditional Formatting in the Styles group (Home tab).
- Choose New Rule from the submenu.
- In the top pane, select the Format Only Cells That Contain option.
- In the lower section, change the first drop down setting (Cell Value) to Specific Text.
- Change the second drop down to containing.
- In the third control, enter =A1, the cell that contains the text value red.
- Click the Format button, click the Font tab, choose red, and click OK. Figure B shows the formula and the format.
- Click OK again. If you select red, the font color is red, as shown in Figure C.
When selecting the other three list items, the font is still black. Create a new rule for the other three using the instructions above while using the settings in Table A. You could also use the fill property to color the cell instead of the font.
Adding color is a helpful visual tool, but sometimes requirements change. For example, you might want to use one validation to limit another; in other words, you want to set a condition to filter or limit the list. We'll cover this is the next article.
Get more Excel tips
Read 56 Excel tips every user should master and the tutorials how to add a condition to a drop down list in Excel, how to create an Excel drop down list from another tab, how to change an Excel conditional format on the fly, and how to combine Excel's VLOOKUP() function with a combo box for enhanced searching. Also, check out these free PDF download compilations Build your Excel skills with these 10 power tips and 13 handy Excel data entry shortcuts.