How to Create Excel Drop Down List with Color (2 Ways)

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.

Sample Dataset of Excel Drop Down List with Color


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.

Manually Create Excel Drop Down List with Color


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

Manually Create Excel Drop Down List with Color

Next, select the source.

⏩ I selected the source range G4:G9.

⏩ Finally, click OK.

Manually Create Excel Drop Down List with Color

➤ 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

Manually Create Excel Drop Down List with Color

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.

Manually Create Excel Drop Down List with Color

⏩ I selected the G4 cell which contains the color Green.

➤ Click on Format to set the color of the Specific Text.

Manually Create Excel Drop Down List with Color

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.

Manually Create Excel Drop Down List with Color

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.

Manually Create Excel Drop Down List with 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.

Manually Create Excel Drop Down List with Color

➤ 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


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.

Using Table in Excel Drop Down List with Color


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.

Using Table in Excel Drop Down List with Color

⏩ I selected the source range I4:I7.

⏩ Now, click OK.

Using Table in Excel Drop Down List with Color

➤ 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

Using Table in Excel Drop Down List with Color

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.

Using Table in Excel Drop Down List with Color

⏩ Finally, click OK.

Therefore, Data Validation is applied for the selected range.

Using Table in Excel Drop Down List with Color


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.

Using Table in Excel Drop Down List with Color

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 .

Using Table in Excel Drop Down List with Color

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.

Using Table in Excel Drop Down List with Color

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.

Using Table in Excel Drop Down List with Color

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.

Using Table in Excel Drop Down List with Color

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.

Using Table in Excel Drop Down List with Color

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)

Using Table in Excel Drop Down List with Color

A dialog box will appear.

Mark on My table has headers.

Then, click OK.

Here, you will see that the Table format is applied.

Using Table in Excel Drop Down List with Color

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.

Using Table in Excel Drop Down List with Color

➤ 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

Using Table in Excel Drop Down List with Color

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.

Practice Sheet for Excel Drop Down List with Color


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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo