How To Create Drop-Down List In Google Sheets With Color

Last Updated on November 3, 2023 by Jake Sheridan

In this tutorial, you will learn how to create drop-down list in google sheets with color.

How To Create Drop-Down List In Google Sheets With Color

• How To Create Drop-Down List In Google Sheets With Color
• Google Sheets Drop-Down Lists Color
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
• Adding a Color Coded Drop Down List
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
• Summary

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.

Google Sheets Drop-Down Lists Color

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:

How to create drop-down list in Google Sheets with color (Quick method)

• Choose the cell where you’d like to add a dropdown list.
• Right-click and select Dropdown
• Add your criteria and customise your colors.

Need more detailed instructions? Here ya’ go:

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.

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

Adding a Color Coded Drop Down List

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

Go deeper here: Dropdowns in Google Sheets: A Guide

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.

More ways to use drop downs in Google Sheets:

• Create A Drop Down List
• Edit Drop Down List
• Multiple Selection Dropdown
• Dependent Drop Down List
• Add Yes/No Drop-Down Lists
• Add Checkboxes to Dropdowns
• Display Date Picker
• Add Yes Or No

You might also like:

22 Ways to Format Text in Google SheetsHow To Count How Many Times A Word Appears in Google SheetsHow To Change Text To All Caps In Google SheetsHow to Fit Text to Cell in Google Sheets