Do you want to add colour to a drop-down list in Excel? It’s very easy, create a drop-down list and add conditional formatting rules to the cell.
Color can be a powerful element in an Excel drop down list, on the upcoming lines we will see how to add a new list and validation control, and then add the conditional format rules to include visual cues.
How to create a drop down list in Excel?
Add a new sheet and then add a new list with the text items red, blue, green, and yellow
Create a drop down list in E4 using the color list as the source
To create a drop-down list in E4:
- Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.
- Select cell E4. (You can position the drop down list in most any cell or even multiple cells.)
- Choose Validation from the Data menu.
- Choose List from the Allow option’s drop down list. (See, they’re everywhere.)
- Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).
- Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won’t present a drop down list.
- Click OK.
How to add color to the drop down list?
To add a conditional rule that changes the font color to red when the selected list value is red, follow these steps.
- With E4 selected, click Conditional Formatting in the Styles group.
- 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.
- Click OK again. If you select red, the font color is red.
If you selected the other three list items, the font will still black. To change this, create a new rule for the other three using the same method.