In this tutorial, you will learn how to create drop-down list in google sheets with color. While Google Sheets is best known for its number crunching abilities, it’s also frequently used for other purposes, such as status or inventory tracking. In many cases, simply entering a number into a cell won’t convey the necessary information, but a problem arises if
users are allowed to manually type data into cells, as typos will often occur and then formulas relying on these entries may break. The best way to prevent this sort of input error is to create a drop-down list that allows users to enter only the exact values the spreadsheet can handle. Using this method also allows for easy implementation of conditional formatting to color code different values. In this tutorial, we’ll cover how to create a drop-down list and apply conditional formatting to
differentiate between the drop-down list items. Read on to learn how.Drop-Down Lists
Drop-down lists are essentially menus located in the cells of the spreadsheet. Users can click on the cell to open the menu and easily select an option. Here’s how to add them to your spreadsheet:
Step 1
Select a cell or range to add drop-downs to and open the Data menu, then select Data Validation
Step 2
In the Data Validation popup, choose the data validation criteria you want to use. The two options that will display as drop-downs are List from Range and List from Items
Step 3
To use the List of Items option, type the desired list items into the text field beside it, separated by commas
Step 4
To use the List from Range option, type the range reference in the text field beside the criteria type drop-down. You can also use the Select Data Range button in the text field to choose the range. Be sure to include dollar signs to make the reference absolute
Step 5
Ensure the box for Show Dropdown List In Cell is checked, and choose if you want to allow users to enter values not in the list [it’s recommended to reject invalid input unless you have a reason not to]. Then click Save.
Step 6
The selected cells will now display a downward pointing triangle to show they contain drop-downs. Click on the cell to open the list and select an option
Color Coding
Once you have the drop-down lists set up, color code the list items by following these steps:
Step 1
Select the same range you added drop-downs to and open the Format menu, then choose Conditional Formatting
Step 2
In the Conditional Format Rules sidebar, under Format Cells If…, select the Text is Exactly option, and type the exact text used for the first drop-down list item
Step 3
Choose formatting options from the Format Style section, then click Done to apply the rule
Step 4
To add more rules for each list item, click Add Another Rule and repeat Steps 2-3 as many times as needed
Step 5
Now when you select list items from the drop-down, the cells will automatically format based on the conditional formatting rules
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to create drop-down list in google sheets with color. Want more? Check out all the Google Sheets Tutorials.
You might also like: