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.
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.
Select the cell or range of cells where we want the dropdown list to appear
From the top toolbar select Data followed by the Data validation option
The Data validation menu will show. In the Criteria drop down menu change this to List of 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
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.
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.
With our list now created we will use conditional formatting to color code it.
Select the cells you want to apply the changes to.
From the top toolbar select Format followed by Conditional formatting
Click the menu underneath Format rules. Select the option for Text contains
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”.
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.
Select 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.
In your list choose a value and see the color code is assigned to the list item as specified in the Conditional format rules.
