Conditional Formatting in Google Sheets: Full Guide

I’ve compiled a comprehensive conditional formatting Google Sheets guide to help you learn everything you need to know. This guide covers conditional formatting, when and why you should use it, the different types, and several step-by-step examples.

But you can get started with basic spreadsheet conditional formatting by highlighting the cells and navigating to “Format” > “Conditional formatting.” If you need more help, read on to learn more.

What Is the Conditional Formatting Google Sheets Function?

The conditional formatting Google Sheets function automatically changes the formatting of a specific row, column, or cell based on your set rules. This feature uses visualization to make the essential data in your Google Sheets stand out more.

Coloring the cells draws attention to the specific values you want to stand out from the rest of the data. In the example above, I used the rule >3 to highlight all cells containing data greater than 3.

Using this feature, you can make your data more appealing and human-readable. It makes it easier to notice important data points more quickly and can even highlight data points you want to remove, like unintentional duplicates.

Don’t forget to watch my latest video on using the conditional formatting Google Sheets feature. Hit “Play” to watch below.

When to Use the Conditional Formatting Google Sheets Feature

The conditional formatting Google Sheets feature can be applied in different workflows. It visualizes the information required for the work, including data patterns, good news, troubling trends, or flawed or faulty data.

Here are some of the situations where you can use Google Sheets conditional formatting:

• Conditional formatting in Google Sheets can help a sales manager gain quick insights into high- or low-value sales data.
• Accountants in any workplace can use Google Sheets to apply conditional formatting to the data, highlighting all the negative values with red highlights in their profit or loss calculations.
• Conditional formatting project managers can use Google Sheets to understand the resource utilization of their business.
• Teachers can use it to quickly identify grades or heat maps of overall class scores.

This feature also makes it easy to keep track of all your goals by providing visual indications of a business’s progress against some specific metrics.

In some large corporations, daily or weekly reports are essential to keeping things in check. It can be challenging to keep track of things if they are not formatted conditionally, especially if you are busy with other things and may not notice trends in your existing data.

Therefore, conditional formatting comes in handy whenever you need to make your work more productive while keeping the essential things in check by using Google Sheets tables.

Related: Check if a Value Exists in Excel

Types of Conditional Formatting in Google Sheets

There are essentially two types of conditional formatting Google Sheets features. Let’s examine both.

Single-Color Conditional Formatting Google Sheets Quick Guide

Data in an individual cell within the specified range is evaluated in single-color conditional formatting. If it passes the formatting rule, the formatting style only applies to a cell. The formatting can be true or false, so the condition is either applied or not.

Here is how to add the conditional formatting Google Sheets feature:

1. Choose the “Format” option from the menu.
2. Click on the “Conditional formatting” option.
3. Now select the “Single color” option.

4. Select the formatting rules and style.

The “Format cells if…” setting is the most essential section of single-color conditional formatting. This section has several formatting rules. By selecting the headers for a column or row, you can apply conditional formatting to that column or row.

If you find that a little confusing, don’t worry. I’ll take you through the rules later in this article.

Color Scale in Conditional Formatting Google Sheets

One of the best ways to use conditional formatting is with color scale conditional formatting Google Shets feature. Although it might sound difficult to use, it is pretty straightforward. Shown below are some examples of using color scales in the dataset:

To use color scale conditional formatting:

1. Highlight the data to which you want to apply conditional formatting.
2. Click “Format” from the menu bar.

3. Now choose the “Conditional formatting” option.
4. You will see the “Conditional format rules,” in the top right of this section, click on the “Color Scale” option.
   

   

5. (Optional) Adjust the colors of the “Minpoint, Midpoint, and Maxpoint.”
   • This will alter the color scale for the formatting rule.
   • It’s green to white by default.

6. Click “Done.”

Note: The Minpoint, Maxpoint, and Midpoint can be changed to flat numbers, percentiles, or flat percentages. The properties of Min and Max values can also be set in conditional formatting.

Related: Highlight the Highest Value in Google Sheets

Rules Used in Conditional Formatting Google Sheets

You can use conditional formatting in your spreadsheet in several ways. In the previous heading, we looked at the types of conditional formatting. Now, let’s look at what kind of rules you can use in conditional formatting.

Text Rules

Text rules usually only apply to cells formatted for non-numerical data. However, they can be used on any cell.

For example, we can identify the dates in a column with the days that start with ‘1’. First, we have to choose the data range to select the dates.

Then, we must start the text with the rule of ‘1’. This will highlight the dates in columns that begin with ‘1’.

Conditional formatting is only applied if:

• Is empty: The cells are empty or only contain spaces.
• Is not empty: The cells have no characters other than the spaces.
• Text does not contain: The cell’s value does not include the defined input.
• Text contains The cells’ values, including the input, which is defined.
• Text is exactly: The value in the cells matches the defined input.
• Text starts with: The cell values begin with the input, which is defined.
• Text ends with: The cell’s value ends with the input, which is defined.

Date Rules

The date rule is a crucial rule for conditional formatting in Google Sheets. There are three date rules in total.

Note: For date input in Google Sheets, the best method is to use the DATE function:

=DATE(year, month, day)

Using this function will prevent confusion, as people often interchange the month and the day due to different region formats. This can be especially useful if you collaborate with teams in other regions and wish to adhere to a specific date format.

There are several filters we can use, such as:

• The date is: The cell’s date value matches the data, which is defined precisely.
• The date is before: The cell’s date value is less than the defined data.
• The date is after: The cell’s date value is larger than the defined data.

Number Rules

The number rules let you apply conditional formatting on mathematical ranges and comparisons. These include greater than, in between, or less than.

There are several filters you can use to format the numbers. Some of these are:

• Greater than: The cell’s value is larger than the defined input.
• Greater than or equal to: The cell’s value is equal to or larger than the defined input.
• Less than: The cell’s value is less than the defined input.
• Less than or equal to: The cell’s value equals or less than the defined input.
• Is equal to: The cell’s value is equal precisely to the defined input.
• Is not equal to: The cell’s value is not equal precisely to the defined input.
• Is between: The cell’s value is between the two defined inputs, including the two ends.
• Is not between: The cell’s value is not between the two defined inputs, inclusive on the two ends.

Related: How to Highlight Highest or Lowest Value in Google Sheets (Easy Way)

Custom Formula

Conditional formatting Google Sheets is a compelling feature when used independently. However, you can only harness the full potential of conditional formatting if you use custom formulas with conditional formatting.

To use custom formulas:

1. Open the “Conditional Format Rules” Google Sheets menu.
2. In the “Format rules” section, select the “Custom Formula is” option under “Format cells if.” This will display a textbox where you can add your custom formula.
3. In the textbox, add your custom formula.
4. Choose the formatting options under it and click on “Done” to apply the changes.

I cover this extensively in my guide to the ISERROR function.

Prerequisites to Use Conditional Formatting Google Sheets

You should ensure a few things to avoid overlaps or errors when using conditional formatting. Let’s take a look at some of them:

• Make sure there are no missing or extra spaces in the text.
• There should not be any unwanted conditional formatting rules already applied, as they may overlap.
• Avoid selecting the headers when selecting the cell range, which can lead to false flagging.

Google Sheets looks for an exact match, so missed or extra spaces are essential to consider. You can remove the additional spaces in your text using the CLEAN or TRIM functions in Google Sheets.

How To Use Conditional Format in Google Sheets

Applying conditional formatting to your spreadsheet is easy and requires you to follow a few simple steps. But you can use conditional formatting differently, so let’s look at some detailed step-by-step examples to help you understand better.

Google Sheets Color Cells Based on Value

For this example, we can take a spreadsheet containing 20 students and the marks they achieved on a test. Let’s look at the conditional formatting of single color and color scales in Google Sheets.

Single Color Conditional Formatting Google Sheets

Let’s say we wanted to highlight students who got 40 or fewer marks in the test. To apply the single-color conditional formatting in Google Sheets for this scenario, you would follow these steps:

1. Click and drag to select the cells containing the marks.
2. With the cells selected, click the “Format” button in the main toolbar.
3. There, click on “Conditional formatting” in the dropdown menu. This will open the conditional formatting menu in a sidebar on the right side of the window. Make sure the “Single color” tab is selected.
4. We already selected the cell range on which we want to apply the conditional formatting.
   • The range will automatically be added to the “Apply to range” textbox.

5. Select “Less than or equal to” in the “Format cells if” menu.
   • This will ensure that the color is only applied to cells with a value less than or equal to the specified value.
   • This will show another text box allowing you to enter the value.
   • For this dataset, we want to highlight the marks equal to or under 40.

6. Under the “Formatting style” options, you can change the fill and text color and text emphasis.
7. When you’ve customized the conditional formatting options, click the green “Done” button to save the changes.

We formatted the cells so that the marks of the students who scored 40 or lower were highlighted in red.

This makes it very easy to see which students were below the passing grade of 40 in a classroom context. You could follow similar steps and highlight students who got over 50 by selecting “Greater than or equal to” in the “Format cells if” menu.

Color Scale Conditional Formatting Google Sheets

The benefit of color scale conditional formatting Google Sheets is that the cells are highlighted using a heatmap, which helps you understand how close a value is to the minimum and the maximum values. Here is how to do this:

1. Click and drag to select the cells containing the marks.
2. With the cells selected, click the “Format” button in the main toolbar.
3. There, click on “Conditional formatting” in the dropdown menu. This will open the conditional formatting menu in a sidebar on the right side of the window.
   • Make sure the “Color scale” tab is selected.

4. To select the color you want to apply, click on the “Preview” button and select the color scheme you want to apply.
   • You can also use the “Custom color scale” option to create a new color template.

5. Now select the “Minpoint, Midpoint, and Maxpoint” parameters.
   • These parameters can be “Number, Percent, or Percentile.”

6. When you’ve customized the conditional formatting options, click the green “Done” button to save the changes.

Conditional formatting so that the values to the closest max point are almost white while the marks closer to the min point are shown as red allows you to see the spread of the students’ marks easily.

Custom Formula Conditional Formatting Google Sheets

Highlighting Empty Cells

You can also use custom formulas in conditional formatting in Google Sheets. If the formula returns as positive, then the conditional formatting condition will be returned as positive.

In the demonstration below, some of the cells are empty. We will use the ISBLANK formula to highlight them to check if the specified cell is empty. If the condition returns as positive, the cell will be highlighted.

Here is how to do this:

1. Click and drag to select the cells containing the marks, and click the “Format” button in the main toolbar.
2. Now, click on “Conditional formatting” in the dropdown menu.
   • This will open the conditional formatting menu in a sidebar on the right side of the window.

3. Here, in the “Format rules” section, select the “Custom Formula is” option for the “Format cells if” option.
   • This will show a textbox allowing you to add your custom formula.

4. In this example, we will use =ISBLANK(B2). You will see the conditional formatting applied instantly.
5. Click on the green “Done” button to save the changes.

The blank cells will be highlighted instantly.

Related: Format Painter in Google Sheets: Copy Conditional Formatting

Highlighting Cells With Errors

The chance of an error occurring during a calculation increases whenever you import data from text or Excel files.

Going through the spreadsheet to find the error manually can take time and be incredibly dull. You can use the ISERROR custom formula for conditional formatting to highlight all the cells that contain an error.

To do this:

1. Click and drag to select the data-containing cells, then click the “Format” button in the main toolbar.
2. Now, click on “Conditional formatting” in the dropdown menu.

3. Here, in the “Format rules” section, select the “Custom Formula is” option for the “Format cells if” option.
   • This will show a textbox allowing you to add your custom formula.

4. Here, we will use =ISERROR(B2) in this example. You will see the conditional formatting applied instantly.
5. Click on the green “Done” button to save the changes.

The cells containing any errors will be highlighted in your chosen color.

Highlighting Alternating Rows

Not only can you use conditional formatting to highlight specific data, but you can also use it to make your spreadsheets look visually appealing.

Highlights in alternating rows are commonly used when reports need to be printed. This format makes the information easy to follow and gives the reports a professional look.

Although you can do this automatically with a few clicks, let’s take a look at how you can use conditional formatting so you can learn the spreadsheet skills behind it:

1. Click and drag to select all cells containing the data.
   • Select all the cells, not just the ones containing the data.

2. Click the “Format” button in the main toolbar.
3. Now, click on “Conditional formatting” in the dropdown menu.
4. Ensure the “Single color” tab is selected in the “Conditional format rules.”
5. As we selected the cell range in the first step, it will automatically be applied in the “Apply to range text” box.

6. In the “Format rules” section, select the “Custom Formula is” option for the “Format cells if” option.
   • This will show a textbox allowing you to add your custom formula.

7. We will enter the =ISEVEN(ROW()) nested formula in the textbox.
8. Under the “Formatting style” options, you can change the fill and text color and text emphasis.
9. Press the green “Done” button to save the formula.

You can repeat the same steps but use the =ISODD(ROW()) formula to apply the condition to the other rows.

Conditional Formatting Google Sheets: Based on Another Cell

A great way to use conditional formatting is to highlight cells based on input in another cell in the spreadsheet.

One way to do this is to make a database that you can search. Taking the same example of the students and their marks, let’s say we wish to find a student who scored 27.

To do this, we will type the student’s marks in one of the designated cells. Google Sheets will then check the marks column to see if there is a match.

As soon as a match is found, it will be highlighted in the color specified by the user. Here are the steps you need to follow to do this:

1. Add the score you’re searching for in cell D2, 27.
2. Click and drag to select the cells containing the marks, and click the “Format” button in the main toolbar.
3. Now, click on “Conditional formatting” in the dropdown menu.
   • This will open the conditional formatting menu in a sidebar on the right side.

4. In the “Format rules” section, select the “Custom Formula is” option for the “Format cells if” option.
   • This will show a textbox allowing you to add your custom formula.

