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.
Conditional formatting can be accessed by going to Format → Conditional Formatting.
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’ 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.
Here are some practical examples of using conditional formatting in Google Sheets.
Suppose you have the data set as shown below and you want to quickly highlight the cells where the score is less than 35.
To do this:
This would instantly highlight all the cells with a score less than 35 in red color (as shown below).
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)
Here are the steps to create the heat map using the exam scores:
This will create a heat map with the gradient shown based on the value in the cell.
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:
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.
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).
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:
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
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:
Here are the steps to highlight all the blank cells in this data set.
This would instantly highlight all the blank cells in the data set (as shown below).
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.
This would instantly highlight all the cells that have any error in it (as shown below).
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:
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:
You May Also Like the following tutorials: