How to add conditional formatting to drop-down list Google Sheets

If you’re wondering how to create a drop down list in Google Sheets with color, this is the guide for you! There are various times when you might want to create a drop down and then color code it based on the selection.

For example, maybe after finding duplicates in two columns, you'll want to categorize the data with a drop down list. Another use case for this might be if you are analyzing historical stock data and want to use repeated notes from a list. You could even add drop downs to your spreadsheet after syncing two sheets into one master sheet.

So, how do you create a color coded drop down list in Google Sheets? To create a drop down list in Google Sheets, you’re first going to use data validation to create the drop down. Next, you’ll want to set up conditional formatting to make the drop down selection color coded. The combination of using data validation and conditional formatting will give you the desired result!

Here are some step by step instructions for creating a dropdown list with color in Google Sheets.

Creating a Drop Down List With Data Validation

We want to make a drop down list from the data contained in our list.

First, we select the cell where we want the drop down to appear.

1. In the Ribbon, select Data > Data Tools > Data Validation.

2. Select List.

3. Select the range of cells required as the Source.

4. Click OK to see the drop down list in the workbook.

Sample Google Sheet File

In this tutorial we will be using the below Google Sheet Document to work. Feel free to create a copy for yourself.

Add color to Dropdown List

Creating a Dropdown Lists

To those who are new to the concept of Data Validation and Dropdown Lists you can get visit below links.

1. How to Create a Dropdown Lists
2. How to Edit a Dropdown List

Or, a quick recap below would also be useful to juggle the memory.

Creating Dropdown Sheet using List of Items Option

Creating Dropdown Cells using Cell Range Option

Creating a Dependent Drop Down List In Google Sheets

Below is the dataset that I will use to create a dependent drop down list:

Here are the steps to create a dependent drop down list in Google Sheets:

• Select the cells that have the fruits (A2:A5).
• Go to Data –> Named Ranges.
• In the Named ranges pane, enter the name for this named range. Note that the name needs to be exactly the same as it’s parent category. In this case, it would be ‘Fruits’.
• Click on Done. This will create the first named range with the name ‘Fruits’ that would refer to the cells that have the fruit names.
• Now click on the Add a range option and use the same steps shown above to create a named range for Vegetables. You need to create as many named ranges as many options you want to show in the first drop down list.
• Now it’s time to create the first drop down list that shows Fruits/Vegetables as the options. Select the cell where you want to create it and go to Data –> Data Validation.
• In the Data Validation dialog box, select the ‘Criteria’ as ‘List from a range’ and specify the cells that contain the names (Fruits/Vegetables).
• Make sure ‘Show dropdown list in cell’ is checked and click on Save.
• This will create a drop-down list in cell D3.
• Now before we create the second drop-down, go to any empty cell in the worksheet, or create another worksheet if you want, and enter the formula =INDIRECT(D3). This would list all the items from the category selected in Drop Down 1. This is a dynamic dependent drop-down list and if you change the drop-down in cell D3 from Fruits to Vegetable, the list would change and show the list of vegetables.
  
  
• Now the last step is to create a drop down list in E3 (which is our Drop Down 2). To do this, select cell E3 and go to Data –> Data Validation.
• In the Data Validation dialog box,select the ‘Criteria’ as ‘List from a range’ and specify the cells that contain the result from the INDIRECT function. It is possible that the number of items in different lists are different (for example ‘Fruits’ categoryhas 4 items and ‘Vegetables’ has 10 items). To handle this, select an appropriate cell range that should suffice all the categories.For example, in this case, I select 10 cells in case the list grows in future.
• Click Save.

Now when you make a selection is Drop Down 1, you will notice that the items in Drop Down 2 changes accordingly.

Important Notes While Creating a Dependent Drop Down List In Google Sheets

Here are a couple of important points to note while creating a dependent drop down list in Google Sheets:

• The named range could only be a single word. In case it more than one word, use underscore to join the words while creating the named range. For example, if the category is ‘Seasonal Fruits’ instead of ‘Fruits’, when you create a named range, name it Seasonal_Fruits.
• While I create the Indirect formula right below the list, as a best practice, create it in a different worksheet. You can then hide the worksheet if needed.
• When you select a category and an item within it (for example you select Fruits and then Mango), and then you change the Main category (for example selecting ‘Vegetables’), the dependent drop down list wouldn’t change. This means that it can happen that Drop Down 1 shows ‘Fruits’ as the category and Drop Down 2 has a vegetable name in it. However, Google Sheets will warn you by showing a red triangle at the top right of the cell. If you hover the cursor over that cell, you will see the warning as shown below:

So this is how you can create a conditional drop-down list in Google Sheet (i.e., a drop-down list based on another cell’s selection)