Table of Contents
In order to create a drop down list with different colors in Excel, you will first need to select the cells where you want the drop down list to appear. Then, go to the “Data” tab and click on the “Data Validation” option. In the dialogue box that appears, select “List” as the validation criteria and enter the different color options in the “Source” field, separated by commas. Next, click on the “Cell Range” button and select the cells where you want the drop down list to appear. Finally, click “OK” to save the changes. This will create a drop down list with different colors in the selected cells, allowing you to easily select and change the color of your data.
Excel: Create a Drop Down List with Color
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
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:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: