The Ultimate 2023 Conditional Formatting Google Sheets Guide

Ready to discuss conditional formatting in Google Sheets? Below, I give step-by-step instructions on the process. I also show how to use conditional formatting to highlight information in your most commonly-used Google Sheets.

Follow along with this conditional formatting Google Sheets guide to learn everything there is to know about this important spreadsheet function.

How to Add Conditional Formatting in Google Sheets

There are many reasons you may want to use conditional formatting in Google Sheets. For example, if you have scores of 50 students in a subject, you can quickly highlight the students who have scored less than 35 on the exam. Similarly, you can use many such conditions (as we will see later in this tutorial) to highlight cells based on their value.

Here’s how to add conditional formatting in Google Sheets:

1. Select the cell range.
2. Go to Format > Conditional formatting. A toolbar will open to the right.
3. Click Add a rule, then choose between single color and color scale.
4. Create your formula.
5. Choose a color or format in the Formatting style.
6. Click Done.

It’s really that simple. The more challenging part is building your formula. Before we discuss which formulas to use when you want to format specific data in Google Sheets, lets’ talk about how to access these options.

How to Access Conditional Formatting in Google Sheets

Access Google Sheets’ conditional formatting function by going to Format → Conditional Formatting. Here’s a screenshot of where to find it.

This opens the ‘Conditional format rules’ pane on the right that you can use to set the rules. Next, you will see two options for using conditional formatting in Google Sheets:

• Single Color
• Color Scale

‘Single color’ can be used when you want to highlight all the cells based on the value. For example, I can use this to highlight all the cells with scores that are less than 35. In this case, irrespective of whether the score is 34 or 10, both will be highlighted with the same color.

When to Use Color Scale Conditional Formatting

‘Color scale‘ can be used when you also want to visually present the difference between the cell values. For example, a score of 34 would be a lighter shade of red and 10 would be a darker shade of red. This makes it easier to search for values based on their order.

Examples of How to Conditional Format in Google Sheets

Ready for a few in-depth examples? I wanted to show what kinds of formulas you might use when you want conditional formatting in Google Sheets. Below, I highlighted practical examples.

Example 1: Highlight Scores less than 35

Suppose you have the data set as shown below, and you want to quickly highlight the cells where the score is less than 35. This example shows two columns of data. The first column is the student name (or number, in this case), and the second column shows their marks. We want to highlight anyone with a score below 35.

To do this, first we need to select the data. Here’s how:

1. Select the cells (B2:B16 in this case).
2. Go to Format → Conditional Formatting.
3. In the Conditional formatting pane, make sure ‘Single color‘ is selected.

4. Make sure ‘Apply to range‘ refers to the correct range. If it doesn’t, you can change it here.
5. In the ‘Format cells if’ drop down, select Less than.

6. A new field would appear right below the drop down. Enter 35 in it.

7. Select the format style. You can choose from the default ones or create your own. In this case, let me select the red color.

8. Click on Done.

This would instantly highlight all the cells with a score less than 35 in red color (as shown below). Note how this conditional formatting makes the data much easier to interpret. A quick glance gives you a rough idea of how many students scored in our target range. You could do the same for those who scored over 65, potentially highlighting those in green.

Example 2: Using Color Scale Conditional Formatting

Conditional formatting in Google Sheets offers much more interesting visual options. Wouldn’t it be nice to see a gradient of performance in the same data we just discussed? Here’s how to create a heat map in Google Sheets using Conditional Formatting.

A heat map shows a gradient of colors based on the values in the cells. Here’s a screenshot that shows our final result. Want to learn how to do it?

.

In order to create the heat map using the exam scores, first you need to select the data. This process mirrors our last example. The difference comes when we select “color scale” instead of “single color.” Here’s my step-by-step guide on this type of conditional formatting in Google Sheets.

1. Select the data set in which you want to create the heat map.
2. Go to Format → Conditional formatting.
3. In the Conditional formatting rules pane, select ‘Color scale‘.

4. Make sure ‘Apply to range‘ refers to the correct range. If it doesn’t, you can change it here.

5. In the Preview drop-down, select the gradient you want. Note that the color on the left of the gradient is applied to the lower value numbers, and one of the higher side is applied to the higher value numbers. While selecting the gradient here, you can also see a live preview in the data set. In this case, I am selecting the red-to-green gradient.

6. Click on Done.

This will create a heat map with the gradient shown based on the value in the cell.

Example 3: Google Sheets Custom Formula Conditional Formatting to Highlight All Instances of Duplicate Data Points

To highlight duplicate data points in Google Sheets, you can use the custom function feature in conditional formatting.

Suppose you have a dataset as shown below.

As you can see, there are duplicates in this list.

To highlight all the instances of duplicate occurrence, follow the below steps:

1. Select the data set.
2. Go to Format → Conditional formatting.
3. In the Conditional Format rules, select ‘Single Color‘.

4. Make sure ‘Apply to range‘ refers to the correct range. If it doesn’t, you can change it here.
5. In the ‘Format cells if’ drop down, select ‘Custom formula is‘ option.

6. Enter the following formula in it:

=COUNTIF($A$2:$A$11,A1)>1

7. Select the format.
8. Click OK.

This will highlight all the instances when there are duplicate values.

Note that this is now dynamic, which means that if you change the dataset, it will automatically highlight the cells that contain duplicate values.

Example 4: Highlight Alternate Rows (Creating Zebra Lines)

So what about formatting with conditions other than cell data? Let’s discuss highlighting alternate rows. You’ll see this kind of Google Sheets conditional formatting for printed reports and for data sets that people quickly review. It just makes everything easier to read.

Alternate-row highlighting helps to increase readability and, subjectively, gives it a more professional look. Here’s an example.

While you can create this by manually applying the format, conditional formatting can make it quick and easy.

The easiest way to do this is by selecting Format > Alternating Colors.

Prefer a different way? You can also highlight alternate cells using conditional formatting in Google Sheets. Here’s the step-by-step guide on how to do that:

1. Select the dataset.
2. Go to Format → Conditional Formatting.
3. In the Conditional Format rules, select ‘Single color‘.

4. Make sure Apply to range refers to the correct range. If it doesn’t, you can change it here.
5. In the ‘Format cells if’ drop down, select conditional formatting ‘Custom formula is‘ option.
6. Enter the following formula in it:

=MOD(ROW(),2)=1.

7. Select the format.
8. Click OK.

This would highlight alternate rows in the selected range.

How it Works: The ROW() function returns the row number of a given cell. The MOD() function then divides in by 2 and returns the remainder. When the remainder is 1 (in case row numbers is odd), the condition is TRUE and the conditional format is applied.

In case you want to highlight the even number rows, use the following formula:

=MOD(ROW(),2)=0

Example 5: Highlight Blank Cells

You can use the custom formula option to highlight cells that are blank in the dataset. While it is easy to scan and identify blank cells when the data set is small, in the case of hundreds of records, using conditional formatting to do this is the way to go.

Suppose you have a dataset as shown below:

Here are the steps to highlight all the blank cells in this data set.

4. Select the dataset.
5. Go to Format → Conditional Formatting.
6. In the Conditional Format rules, select ‘Single color’.
7. Make sure ‘Apply to range’ refers to the correct range. If it doesn’t, you can change it here.
8. In the ‘Format cells if’ drop down, select ‘Custom formula is’ option.
9. Enter the following formula in it:

=ISBLANK(A2)

7. Select the format.
8. Click OK.

This would instantly highlight all the blank cells in the data set (as shown below).

Example 6: Highlight Errors

If you import the data from Excel or Text files, or you have used a lot of formulas, there is a possibility that some of the cells in your data set have an error value.

It is important to treat these error values if you plan to use this data for further analysis/calculations.

Suppose you have a dataset as shown below:

Here are the steps to highlight all the cells that contain error.

4. Select the dataset.
5. Go to Format → Conditional Formatting.
6. In the Conditional Format rules, select ‘Single color’.
7. Make sure ‘Apply to range’ refers to the correct range. If it doesn’t, you can change it here.
8. In the ‘Format cells if’ drop down, select ‘Custom formula is‘ option.
9. Enter the following formula in it:

