How to Color Code a Drop Down List in Google Sheets

5 Minutes

In this article we will show you exactly how to create a drop-down list in google sheets with color coding.

‍

We have created a sample sheet for your convenience. Click here to access this spreadsheet and follow along with our tutorial at home.

Creating a Dropdown List

We must first create a dropdown list. In this example we are going to create a dropdown list for a hiring manager who wants to put yes, no and maybe beside applications. You can also read our full tutorial to add a dropdown in Google Sheets for further explanation.

‍

Step 1: Select the cell range

Select the cell or range of cells where we want the dropdown list to appear

*Create a drop down list in Google Sheets*

Step 2: Click on Data > Data Validation

From the top toolbar select Data followed by the Data validation option

*Create a drop down list in Google Sheets: Data validation*

‍

Step 3: Change Criteria to List of Items

The Data validation menu will show. In the Criteria drop down menu change this to List of items

*Create a drop down list: List of items*

‍

Step 4: Input the List Items

Type the list item items in the input box to the right of the criteria menu. Remember to separate the items with a comma like: item1,item2,item3

In our example we will input: Yes,No,Maybe

*Create a drop down list in Google Sheets: List items*

Step 5: Set Invalid Data Behavior

If any information is entered in the dropdown menu that is not one of the list items then we can set an event that will be triggered. We can set a warning to be shown or to reject the input completely. In this example we will select Show warning. To set a custom warning message check the box next to Appearance to Show validation help text. This will show an input box for you to type your message.

‍

Step 6: Select Save to Apply the Dropdown List

Click on the save button and the dropdown list will be created in the cell selection. You will see a grey downward facing arrow to the right of the cell and clicking a cell will show the list items.

Color Code a Dropdown List

With our list now created we will use conditional formatting to color code it.

‍

Step 1: Select the Dropdown List Cells

Select the cells you want to apply the changes to.

*Color code drop down list in Google Sheets: Cell selection*

‍

Step 2: Click Format > Conditional formatting

From the top toolbar select Format followed by Conditional formatting

*Color code drop down list in Google Sheets: Conditional formatting*

‍

Step 3: Specify the Format Conditions

Click the menu underneath Format rules. Select the option for Text contains

*Use conditional formatting on drop down list in Google Sheets: Cell selection*

‍

When a format rule is selected an input box will appear underneath. As our example is using the Text contains rule we need to enter one of our list items here. At this step we will input “Yes”.

*Use conditional formatting on drop down list in Google Sheets: Format conditions*

Step 4: Set the formatting style and Apply Changes

Under the heading Formatting style there are options to change the format of the cell . We will set the cell fill color to green for the “Yes” item in our list. Select Done to apply the changes.

‍

Step 5: Add Another Rule for the Remaining List Items

Select Add another rule.

*Use conditional formatting on drop down list in Google Sheets: Add another rule*

‍

Repeat steps 3 and 4 to apply the color code to the remaining list items.

‍

In our example we are going to set the Text contains rules to our “Maybe” and “No” list items selecting different color fills in our formatting style.

‍

Step 6: Select a Value in the Dropdown List to see Results

In your list choose a value and see the color code is assigned to the list item as specified in the Conditional format rules.