X
Home & Office
Why you can trust ZDNET : ZDNET independently tests and researches products to bring you our best recommendations and advice. When you buy through our links, we may earn a commission. Our process

'ZDNET Recommends': What exactly does it mean?

ZDNET's recommendations are based on many hours of testing, research, and comparison shopping. We gather data from the best available sources, including vendor and retailer listings as well as other relevant and independent reviews sites. And we pore over customer reviews to find out what matters to real people who already own and use the products and services we’re assessing.

When you click through from our site to a retailer and buy a product or service, we may earn affiliate commissions. This helps support our work, but does not affect what we cover or how, and it does not affect the price you pay. Neither ZDNET nor the author are compensated for these independent reviews. Indeed, we follow strict guidelines that ensure our editorial content is never influenced by advertisers.

ZDNET's editorial team writes on behalf of you, our reader. Our goal is to deliver the most accurate information and the most knowledgeable advice possible in order to help you make smarter buying decisions on tech gear and a wide array of products and services. Our editors thoroughly review and fact-check every article to ensure that our content meets the highest standards. If we have made an error or published misleading information, we will correct or clarify the article. If you see inaccuracies in our content, please report the mistake via this form.

Close

How to add conditional formatting to a Google Sheets drop-down list

When you want better visualizations of your Google Sheets data, conditional formatting is the way to go.
Written by Jack Wallen, Contributing Writer
Reviewed by Min Shin
spreadsheets concept
Passakorn Prothien/Getty Images

Now that school is back in session, you might find yourself having to take on assignments that include spreadsheets. If so, you might want to know about some handy features found in Google Sheets that could seriously impress your teachers and classmates.

For example, 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 list of options (instead of having to type everything manually). Not only can this save you and your users time, but it can also prevent users from entering the wrong information into a critical field in your spreadsheet. 

Also: How to organize your Google Drive: 5 tips and tricks to know

What if, however, you could take those drop-down lists to the next level and make the results of those drop-down selections really stand out? With conditional formatting, you can do just that. For example, if a user were to select a particular answer, the resulting cell would automatically have a particular background color. 

This can be handy if a user were to enter invalid data and the resulting cell could turn red to indicate a problem. Or maybe you simply want to associate a certain selection with a particular format. Thanks to conditional formatting, you can do that. Adding this feature to your spreadsheets could go a long way to impressing your teachers.

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. 

Also: The best smart notebooks right now: Top picks for students

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 or is not empty.
  • Contains (or does not contain) specific text or formula.
  • Text starts or 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.

How to add conditional formatting to Google Sheets

What you'll need: All you'll need for this is a valid Google account and a web browser to access Google Docs or Google Drive.

Before you continue reading, make sure to first read the article about creating drop-down lists. In that tutorial 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.

Simple.

1. Open the conditional formatting rule editor

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.

Screenshot by Jack Wallen/ZDNET

2. Look for a new sidebar

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.

Screenshot by Jack Wallen/ZDNET

3. Create a new conditional formatting rule

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.

Screenshot by Jack Wallen/ZDNET

4. With the correct cell in the range field, select Text contains from the Format cells drop-down

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.

Screenshot by Jack Wallen/ZDNET

5. Once you've selected the color, click Done.

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

Also: 6 ways to streamline your workflow with Microsoft Excel

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.

Screenshot by 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.

Editorial standards