The Ultimate Guide to Using Conditional Formatting in Google Sheets

Conditional Formatting in Google Sheets can be used to highlight/format cells based on the values in it.

For example, if you have the scores of 50 students in a subject, you can quickly highlight the students who have scored less than 35 in the exam.

Similarly, you can use many such conditions (as we will see later in this tutorial) to highlight cells based on the value in it.

How to Access Conditional Formatting in Google Sheets

Conditional formatting can be accessed by going to Format → Conditional Formatting.

Conditional Formatting in Google Sheets - Select CF

This opens the ‘Conditional format rules’ pane on the right that you can use to set the rules.

Now there are two options available to you when you use conditional formatting in Google Sheets:

  • Single Color
  • Color Scale

Conditional Formatting in Google Sheets - single color

‘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.

‘Color scale’ can be used when you also want to visually present the difference between the values in the cells. For example, a score of 34 would be a lighter shade of red and 10 would be a darker shade of red.

Conditional Formatting in Google Sheets – Examples

Here are some practical examples of using conditional formatting in Google Sheets.

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.

Conditional Formatting in Google Sheets - less than 35 dataset

To do this:

  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.Conditional Formatting in Google Sheets - less than
  6. A new field would appear right below the drop down. Enter 35 in it.Conditional Formatting in Google Sheets - 35
  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).

Conditional Formatting in Google Sheets - less than highlighted

Example 2: Create a Heatmap Using the Scores

Taking the same data set as in Example 1, let’s see how to create a heat map in Google Sheets using Conditional Formatting.

A heat map would show a gradient of colors based on the values in the cells (something as shown below)

Conditional Formatting in Google Sheets - heat map.

Here are the steps to create the heat map using the exam scores:

  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 the 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: 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.

Conditional Formatting in Google Sheets - duplicate dataset

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)>1Conditional Formatting in Google Sheets - countif formula
  7. Select the format.
  8. Click OK.

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

Conditional Formatting in Google Sheets - duplicate highlighted

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)

Highlighting alternate rows is often used in cases where the reports are to be printed.

It increases the readability of the report and gives it a more professional look (something as shown below).

Conditional Formatting in Google Sheets - highlight alternate rows

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

Here are the steps to highlight alternate cells using conditional formatting in Google Sheets:

  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 ‘Custom formula is’ option.
  6. Enter the following formula in it: =MOD(ROW(),2)=1.Conditional Formatting in Google Sheets - mod formula
  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 easy to scan and identify blank cells when the data set is small, in case of hundreds of records, using conditional formatting to do this is the way to go.

Suppose you have a dataset as shown below:

Conditional Formatting in Google Sheets - blank dataset

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

  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 ‘Custom formula is’ option.
  6. Enter the following formula in it: =ISBLANK(A2)Conditional Formatting in Google Sheets - isblank formula
  7. Select the format.
  8. Click OK.

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

Conditional Formatting in Google Sheets - blank highlighted

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:

Conditional Formatting in Google Sheets - error dataset

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

  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 ‘Custom formula is’ option.
  6. Enter the following formula in it: =ISERROR(A2)Conditional Formatting in Google Sheets - iserror formula
  7. Select the format.
  8. Click OK.

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

Conditional Formatting in Google Sheets - error highlighted

Example 7: Highlight Cells that Contain the Searched String

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

Something as shown below:

Conditional Formatting in Google Sheets Search

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:

  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 ‘Custom formula is’ option.
  6. Enter the following formula in it: =AND(NOT(ISBLANK($C$2)),ISNUMBER(SEARCH($C$2,A2)))Conditional Formatting in Google Sheets - Search Formula
  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.

You May Also Like the following tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X

Leave a Comment: