---
title: "How to use conditional formatting in Google Sheets "
description: "Here's how to apply conditional formatting in Google Sheets to make key data stand out. "
image: "https://images.ctfassets.net/lzny33ho1g45/conditional-formatting-google--p-img/755a081fb79f77e9fc2e7eac1dbac05c/file.png"
---

# How to use conditional formatting in Google Sheets 

Here's how to apply conditional formatting in Google Sheets to make key data stand out. 

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](https://docs.google.com/spreadsheets/d/1S69ZQuKPi5YvFx93dmAn0lqNX6NNEPH2xLJvXdtCLuo/copy). 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?](#what-is-conditional-formatting)
- [How to do conditional formatting in Google Sheets](#how-to-do-conditional-formatting)
- [How to apply conditional formatting with is empty/is not empty](#conditional-formatting-based-on-empty-not-empty)
- [How to apply conditional formatting based on text](#conditional-formatting-based-on-text)
- [How to apply conditional formatting based on number values](#conditional-formatting-based-on-number-values)
- [How to apply conditional formatting to an entire row ](#apply-conditional-formatting-to-rows)
- [How to apply conditional formatting using a color scale](#conditional-formatting-using-a-color-scale)
- [How to apply conditional formatting based on dates](#conditional-formatting-based-on-dates)
- [How to apply multiple conditional formatting rules](#multiple-conditional-formatting-rules)
- [How to copy and paste conditional formatting to another Google Sheet](#copy-and-paste-conditional-formatting)

## 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: 

- Greater than
- Greater than or equal to
- Less than
- Less than or equal to
- Is equal to
- Is not equal to
- Is between
- Is not between

**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](https://zapier.com/blog/how-to-make-table-google-sheets). 

**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](https://zapier.com/blog/google-sheets-shortcuts/) 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**

- [How to find and remove duplicates in Google Sheets](https://zapier.com/blog/remove-duplicates-google-sheets/)
- [How to link data from one spreadsheet to another](https://zapier.com/blog/link-spreadsheet-data/)
- [Google Sheets vs. Excel: Which is right for you? ](https://zapier.com/blog/google-sheets-vs-excel/)
- [How to add a dropdown list in Google Sheets](https://zapier.com/blog/how-to-add-dropdown-list-google-sheets)
- [Free Google Sheets templates to boost productivity](https://zapier.com/blog/google-sheets-templates)
- [How to merge cells in Google Sheets](https://zapier.com/blog/google-sheets-merge-cells/)
- [How to sort in Google Sheets](https://zapier.com/blog/how-to-sort-in-google-sheets)

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