How to use conditional formatting in Google Sheets

Interpreting spreadsheets full of data isn’t a skill that comes naturally to me. My eyes glaze over from information overload before I can even get into the meat and potatoes of what it all means. That’s why I use conditional formatting. 

It helps me better understand key data at a glance so I can track things like my spending habits and how my team is progressing toward our quarterly goals.

Here, I’ll walk you through the basics of conditional formatting in Google Sheets. And I’ll show you how to apply some of the most common formatting rules. To follow along, use our demo spreadsheet. Copy the spreadsheet, and then play around with it as we proceed through the tutorial.

But first, if you’re looking for a quick refresher, here’s the short version on how to use conditional formatting. (Scroll down to learn more specifics and practice with our demo spreadsheet.) 

1. Highlight the cell range you want to apply the formatting rules to. 

2. Select Format > Conditional formatting. 

3. From the Conditional format rules window that appears, click the dropdown menu under Format cells if…

4. Select the condition you want to trigger your rule. 

5. Under Formatting style, select your formatting style.

6. Click Done.

Want to view all the rules in your spreadsheet? Simply highlight the entire data range (or the whole sheet), and open the Conditional format rules window using the first step from above. This will display a complete list of any existing rules. You can also quickly delete any rules by selecting the Remove rule icon, which looks like a garbage can.

What is conditional formatting in Google Sheets? 

In Google Sheets, conditional formatting allows you to dynamically change a cell’s text style and background color based on custom rules you set. Every rule you set is created using an if this, then that statement. 

In the example below, the rule tells Google Sheets, “If any cell in column E is equal to 0%, then change that cell’s background color to light yellow.” 

Before we go further, let’s clarify the three key components that make up every conditional formatting rule. 

• Range: The cell (or cells) which the rule applies to. In the example above, the range is “E:E” (i.e., all of column E).   

• Condition: This is the “if” part of the if this, then that rule. It refers to the trigger event that needs to happen for the rule to run. In the example above, the condition is “is equal to.” 

• Formatting: This is the “then” part of the if this, then that rule. It refers to the formatting that should apply to any given cell, if the conditions are met. In the example above, the style is “background color to light yellow.” 

How to do conditional formatting in Google Sheets 

Conditional formatting is an efficient way to make valuable information stand out. Here’s how to create and modify conditional formatting rules in Google Sheets. 

1. Select a range 

First, select your desired range. You can do this one of two ways. 

Option 1: Highlight a range directly in the spreadsheet. This can be anything from a single cell to multiple cells across different rows and columns. If you’re not managing a lot of data, this is the quickest way to go.

Option 2: Without highlighting anything, select Format > Conditional formatting. From the Conditional format rules window that appears, enter your desired range under Apply to range. 

To add more ranges, click Select data range, which looks like a grid, from the cell range text box. In the Select a data range window that appears, click Add another range. 

2. Create the condition 

Once you’ve selected your range, create your trigger rule (i.e., your if this, then that statement) in the Format cells if section. There are a lot of triggers to choose from, which will impact how you format your rule. Let’s look at the most common triggers. 

Apply conditional formatting with is empty/is not empty 

The first set of triggers—cell is empty and cell is not empty—will run based on whether or not there’s data in the specified range.

Try it out: 

1. In the demo spreadsheet, select cell A1, and then click Format > Conditional formatting. 

2. Select the trigger is empty. Since the selected cell (A1) is empty, the default conditional formatting will apply. Magic! (Note: not actually magic.) 

Apply conditional formatting based on text 

To automatically format a cell based on its text, use any of these text-based triggers: 

• Text contains 

• Text does not contain 

• Text starts with 

• Text ends with 

• Text is exactly 

Try it out: Let’s say you want to highlight all of your employees based in the Tampa office. 

1. Select column B, and click Format > Conditional formatting.

2. Under Format cells if, select Text contains. 

3. In the Value or formula field, enter Tampa (the text value isn’t case sensitive). 

Now, the default formatting style will apply to any cell containing the word “Tampa.” And since you applied the rule to all of column B, any time you add a new rep in the Tampa office, the cell containing “Tampa” will automatically be highlighted for easy access. 

Apply conditional formatting based on number values 

To automatically format a cell based on numbers, use any of these triggers: 

Try it out: Let’s say you want to highlight “stretch” goals i.e., any goals where the increase is 20% or higher. 

1. Select column E, and click Format > Conditional formatting.

2. Under Format cells if, select Greater than or equal to.

3. In the Value or formula field, enter 20%. 

Apply conditional formatting to an entire row  

Let’s kick things up a notch and apply conditional formatting to entire rows. To do this, using a custom formula is your best bet. 

Try it out: Let’s say you want quick access to all data associated with your existing Tampa reps, including their names and sales targets.  

1. Highlight your entire data set (in this case, A3:F14), and select Format > Conditional formatting.

2. Under Format cells if, select Custom formula is (last option). 

3. In the Value or formula field, enter =$B:$B="Tampa". 

Now, any row containing “Tampa” in column B is highlighted. 

How did that work? Let’s nerd out and break the custom formula down. 

The = symbol indicates the start of the formula. $B:$B tells Google Sheets to scan through column B for a specific value. By adding the $ before B, it tells Google to only scan through column B. And finally, ="Tampa" tells Google Sheets what value to look for. 

Now, let’s say you want to highlight any row where the stretch goal is greater than or equal to 20%. Again, you’d use a custom formula. In this case, you would enter =$E:$E>=20%.

If you’re applying a custom formula based on number values, there are more operators you can play around with: less than or equal to (<=), less than (<), greater than (>), or equal to (=). 

You can also use a custom formula to highlight any rows that don’t include a given value. For example, if you wanted to highlight your reps who don’t work out of the Tampa office, you’d use the custom formula =$B:$B<>"Tampa".

Apply conditional formatting using a color scale 

The color scale is another formatting style that makes it easier to visualize values on a spectrum. 

Try it out: Let’s say you want to see where your reps’ stretch goals fall on a spectrum: a 0% increase being not great, and a 50% being “Wow! Now that’s a stretch goal.” A great way to visualize this is by using a color scale where the fill color differs in intensity based on the cell value. Here’s how. 

1. Highlight column E, and then click Format > Conditional formatting. 

2. In the Conditional format rules window, click Color scale. The default formatting will appear, highlighting the lowest stretch goal percentages with a highly saturated color (in this case, dark green) and the highest ones with a less saturated version of the same color (in this case, light green).  

Apply conditional formatting based on dates 

Before applying conditional formatting rules using dates, it’s important to use a consistent date format throughout your spreadsheet. 

To do this, highlight any cells that include dates, click Format > Number. From here, you can either select the default date format, or select Custom date and time and choose your preferred style.  

Now that your date format is consistent throughout, let’s apply some conditional formatting rules. Here are your options for date-based rules:  

• Date is 

• Date is before 

• Date is after 

Try it out: Let’s say you want to highlight reps who have goals to increase their sales by the end of August. 

1. Highlight column F, and then click Format > Conditional formatting. 

2. Under Format cells if, select Date is before > Exact date.

3. In the Value or number field, enter 09/01/2023. 

Apply multiple conditional formatting rules 

Adding more than one conditional formatting rule to a given range is easy. Simply highlight the range, click Format > Conditional formatting, and then click Add another rule. 

Google Sheets will run through each rule—in the order they were created—until it finds a condition that requires a style change. However, once a rule is met by any given cell, subsequent rules won’t override it.

In the example below, the following conditional formatting rules have been applied to the entire data range (A3:F14): 

• First: If the rep is based in Tampa, highlight the entire row in light green. To do this, I used the same formula from before: =$B:$B="Tampa".

• Second: If the sales goal increase of any rep is equal to 0%, highlight the cell in light yellow. To do this, I set the rule to “is equal to 0%.”

Jackson (row 7) works from the Tampa office. Because this meets the first rule, row 7 is highlighted in green. Even though Jackson also has a 0% increase in his sales goals and the second rule theoretically applies, it doesn’t matter to Google Sheets. It’s one conditional formatting rule to… rule them all. 

3. Select a formatting style 

Up to this point, we’ve mainly used Google Sheets’ default formatting style to highlight specific cells. But if you want to add a little more pizzazz, you can easily update your formatting style. Here’s how. 

In the Conditional format rules window, click Default under Formatting style to choose from five other preset styles. If none of these options meet your needs, you can also create a custom style using the tools under Formatting style. 

Bonus: How to copy and paste conditional formatting to another Google Sheet

Let’s say you have multiple sheets of data, and you want to apply the same conditional formatting rules from one worksheet to another in Google Sheets. You could apply the same rules manually, or you can take a much faster route: copy and paste—but make it special. Here’s what I mean. 

In the example below, there are two spreadsheets of sales data: one for 2022 goals and another for 2023 goals. In the 2022 Goals spreadsheet, the rule is “if value is equal to 0%, highlight the cell in light green.” Here’s how to apply the same rule to the 2023 Goals spreadsheet.

1. Click any cell that has the desired rule applied in the 2022 Goals spreadsheet, and use your keyboard shortcut to copy it. 

2. Click the spreadsheet tab 2023 Goals. 

3. Highlight the data range in column E (E3:E14). 

4. Right-click, and select Paste special > Format only. 

Any cells in column E with the value 0% will immediately be filled in light green. 

Related reading

This article was originally published in December 2018 by Theodor Porutiu. The most recent update was in June 2024.