Conditional Formatting in Google Sheets (Easy 2023 Guide)

Learning how to use conditional formatting in Google Sheets can be a daunting task. Lucky for you, we’ve put together a comprehensive guide to help you learn everything you need to know to get started using Google Sheets conditional formatting. In this guide, we’ll cover what conditional formatting is, when and why you should use it, the different types, and several step-by-step examples. Read on to learn more.

What Is Conditional Formatting in Google Sheets?

Conditional formatting in Google Sheets is a feature that automatically changes the formatting of a specific row, column, or cell, based on the rules you have set. This feature uses the power of visualization to make the important data in your Google Sheets stand out more.

For example, coloring the cells draws attention to specific values that you want to stand out from the rest of the data. 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.

When to Use Google Sheets Conditional Formatting

Conditional formatting can be applied in many different workflows. It is used to visualize the information required for the work. It can be patterns in the data, good news, troubling trends, or even flawed or faulty data. It is quite possible that no other kind of formula or feature in Google Sheets can be that versatile and applied to many applications.

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

• Conditional formatting can be useful for a sales manager to gain quick insights from the sales data that are high or low.
• Accountants in any workplace can apply conditional formatting to the data to highlight all the negative values with red highlights in their profit or loss calculations.
• Conditional formatting can be used by project managers to understand the resource utilization of their business.
• Teachers can use it to identify grades or heat maps of overall class scores quickly.

This feature also provides an easy way 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 difficult to keep track of things if they are not formatted conditionally, especially if you are busy with other things and may not even notice trends in your existing data.

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

Types of Conditional Formatting in Sheets

There are essentially two types of conditional formatting. Let’s take a look at both of them.

Single-Color Conditional Formatting

Every individual cell’s data in the specified range is evaluated in single-color conditional formatting. The Formatting style is only applied to a cell if it passes the formatting rule. Essentially the formatting can be true or false, so the condition is either applied or not applied.

Here is how you can set up the single-color conditional formatting:

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… is the most essential section of single color conditional formatting. This section has several formatting rules that can be used. If you find that a little confusing, don’t worry. We will take a look at these rules later in this article.

Color Scale Conditional Formatting

One of the best ways to use conditional formatting in Google Sheets is with color scale conditional formatting. 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 you want to apply conditional formatting to.
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.

Rules Used in Conditional Formatting

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 take a look at what kind of rules you can use in conditional formatting.

Text Rules

Text rules usually only apply to the cells that have been formatted for non-numerical data. However, it is possible to use them on any type of cell. For example, we can identify the dates in a column with the days that start with ‘1’. We first have to choose the range of the data so that dates can be selected. Then we have to set the Text starts with the rule to ‘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 any 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 with the input, which is defined.
• 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 key rule for conditional formatting in Google Sheets. There are a total of three date rules.

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

=DATE(year, month, day)

Using this function, you will be saved from 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 stick 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 input which is defined.
• Greater than or equal to: the cell’s value is equal 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 is equal to or less than the defined input.
• Is equal to: the cell’s value is equal exactly to the defined input.
• Is not equal to: the cell’s value is not equal exactly to the defined input.
• Is between: the cell’s value is in between the two defined inputs; inclusive on the two ends.
• Is not between: the cell’s value is not in 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 is an extremely powerful feature in Google Sheets when used on its own. 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.
2. In the Format rules section, select the Custom Formula is option under Format cells if. This will show a textbox allowing you to 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.

Prerequisites to Use Google Sheets Conditional Formatting

You should ensure a few things to avoid overlaps or any errors when you use 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, as it can lead to false flagging.

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

How to Conditional Format in Google Sheets

Applying conditional formatting to your spreadsheet is easy and requires you to follow a few simple steps. But there are tons of different ways you can use conditional formatting, 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 both the single color and color scale conditional formatting in Google Sheets.

Single Color Conditional Formatting

To apply the single-color conditional formatting in Google Sheets, 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. As 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. Now, select Less than or equal to in Format cells if menu. This will ensure that the color is only applied to the cells that are 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’re done customizing the conditional formatting options, click on the green Done button to save the changes.

We formatted the cells in a way that marks of the students who scored 40 or lower were highlighted in red. In the context of a classroom, this makes it very easy to see which students were below the passing grade of 40.

Color Scale Conditional Formatting

The benefit of using color scale conditional formatting 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 wish 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’re done customizing the conditional formatting options, click on the green Done button to save the changes.

Conditionally 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. To highlight them, we are going to use the ISBLANK formula 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. Here, we will use =ISBLANK(B2) in this example. 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 a huge chunk of your time and can be incredibly boring. Thankfully, 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 cells containing the data, and 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 simply make your spreadsheets look visually appealing. Highlights in alternating rows are commonly used when reports need to be printed. It makes the information easy to follow and gives them 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. Make sure to select all the cells and 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. Make sure 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. 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.
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.

Google Sheet Conditional Formatting 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 cyou 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 simply type in the student’s marks in one of the designated cells. Sheets will then check the marks column to check 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 in our case.
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 of the window.

4. 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.
5. For the custom formula, we are going to use a nested 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’re done customizing the conditional formatting options, click on the green Done button to save the changes.

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 you should see the matching marks in the marks column highlighted.

Frequently Asked Questions

What Is Google Sheets Conditional Formatting?

The conditional formatting feature in Google Sheets automatically changes the font or color of specific cells, rows, and columns based on if a predefined condition is fulfilled. This feature is great for visualization as it helps make your data stand out. It can help draw the viewer’s attention to specific parts of the spreadsheet making certain parts stand out more.

How Can I Use Conditional Formatting in Sheets?

To access the basic conditional formatting menu in Google Sheets, click on Format in the main top bar. In the dropdown menu, click on Conditional formatting. This will open a sidebar towards the right side of the screen. Here, select between Single color or Color scale. With the type selected, enter the cell range in the Apply to range textbox. Set the conditional formatting rules in the Format rules option. Click on Done once you’re done making the changes.

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

You can use the IF formula in conditional formatting. Open the Conditional formatting menu and choose the Custom formula is option under the Format cells if option. However, one thing to note is that you can not use the IF THEN ELSE syntax. Instead, you can only use the IF THEN test.

How Do You Conditional Format 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 we covered above about conditional formatting in Google Sheets wasn’t too overwhelming. If so, feel free to ask us any questions in the comments section. Alternatively, you may be interested in taking a complete beginners guide to Google Sheets to brush up your overall skills 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]