Often you may want to create a dropdown list in Excel with colors.
The following step-by-step example shows how to do so.
Step 1: Create Data
Suppose we have data in Excel on the number of points scored by a basketball player on a particular team and we would like to assign a rating of Good, OK, or Bad to the player:
Step 2: Define Dropdown List Options
Next, type Good, OK and Bad in the range F1:F3:
These will be used in the next step as the potential options that can be selected in the dropdown list.
Step 3: Create Dropdown List
Next, select cell C2, then click the Data tab along the top ribbon, then click the Data Validation icon within the Data Tools group:
In the new box that appears, select List from the Allow dropdown menu, then select F1:F3 for the Source:
Once you click OK, a dropdown list will automatically be added to cell C2 with Good, OK and Bad as the potential options.
Step 4: Add Colors to Dropdown List
Next, with cell C2 selected, click the Conditional Formatting icon on the Home tab along the top ribbon, then click New Rule:
In the new box that appears, click Format only cells that contain, then choose Specific Text under the Rule Description box, then choose =$F$1.
Then click the Format icon and choose a fill color to use. We will choose green:
Once you click OK, the “Good” option will be filled with a green background when it is selected from the dropdown list:
We can repeat this same process to specify a yellow background to be used when “OK” is selected:
And we can repeat the process again to specify a red background to be used when “Bad” is selected:
We have now successfully added colors to our dropdown list.
Note: If you click the Conditional Formatting icon on the Home tab and then click Manage Rules, you will be able to see all of the conditional formatting rules you have created:
The following tutorials explain how to perform other common tasks in Excel: