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 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.
Download to Practice
2 Ways to Use Excel Drop Down List with Color
1. Manually Create Excel Drop Down List with Color
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.
1.1. 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 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.
1.2. 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 then it will appear with the corresponding color in the cell.
Read more: Conditional Drop Down List in Excel
Similar Readings
- Create Drop Down List with Filter in Excel (7 Methods)
- How to Create List from Range in Excel (3 Methods)
- Create Dependent Drop Down List in Excel
- How to Create Drop Down List in Excel with Multiple Selections
2. Using Table in Excel 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.
2.1. 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.
2.2. 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 formatted color.
2.3. 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 is 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.
Conclusion
In this article, I have explained 2 ways to use 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.
Further Readings
- Multiple Dependent Drop-Down List Excel VBA (3 Ways)
- How to Make Multiple Selection from Drop Down List in Excel (3 Ways)
- Create Dynamic Dependent Drop Down List in Excel
- How to use IF Statement to Create a Drop-Down List in Excel