How to add conditional formatting to a Google Sheets drop-down list (and why you should)


Getty/Jamie Grill

Adding a drop-down list in Google Sheets is a simple trick that can make it exponentially easier for collaborators to fill in data from a handy list of options (instead of having to type everything manually). This can also prevent users from entering the wrong information into a critical field in your spreadsheet.

Also: How to create a drop-down list in Google Sheets

You can use another nifty trick to help make these cells stand out once a collaborator has made a section. That trick is called Conditional Formatting. 

What is conditional formatting?  

Simply stated, conditional formatting formats a cell based on specific conditions. In the example I will show you, the conditional formatting will add a background color to a cell based on the color chosen from the drop-down. For instance, if the color red is chosen from the drop-down, the background color for that cell will automatically be, you guessed it, red. 

Of course, you aren’t limited to only adding colors to your cells, as you can also use other (basic) formatting, such as bold, italics, underline, strikethrough, and change the color of the text. 

Conditional formatting also allows you to apply the formatting based on several conditions, such as:

  • If a cell is/is not empty.
  • Contains (or does not contain) specific text or formula.
  • Text starts/ends with specific text or formula.
  • Dates.
  • Greater than, less than, or equal to.

You can add a single rule or even multiple rules to your conditional formatting. 

Let’s see how this is done.

Adding conditional formatting to a drop-down list

Remember, in the tutorial for adding drop-down lists, we created a list of the following colors:

  • blue
  • red
  • green
  • yellow
  • black
  • orange
  • purple
  • white

Now we’re going to use conditional formatting to set the cell’s background color based on the color chosen. So, if a collaborator chooses green from the drop-down, the cell background color will be set to green.


Select the cell that contains the drop-down and then click Format > Conditional formatting.

The Google Sheets Format menu.

Conditional Formatting is found in the Formatting menu in Google Sheets.

Image: Jack Wallen/ZDNET

Upon clicking conditional formatting, you’ll see a new sidebar on the right side of the window.

The Google Sheets Conditional Formatting sidebar.

I’ve already added two rules, one for blue and red.

Image: Jack Wallen/ZDNET

Click + Add another rule to open the Rule editor.

The Conditional Formatting rule editor.

The conditional formatting rule editor makes it simple to create new rules.

Image: Jack Wallen/ZDNET

In the Value or formula area, type green and then select the color green from the color picker.

The Conditional Formatting color picker.

Adding the proper shade of green for our conditional formatting rule.

Image: Jack Wallen/ZDNET

Now the rule is ready. Continue to create more rules until you have one for each color in your drop-down. 

Now, when a user selects a color from the drop-down, the background color will automatically change based on the condition.

Conditional Formatting as applied to a cell in Google Sheets.

Select green and the background color will change to green.

Image: Jack Wallen/ZDNET

And that, my fellow Google Sheets users, is how you add conditional formatting to a drop-down list. You’re not limited to drop-downs for this feature, as you can apply it to any type of call (and even a range of cells). Enjoy the fresh taste of automated formatting.

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *