The Simplest Guide to Conditional Formatting Google Sheets

Ready to discuss conditional formatting in Google Sheets? Below, I’m providing step-by-step instructions on the process, including color gradient (scaling), using a checkbox, highlighting information, and much more.

I’ve created the most straightforward conditional formatting Google Sheets guide so you can discover everything about this critical spreadsheet function.

Using Excel? Check out our Using Excel conditional formatting guide!

How To Add Conditional Formatting in Google Sheets

You may want to use conditional formatting in Google Sheets for many reasons.

Say that you have 50 test scores. Conditional formatting can be used to highlight scores that are lower than 35. You can use countless parameters to highlight cells based on their value.

To add conditional formatting in Google Sheets:

1. Select the cell range.
2. Go to “Format” > “Conditional formatting” in the drop-down menu. 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 how you want to build your formula.

How To Access Conditional Formatting in Google Sheets

You must locate the command before entering advanced conditional formatting in Google Sheets. You can do this by selecting “Format” > “Conditional Formatting.”

This will open the “Conditional format rules” pane that can be used to set your rules. You should see two options for conditional formatting in Google Sheets:

• Single color
• Color scale

Using Color Scale Conditional Formatting

‘Single color’ can highlight all a cell’s background color based on a specific value. For example, I might highlight all the cells with scores less than 35. It wouldn’t matter if the score is 34 or 10: Both cells’ background color will be affected.

Using Color Scale Conditional Formatting

‘Color scale‘ can present varying color gradations between the cell values. For example, a score of 34 might be a lighter shade of red, while 10 would be a darker shade of red.

Examples of Conditional Formatting in Google Sheets

Below, I’ve included a few formulas you could use for conditional formatting in Google Sheets.

1. Highlight Scores Less than 35

I want to highlight the cells with scores less than 35. The first column is reserved for the students’ numbers, and the second column shows their marks. We want to highlight trends showing students’ results with a score below 35.

First, we need to select the data:

1. Select the cells (B2:B16).
2. Go to Google Sheet’s top menu and select “Format” > “Conditional Formatting.”
3. Select “Single color.”

4. Ensure that “Apply to range” refers to the correct range.
5. In the “Format cells if” drop-down, select “Less than.”
6. A new field should appear below the drop-down. Enter 35 in it.

7. Select the format style. You can choose from the default colors or choose your own. In this case, I selected red.

8. Click “Done.”

This will instantly highlight all the cells with a score less than 35 in red (as shown below). You can easily do the same using another color for those who scored over 65.

Related: How To Freeze Panes in Google Sheets

2. Using a Heat Map for Google Sheets Conditional Formatting

Conditional formatting in Google Sheets offers more interesting visual options. Here’s how to create a heat map in Google Sheets using conditional formatting:

To create the heat map using the exam scores, I selected the data the same way in Example 1. The difference comes when we select “color scale” instead of “single color.”

1. Select the data set in which you want to create the heat map.
2. Go to “Format” > “Conditional formatting” > “Color scale.”
3. Be sure that “Apply to range” refers to the correct range. You can change it if it doesn’t.
   

   

4. In the “Preview” drop-down, select your preferred gradient.
   • The color on the left of the gradient is applied to the lower-value numbers — and those on the higher side are applied to the higher-value numbers.
   • When selecting the gradient, you can see a live preview.
   • Note: In this case, I am selecting the red-to-green gradient.

6. Click “Done” to create a heat map with gradient shading.

3. Google Sheets Custom Formula Conditional Formatting to Highlight Duplicate Data Points

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

Suppose you have the following dataset:

To highlight all the duplicate data:

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

4. Be sure that “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 list, select the “Custom formula is” option.

6. Enter the following formula:

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

7. Select the format and click the “OK” button to highlight all the instances of duplication.

Note: This is dynamic. This means that if you change the dataset, it will automatically highlight the cells containing duplicate values.

4. Highlighting Alternate Rows

In Google Sheets, highlighting alternate rows is preferred for printed reports and data sets that people review quickly. Simply put, adding color to whole rows improves readability and provides a more professional look.

Highlight alternate cells using conditional formatting in Google Sheets:

1. Select the dataset.
2. Select “Format” > “Conditional Formatting” > “Single color.”
   

   

3. Be sure you’ve selected the correct range (or change it).
4. In the “Format cells if” drop-down, select the conditional formatting “Custom formula is” option.
5. Enter the following formula:

=MOD(ROW(),2)=1

6. Select the format.
7. Click “OK” to highlight alternate rows in the selected range.

How Do I Auto Highlight Every Other Row in Google Sheets?

The easiest way to do this is by highlighting your entire spreadsheet and selecting “Format” > “Alternating Colors.” Next, choose the theme you want to apply to your spreadsheet.

5. Highlight Blank Cells

Using the custom formula option to highlight blank cells in the dataset is easy. This is especially useful when there are hundreds or thousands of records. Suppose you have the following dataset:

Related: How to Count Sheets Cells That Aren’t Blank

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

1. Select the dataset.
2. Select “Format” > “Conditional Formatting” > “Single color.”
3. Make sure “Apply to range” refers to the correct range or change it.
4. In the “Format cells if” drop-down, select the “Custom formula is” option.
5. Enter the following formula in it:

=ISBLANK(A2)

7. Select the format and click “OK” to instantly highlight all the blank cells in the data set:

6. Highlighting Errors with Google Sheets Conditional Formatting

If you’ve imported the data from Excel or Text files — or have used a lot of formulas — there’s a possibility that some of the cells will have an error value. It would be best if you fixed these error values.

Suppose you have the following dataset:

To highlight all the cells that contain errors:

1. Select the dataset.
2. Select “Format” > “Conditional Formatting” > “Single Color.”
3. Be sure that “Apply to range” refers to the correct range or change it.
4. In the “Format cells if” drop-down, select the “Custom formula is” option.
5. Enter the following formula:

=ISERROR(A2)

6. Select the format and click “OK” to highlight all the cells with any error (as shown below).

7. Highlighting Cells That Contain the Searched String

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

When I enter a name in cell C2 and hit “Enter,” it will highlight that name in the data set. If I enter a partial name, it will highlight all the cells that contain part of that string. For example, if you enter ‘P’ in cell C2 and hit the enter key, it will highlight the names of Pat and Paul.

1. Select the dataset.
2. Go to “Format” > “Conditional Formatting” > “Single color.”
3. Make sure the “Apply to range” refers to the correct range — if it doesn’t, change it here.
4. In the “Format cells if” drop-down, select the “Custom formula is” option.
5. Enter the following formula in it:

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

6. Select the format and click “OK.”

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

8. Conditional Formatting Google Sheets for an Entire Row

To conditionally format an entire row, you have to:

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

9. Conditional Formatting Using a Checkbox

Let’s say I want to include a grocery list in my spreadsheet. I can highlight the checked items by using conditional formatting.

1. Select the dataset.
2. Go to “Format” > “Conditional Formatting” > “Single color.”
3. Make sure the “Apply to range” refers to the correct range — if it doesn’t, change it here.
4. In the “Format cells if” drop-down, select the “Custom formula is” option.
5. Enter the following formula to highlight the cells with checked boxes:

=B2=TRUE

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

10. Google Sheets Conditional Formatting with Multiple Rules

Having more than one conditional formatting formula in Google Sheets is possible. In my checkbox sheet, I can highlight the checkboxes in green and the unchecked boxes in red.

1. Select a cell in your data range
2. Go to “Format” > “Conditional formatting.”
3. In the “Conditional Format rules,” select “Single color.”
4. In the “Format cells if” dropdown menu, select the “Custom formula is” option.

5. To highlight the cells with checkboxes, enter the following formula:

=B2=TRUE 

6. Click “Done” > “Add another rule.”
   

   

7. Under the “Format cells if” drop-down, select the “Custom formula is” option again.
8. Enter the following formula to highlight the cells with unchecked boxes:

=B2=FALSE

9. Go to formatting style and choose your preferred color.

11. Click “Done.”

Watch How To Apply Conditional Formatting Based on Another Cell Value in Google Sheets

No time to read through my whole article? No problem! Here is a quick shortcut to teach you how to apply conditional formatting based on another cell value. Enjoy!

[adthrive-in-post-video-player video-id=”CrurMxIs” upload-date=”2021-08-02T15:09:10.000Z” name=”Apply Conditional Formatting Based on Another Cell Value in Google Sheets” description=”Apply Conditional Formatting Based on Another Cell Value in Google Sheets” player-type=”default” override-embed=”default”]

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 the whole row of your data range.

How To Remove Conditional Formatting from One Cell

To remove formatting from a selected cell, select the cell and go to “Format” > “Clear formatting.”

It’s just that simple!

Google Sheets Conditional Formatting Rules

We use conditional format rules in Google Sheets to apply the correct conditions (e.g., highlight specific values). You can find them under the drop-down that says “Format cells if:”

Is Empty & Is Not Empty Formatting Rules

These two formatting rules add a conditional format to a range of cells. They depend on whether the cell has values or is empty.

“Is empty” will add the format to any empty cell within the selected range. “Is not empty” will do the opposite.

Text Rules

There are 5 text rules in Google Sheets. See below to understand how each rule is used to format cells:

1. Text contains: Format any cell that has the mentioned text.
2. Text does not contain: Format any cell that doesn’t contain the mentioned text.
3. Text starts with: Format any cell that contains the mentioned text at the beginning.
4. Text ends with: Format any cell that contains the text mentioned at the end.
5. Text is exactly: Format any cell that only contains the mentioned text.

Date Rules

Date formatting rules format cells with specific dates as the condition. The three most popular date rules include:

1. Date is: Format cells that contain a specific date mentioned.
2. Date is before: Format cells that contain any date before the date mentioned.
3. The date is after: Format cells containing any date after the date mentioned.

Number Comparison Rules

These formatting rules work specifically with numerical values and are often used as comparison operators to create conditions. Below are the right most common number comparison rules:

1. Greater than: Format cells if 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: Format cells under the condition that the number is larger or the exact value mentioned.
3. Less than: Format cells if the number is smaller than the value mentioned.
4. Less than or equal to: Format cells if the number is smaller than the value mentioned.
5. Is equal to: Format cells under the condition that the number is the exact value mentioned.
6. Is not equal to: Format cells if the number is other than the value mentioned.
7. Is between: Format cells if the number is within the range mentioned.
8. Is not between: Format cells if the number is not within the range mentioned.

Custom Formula

Custom formulas are typically used for advanced conditional formatting (that can’t be applied using the formatting rules we’ve mentioned so far). That said, I’ve reviewed most of Google Sheets’s conditional formatting custom formula examples.

Frequently Asked Questions

What Is Google Sheets Conditional Formatting?

Conditional formatting in Google Sheets is an automatically applied formatting of cells based on requirements set by the user. You’ll 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.
2. Navigate to “Format” > “Conditional formatting.”
3. Set your conditional formatting rules.

Can I Copy and Paste Conditional Formatting into a New Google Sheet?

Yes, you can copy and paste items formatted into a new Google Sheet. Follow these easy steps:

1. Copy the cells you wish to paste.
2. Right-click and choose “Paste Special.”
3. Select “Paste Format Only.”
4. Finally, paste your selection into the new Google Sheets location.

How Do You Conditionally Change Cell Color in Google Sheets?

You can change the cell fill color under the “Formatting style” heading of the Conditional formatting menu bar.

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.” 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 on 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.

Finishing up

So there you have our guide to conditional formatting Google Sheets! If you’re looking for more tips and tricks on using spreadsheets like a boss, check out Udemy’s enormous range of Google Suite courses!

Related:

• Apply Conditional Formatting Based on Another Cell Value in Google Sheets
• How to Use Spell Check in Google Sheets to Find Misspelled Words
• How To Format Phone Numbers In Google Sheets
• A Guide to the Google Sheets IF Function