5. We are going to use a nested formula for the custom formula. It is:

   =AND(NOT(ISBLANK($D$2)),ISNUMBER(SEARCH($D$2,B2:B21)))

6. Under the “Formatting style” options, you can change the fill and text color and text emphasis.
7. When you’ve customized the conditional formatting options, click the green “Done” button to save the changes.

Note that we use dollar signs to make absolute references for some of the cells. If you need to alter the formula, make sure you still use this instead of relative references.

There are plenty of other Google Sheets conditional formatting formulas that you can use in the “Custom formula is” box. It would be impossible to cover all of them in one article, but let us know in the comments if you need anything specific answered.

Now, to see the Google Sheets conditional formatting color scale based on another cell in action, you can change the number in the designated search box and see the matching marks in the highlighted marks.

How to Highlight Based on a Checkbox in a Different Column

1. Select the cells to which you want to apply conditional formatting.
2. Navigate to “Format” > “Conditional formatting,”
3. Change the “Format Rules” to “Custom formula is.”
4. Type an equals symbol and the cell reference with the first checkbox.
   • Then, type another equals symbol, followed by TRUE to highlight specific checked cells.
   • Alternatively, type FALSE to highlight unchecked cells.
   • In my example, it becomes: =C2=TRUE or =C2=FALSE

5. Click “Done.”

How to Apply More Than One Conditional Formatting Rule

To create Google Sheets conditional formatting with multiple conditions:

1. Click and drag over the cells.
2. Navigate to “Format” > “Conditional formatting.”
3. Click “Add another rule.”
4. Add the new rule to the selected range.

You can repeat this process for Google Sheets multiple conditional formatting as many times as you’d like.

How to Remove Conditional Formatting in Google Sheets

To remove conditional formatting rules, click the trash icon next to the rule in the conditional formatting menu.

You could also highlight the data range and then navigate to “Format”> “Clear formatting.” This will remove the cell’s background color, whether it was a conditional formatting rule or any other formatting.

Frequently Asked Questions

What Is Conditional Formatting Google Sheets?

The conditional formatting feature in Google Sheets automatically changes the font or color of specific cells, rows, and columns based on whether a predefined condition is fulfilled. This feature is excellent for visualization as it helps make your data stand out.

It can help draw the viewer’s attention to specific spreadsheet parts, making certain parts stand out more.

How Can I Use the Conditional Formatting Google Sheets Feature?

To access the primary conditional formatting Google Sheets menu, follow these simple steps:

1. Click on “Format” in the main top bar.
2. In the dropdown menu, click on “Conditional formatting.” This will open a sidebar towards the right side of the screen.
3. Select between “Single color” or “Color scale.”
4. Enter the cell range in the “Apply to range” textbox with the selected type.
5. Set the conditional formatting rules in the “Format rules” option.
6. Click on “Done” once you’re done making the changes.

Can I Use an IF Formula in Conditional Formatting Google Sheets?

You can use the IF formula in conditional formatting Google Sheets. Follow these steps:

1. Open the “Conditional formatting” menu.
2. Choose the “Custom formula is” option under the “Format cells if” option.
3. However, one thing to note is that you can not use the IF THEN ELSE syntax.
4. Instead, you can only use the IF THEN test.

How Do You Use Conditional Formatting Google Sheets Option if Another Cell Contains Text?

You can use several nested formulas to highlight another cell if another cell contains the text. This can be great if you wish to create a searchable database where you can enter the text, and Google Sheets will look through the data columns to find the matching value.

Wrapping Up

Hopefully, everything I covered in the conditional formatting Google Sheets guide above wasn’t too overwhelming. If so, please ask us any questions in the comments section. Alternatively, you may be interested in taking a complete beginner’s guide to Google Sheets to brush up your overall skills (formatting, calculating interest, building spreadsheet templates, and more) and prepare you for more difficult Google spreadsheet guides in the future.

Related:

• How to Freeze Rows in Google Sheets
• How to Clear Formatting in Google Sheets (Step-by-Step)
• Apply Conditional Formatting based on Another Cell Value in Google Sheets
• How to Merge Cells In Google Sheets
• Formula to Highlight Duplicates in Google Sheet
• How to Copy Conditional Formatting in Google Sheets
• How to Show Negative Numbers in Red in Google Sheets
• How to Zoom-In and Zoom-Out in Google Sheets (Shortcuts)
• How to Apply a Formula to an Entire Column in Google Sheets
• How to Highlight Highest or Lowest Value in Google Sheets
• Import Excel to Google Sheets [Easy Step-by-Step Guide]
• 2 Easy Ways To Change Google Sheets Cell Padding