In your dataset when you have particular values that you will need to use multiple times then the Excel drop-down list will be helpful. In most cases, you may need to use color code to represent a particular value. Especially for accessories, dresses, toys, etc., the color code adds more meaning to the dataset. In this article, I’m going to explain how you can create an Excel drop-down list with color.
To make the explanation clearer, I’m going to use a sample dataset of dress stores that represents the order, size, and color information of a particular dress. The dataset contains 4 columns these are Order ID, Dress, Available Color, and Available Size.
1. Creating Excel Drop Down List with Color Manually
By using the Excel Data Validation feature I will create the drop-down list later I will use the Conditional Formatting feature to color the drop-down list values.
Here, I will create the drop-down list of the Available Colors. The two steps are illustrated below.
Creating Drop Down List
To begin with, select the cell or cell range to apply Data Validation
⏩ I selected the cell range E4:E12.
Open the Data tab >> from Data Tools >> select Data Validation
A dialog box will pop up. From the validation criteria select the option you want to use in Allow.
⏩ I selected List
Next, select the source.
⏩ I selected the source range G4:G9.
⏩ Finally, click OK.
➤ Hence, Data Validation is applied for the selected range.
Read More: How to Make a Drop Down List in Excel
Color the Drop Down List
➤ As the drop-down list is created I will add color to the values of the drop-down list by using Conditional Formatting.
To begin with, select the cell range where Data Validation is applied already.
⏩ I selected the cell range E4:E12.
➤ Open the Home tab >> from Conditional Formatting >> select New Rule
A dialog box will pop up. From there select any rule from Select a Rule Type.
⏩ I selected the rule Format only cells that contain.
In Edit the Rule Description select the Format only cells with options.
⏩ I selected Specific Text.
➤ Now, select the cell address from the sheet that contains the Specific Text.
⏩ I selected the G4 cell which contains the color Green.
➤ Click on Format to set the color of the Specific Text.
Another dialog box will pop up. From there select the fill color of your choice.
⏩ I selected the color Green as my specific text is Green.
Then, click OK.
As all the New Formatting Rule is selected finally click OK again.
Therefore, the specific text Green is colored with green.
Now, every time you select the text Green from the drop-down list the cell will be colored with green color.
➤ Here you can follow the same procedure that I explained earlier to color the drop-down list values.
⏩ I colored all the drop-down list values with the corresponding color of the name.
➤ Now, each time you select any of the values from the drop-down list it will appear with the corresponding color in the cell.
2. Using Table Format in Excel to Create Drop Down List with Color
You may have a dynamic dataset where you insert data or values frequently in those cases you can use the Table Format so that whenever you insert data in the table the drop-down list with color will work for every new entry.
To demonstrate to you the process, I’m going to use the dataset mentioned value, where I will create two drop-down lists. One for Available Size and another for Available Colors.
Creating Drop Down List
To begin with, select the cell to apply Data Validation
⏩ I selected cell D4.
Open the Data tab >> from Data Tools >> select Data Validation
A dialog box will pop up. From validation criteria select a preferred option from Allow.
⏩ I selected List.
Next, select the Source from the sheet.
⏩ I selected the source range I4:I7.
⏩ Now, click OK.
➤ Hence, you will see Data Validation is applied for the selected range.
Again, select the cell to apply Data Validation.
⏩ I selected cell E4 to apply Data Validation for color.
Open the Data tab >> from Data Tools >> select Data Validation
A dialog box will pop up. From validation criteria select any option from Allow.
⏩ I selected List.
Next, select the Source from the sheet.
⏩ I selected the source range G4:G7.
⏩ Finally, click OK.
Therefore, Data Validation is applied for the selected range.
Color The Drop Down List
➤ As the drop-down list is created I will add color to the values of the drop-down list by using Conditional Formatting.
To begin with, select the cell range where Data Validation is applied already.
⏩ I selected cell D4.
➤ Open the Home tab >> go to Conditional Formatting >> from Highlight Cells Rules >> select Equal To
A dialog box will pop up. From there select any cell to apply Format cells that are EQUAL TO
⏩ I selected cell I4.
In with select the options of your choice.
⏩ I selected Green Fill with Dark Green Text.
Finally, click OK.
Hence, the size value from the drop-down list is coded with the selected color.
➤ Follow the same process I explained earlier to color the drop-down list value which is in the I5 cell.
Here, the I5 value is colored with the selected option.
➤ By following the process that I explained earlier color the drop down list values of Available Size.
Again, to color the drop-down values of Available Colors.
⏩ I selected cell E4.
➤ Open the Home tab >> go to Conditional Formatting >> from Highlight Cells Rules >> select Equal To
A dialog box will pop up. From there select any cell to apply Format cells that are EQUAL TO
⏩ I selected cell G4.
In with select the options of your choice.
⏩ I selected Custom Format.
Another dialog box will pop up. From there select the fill color of your choice.
⏩ I selected the color Green.
Then, click OK.
➤ Again, click OK.
Therefore, the selected color is applied to the drop-down list values.
➤ By following the process that I explained earlier colored all the values of Available Colors.
Now, each of the values will appear with a formatted color.
Using Table Format in Drop-Down List with Color
Here, I only have values for one row it may happen that I may need to insert a couple of entries later for that I’m going to format the dataset as Table.
First, select the cell range to format the range as Table.
⏩ I selected the cell range B3:E4.
Now, open the Home tab >> from Format as Table >> select any Format (I selected Light format)
A dialog box will appear.
⏩ Mark on My table has headers.
Then, click OK.
Here, you will see that the Table format is applied.
Now, insert new data in row 5 you will see that drop-down list is available.
➤ Select any value to check if the values come with colors or not.
Here, you will see a drop-down list with color available there too.
➤ For each entry drop-down list with color will be available.
⏩ If you don’t want the Filter option in the Table Header then you can remove it.
First, select the Table,
Then, open the Table Design tab >> from Table Style Options >> Unmark Filter Button
Hence, the table header filter button is removed.
Read More: Create Excel Drop Down List from Table
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained ways.
Download Practice Workbook
Conclusion
In this article, I have explained 2 ways to use an Excel drop-down list with color. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Related Articles
- How to Create a Drop Down List from Another Sheet in Excel
- How to Edit Drop Down List in Excel
- How to Remove Drop Down List in Excel
- How to Link a Cell Value with a Drop Down List in Excel
- How to Auto Update Drop-Down List in Excel
- How to Create Drop Down List with Filter in Excel
- How to Add Item to Drop-Down List in Excel
- How to Create a Drop Down List with Unique Values in Excel
- How to Copy Filter Drop-Down List in Excel
- Excel Drop Down List Not Working
<< Go Back to Create Drop-Down List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