=ISERROR(A2)

7. Select the format.
8. Click OK.

This would instantly highlight all the cells that have any error in it (as shown below).

Example 7: Conditional Formatting Sheets to Highlight Cells That Contain the Searched String

You can use conditional formatting in Google Sheets to create a searchable database.

Something as shown below:

Note that as soon as I enter a name in cell C2 and hit enter, it highlights that name in the data set. In case you enter a partial name, it will highlight all the cells that have that part of the string. For example, if you enter ‘P’ in cell C2 and hit the enter key, it will highlight the names of Pat and Paul.

Here are the steps to make this:

4. Select the dataset.
5. Go to Format → Conditional Formatting.
6. In the Conditional Format rules, select ‘Single color‘.
7. Make sure ‘Apply to range’ refers to the correct range. If it doesn’t, you can change it here.
8. In the ‘Format cells if’ drop down, select ‘Custom formula is’ option.
9. Enter the following formula in it:

=AND(NOT(ISBLANK($C$2)),ISNUMBER(SEARCH($C$2,A2)))

7. Select the format.
8. Click OK.

Now whenever you enter a string in cell C2 and hit enter, it will automatically highlight all the cells that contain that string.

How it works:

The formula used in this case has two parts:

• NOT(ISBLANK($C$2)) – This part is used to check if cell C2 is empty or not. If it is empty, then this condition is false and nothing is highlighted. If you don’t use this part, then all the cells in the dataset would be highlighted when C2 is empty.
• ISNUMBER(SEARCH($C$2,A2)) – This part checks whether the string in cell C2 matches that in the cells in the data range. If the string is found, the SEARCH function would return a number. ISNUMBER would then return TRUE in this case.

Example 8: Conditional Formatting Google Sheets Entire Row

To conditionally format an entire row, you simply have to:

1. Select the row header
2. Navigate to Format > Conditional formatting
3. Set your conditional formatting rules

Example 9: Conditional Formatting Using a Checkbox

Checkboxes usually use the expression true or false in formulas. Let’s say we had checkboxes in our example spreadsheet. We can highlight the items that have been checked in our list using conditional formatting.

4. Select a cell in your data range
5. Go to Format > Conditional formatting.
6. In the Conditional Format rules, select ‘Single color‘.
7. In the ‘Format cells if’ drop down, select ‘Custom formula is‘ option.
8. Enter the following formula in it to highlight the cells with checked boxes:

 =B2=TRUE

This custom conditional formatting Google Sheets will highlight the cells in column A with checked boxes in column B.

Example 10: Google Sheets Conditional Formatting with Multiple Rules

We can also have more than one conditional formatting formulas in Google Sheets. For example, for our checkbox sheet, we can highlight the checked boxes in green and the unchecked boxes in red.

4. Select a cell in your data range
5. Go to Format > Conditional formatting.
6. In the Conditional Format rules, select ‘Single color‘.
7. In the ‘Format cells if’ drop down, select ‘Custom formula is‘ option.

5. Enter the following formula in it: =B2=TRUE to highlight the cells with checked boxes.

6. Click Done.
7. Click Add another rule.

8. In the ‘Format cells if’ drop down, select ‘Custom formula is‘ option.
9. Enter the following formula in it to highlight the cells with unchecked boxes:

=B2=FALSE

10. Go to formatting style and choose the color red.

11. Click Done.

In this example, we’ve used more than one conditional formatting rule in the same data range. In this case we have used conditional formatting based on another cell to highlight the cells in column A.

How to Remove Conditional Formatting in Google Sheets

If you want to remove conditional formatting in your Google spreadsheet:

1. Select a cell in your data range
2. Go to Format > Conditional formatting
3. Click the trash icon beside the rule you want to remove.

This will delete the conditional formatting applied to your data range.

Google Sheets Conditional Formatting Rules

We create conditional formats using conditional format rules in Google Sheets. These rules have formulas that are used to apply the conditions, for example, to highlight specific values. You can find them in the dropdown that says “Format cells if:”

Let’s break down this rules:

Is empty and Is not empty

