Do you want to add colour to a drop-down list in Excel? It’s very easy, create a drop-down list and add conditional formatting rules to the cell.
Color can be a powerful element in an Excel drop down list, on the upcoming lines we will see how to add a new list and validation control, and then add the conditional format rules to include visual cues.
How to create a drop down list in Excel?
Add a new sheet and then add a new list with the text items red, blue, green, and yellow
Create a drop down list in E4 using the color list as the source
To create a drop-down list in E4:
- Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.
- Select cell E4. [You can position the drop down list in most any cell or even multiple cells.]
- Choose Validation from the Data menu.
- Choose List from the Allow option’s drop down list. [See, they’re everywhere.]
- Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference [=$A$1:$A$4].
- Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values [A1:A4], but it won’t present a drop down list.
- Click OK.
How to add color to the drop down list?
To add a conditional rule that changes the font color to red when the selected list value is red, follow these steps.
- With E4 selected, click Conditional Formatting in the Styles group.
- Choose New Rule from the submenu.
- In the top pane, select the Format Only Cells That Contain option.
- In the lower section, change the first drop-down setting [Cell Value] to Specific Text.
- Change the second drop-down to containing.
- In the third control, enter =A1, the cell that contains the text value red.
- Click the Format button, click the Font tab, choose red, and click OK.
- Click OK again. If you select red, the font color is red.
If you selected the other three list items, the font will still black. To change this, create a new rule for the other three using the same method.
Related Articles
One way to streamline data entry is to create custom lists in Excel and use them to create drop-down lists in specified cells. When you use the program's Data Validation tool, along with its Conditional Formatting feature, you can make a drop-down list that is color-coded, enabling you to determine data at a glance. You can customize the list colors to coordinate with your logo, topic or other data.
-
Create a list in an unused or hidden column of your spreadsheet. Enter the items into consecutive cells in a single column, one list item per cell. Leave no blank cells in-between.
-
Click on the cell or select the range of cells in which you want to add the drop-down list. Select the "Data" tab, click "Data Validation" and choose "Data Validation." Choose "List" in the Allow section. Click inside the "Source" box and select the cells containing the list you created. Click "OK."
-
Make sure the cell or the range of cells in which you want to add the drop-down list is still selected and click on the "Home" tab. Select "Conditional Formatting" in the Styles group.
-
Point to "Highlight Cells Rules" and select "More Rules." The New Formatting Rule dialog opens.
-
Choose "Format Only Cells that Contain" in the Select a Rule Type list. Choose "Specific Text" in the Format Only Cells With list. Enter the first list item in the third box and click the "Format" button.
-
Select the "Fill" tab of the Format Cells box. Choose the color with which you want to fill the cell, if the first list item is selected. For instance, if your list consists of the options "High," "Average" and "Low," you might want to shade cells with the word "High" in green, "Average" in yellow and "Low" in red.
-
Click "OK" to apply the formatting. Format the cells again with the second list item and continue until you have added a rule and a color for each item on the list. Save the worksheet before closing Excel. When a user chooses an option from the drop-down list, it will automatically fill the cell with the appropriate color.
In this article, we will learn how to create a Dropdown list with color in Microsoft Excel.
Drop down list limits the user to choose a value from the list provided instead of adding values in sheet.
We will be using Conditional Formatting and Data Validation options.
First, let’s understand how to make a dropdown list in Excel with an example here
Here is a list of three colours Red, Green and Blue. We need that the user has to select from this list.
Click Data > Data Validation option in Excel 2016
Data Validation dialog box appears as shown above.
Select the option List in Allow and select the source list in Source option and click OK.
A dropdown list will be created on the cell.
Now click Home > Conditional formatting. Select New rule from the list and a dialog box will appear.
Select Specific Text option and select the cell for colour as in this case Red.
Select Format > Fill option. Select the Red colour and click OK.
Repeat the process for all the options like Green and Blue and your list will be shown like in below snapshot.
Drop down list and Conditional formatting tools are very useful in Excel 2016, to view your data in a particular format manner. You can create a dropdown list in google sheets using the same method.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at
Check out more related articles:
How to edit a drop down list in Excel?
How to delete drop down list?
How to create dependent drop down list?
How to create multiple dropdown List without repetition using named ranges?
How to create drop down list?
Popular Articles:
50 Excel Shortcut to Increase Your Productivity
How to use the VLOOKUP Function in Excel
How to use the COUNTIF function in Excel 2016
How to use the SUMIF Function in Excel