Copyright infringement

How to add color to a drop down list in Excel

This tutorial shows the steps for adding color to a dropdown list in Microsoft Excel.

woman with spreadsheet

Photo: Kerkez, Getty Images/iStockphoto

Color can be a powerful element of an Excel dropdown list, and it’s easier to add than you think – you can simply add conditional formatting rules to the cell that contains the dropdown. Learn how to add a new menu and control validation, then add conditional formatting rules to include visual cues. You can work with your own data or download the .xlsx and . xls.

To get started, add a new sheet and then add a new list of text items in red, blue, green, and yellow (Figure A). Using the instructions from a previous Dropdown List article in Excel, create a Dropdown List in E4 using the Color List as the source. With the new menu in place, you’re ready to add conditional formatting.

Figure A

microsoftexceldropdownlistaddingcolors-figure-c.jpg

Add a new color list in Excel.

We will use a predefined rule. To add a conditional rule that changes the font color to red when the specified list value is red, follow these steps.

  • With E4 selected (in the new sheet with the color list), click Conditional Formatting in the Styles group (Home tab).
  • Choose a new rule from the submenu.
  • At the top, select the Format cells that contain only option.
  • In the bottom section, change the first dropdown setting (cell value) to Selected Text.
  • Change the second dropdown to a dropdown.
  • In the third control, enter =A1, the cell containing the text value in red.
  • Click the Format button, click the Font tab, choose the color red, and then click OK. Figure B Shows the format and format.
  • Click OK again. If you select red, the font color will be red, as shown in Figure C.

Figure B

microsoftexceldropdownlistcolor.jpg

Specify the terms and format.

Figure C

microsoftexceldropdownlistcolor-figure-e.jpg

The conditional formatting rule uses a red font when choosing red from the list.

When the other three menu items are selected, the line remains black. Create a new base for the other three using the above instructions while using the settings in Table A. You can also use the fill property to color the cell instead of the line.

Table A

blue

specific text

it contains

= A2

Green color

specific text

it contains

= A3

yellow

specific text

it contains

= A4

Adding color is a useful visual tool, but sometimes requirements change. For example, you may want to use one validation process to limit another; In other words, you want to set a condition to filter or restrict the list. We will cover this next article.

Get more tips about Excel

Read 56 Excel tips every user should master and tutorials on how to add a condition to a dropdown list in Excel, how to create an Excel dropdown list from another tab, how to change Excel conditional formatting on the fly, and how to combine the VLOOKUP() function in Excel with the edit box and narration to improve the search. Also check out these free PDF download kits, Build your Excel skills with these 10 powerful tips and 13 handy Excel data entry shortcuts.