These two formatting rules are used to add a conditional format to a range of cells depending on whether the cell has values in it or it’s empty.

Is empty will add the format to any cell that is empty within the selected range, while is not empty will do the opposite.

Let’s look at our example below:

We’ve used the Is empty rule to highlight all the cells that are empty in our range. If we used the Is not empty rule, instead all the cells with text will be highlighted.

Text rules

Conditional formatting text rules. There are 5 text rules in Google Sheets

They include:

1. Text contains– This rule will format any cell that has the text mentioned.
2. Text does not contain– This rule will format any cell that does not contain the text mentioned.
3. Text starts with– This rule will format any cell that has the text mentioned at the beginning.
4. Text ends with– This rule will format any cell that has the text mentioned at the end.
5. Text is exactly- This rule will format any cell that has specifically and only the text mentioned.

Date Rules

These formatting rules are used to format cells with dates specifically as the condition.

They include:

1. Date is– Formats cell that contain a specific date mentioned.
2. Date is before– Formats cells that contain any date before the date mentioned
3. Date is after– Formats cells that contain any date that comes after the date mentioned.

Number Comparison Rules

These formatting rules work specifically with numerical values and often use comparison operators to create conditions.

They include:

1. Greater than– This rule formats cells under the condition that the number is larger than the value mentioned. For example, if the formatting rule is “greater than 10”, the rule will highlight all the cells with a number larger than 10.
2. Greater than or equal to-This rule formats cells under the condition that the number is larger than or the exact value mentioned.
3. Less than– This rule formats cells under the condition that the number is smaller than the value mentioned.
4. Less than or equal to– This rule formats cells under the condition that the number is smaller than or the exact value mentioned.
5. Is equal to- This rule formats cells under the condition that the number is the exact value mentioned.
6. Is not equal to– This rule formats cells under the condition that the number is any other number other than the value mentioned.
7. Is between- This rule formats cells under the condition that the number is within the range mentioned.
8. Is not between– This rule formats cells under the condition that the number is not within the range mentioned.

Custom Formula

Custom formulas are usually used for advanced conditional formatting that can’t be applied using the formatting rules we’ve mentioned so far. We have already shown you plenty of Google Sheets conditional formatting custom formula examples.

The most common ones use the COUNT function.

Frequently Asked Questions

What Is Google Sheets Conditional Formatting? / What Is Conditional Formatting in Google Sheets?

Conditional formatting in Google Sheets is an automatically applied formatting of cells based on requirements set by the user. You apply conditional formatting rules to format your data based on those rules.

How Do I Use Conditional Formatting in Google Sheets?

You can use conditional formatting to make the data in your spreadsheets more scannable. The simplest way to apply conditional formatting is to:

1. Highlight the cells you want to apply conditional formatting to
2. Navigate to Format > Conditional formatting
3. Set your conditional formatting rules

How Do You Conditionally Change Cell Color in Google Sheets?

Under the Formatting style heading of the Conditional formatting menu, change the fill color.

How Do I See Conditional Formatting Rules in Google Sheets?

To see all the conditional formatting rules at once, head over to Format > Conditional formatting. Then click on any existing rules to see the cells they apply to.

How Do I Change Conditional Formatting Colors and Rules?

1. Navigate to Formatting > Conditional formatting
2. Click o the existing rule you’d like to change
3. Change the rule as though you were making a new one
4. Change colors with the fill color option

Learning More

So that’s how to use conditional formatting in Google Sheets. Our guide covered step-by-step instructions with multiple examples.

If you’d like to learn more about mastering Google Sheets and other spreadsheet software, consider a course. We regularly evaluate these courses, and they’re wonderful resources for those who want to master more advanced concepts.

Related:

• How to Color Alternate Rows in Google Sheets.
• How to Count Cells That Aren’t Blank in Google Sheets
• Apply Conditional Formatting Based on Another Cell Value in Google Sheets
• How to Use Spell Check in Google Sheets to Find Misspelled Words.
• Using Conditional Formatting in Excel – 7 Examples.
• How To Format Phone Numbers In Google Sheets
• How To Freeze Panes in Google Sheets
• A guide to the Google Sheets IF Function