Drop down list in Excel with Conditional formatting

How to Create a Drop-Down Box With Color

One way to streamline data entry is to create custom lists in Excel and use them to create drop-down lists in specified cells. When you use the program's Data Validation tool, along with its Conditional Formatting feature, you can make a drop-down list that is color-coded, enabling you to determine data at a glance. You can customize the list colors to coordinate with your logo, topic or other data.

1

Create a list in an unused or hidden column of your spreadsheet. Enter the items into consecutive cells in a single column, one list item per cell. Leave no blank cells in-between.

2

Click on the cell or select the range of cells in which you want to add the drop-down list. Select the "Data" tab, click "Data Validation" and choose "Data Validation." Choose "List" in the Allow section. Click inside the "Source" box and select the cells containing the list you created. Click "OK."

3

Make sure the cell or the range of cells in which you want to add the drop-down list is still selected and click on the "Home" tab. Select "Conditional Formatting" in the Styles group.

4

Point to "Highlight Cells Rules" and select "More Rules." The New Formatting Rule dialog opens.

5

Choose "Format Only Cells that Contain" in the Select a Rule Type list. Choose "Specific Text" in the Format Only Cells With list. Enter the first list item in the third box and click the "Format" button.

6

Select the "Fill" tab of the Format Cells box. Choose the color with which you want to fill the cell, if the first list item is selected. For instance, if your list consists of the options "High," "Average" and "Low," you might want to shade cells with the word "High" in green, "Average" in yellow and "Low" in red.

7

Click "OK" to apply the formatting. Format the cells again with the second list item and continue until you have added a rule and a color for each item on the list. Save the worksheet before closing Excel. When a user chooses an option from the drop-down list, it will automatically fill the cell with the appropriate color.

Related Articles