How to use conditional formatting based on another cell in Google Sheets?

by Nov 8, 2017

Conditional formatting based on another cell

There are times when we need to look at the mounds of data on our daily spreadsheets. However, without any design elements or visual cues, the scouring the data can be stressful to the eyes. Fortunately, though, we can change the cell colors based on the cell value in Google Sheets application! This functionality is called conditional formatting based on another cell. Let us explore this with the help of a couple examples.

Example 1: Identify the cells below a threshold

Please consider the following spreadsheet. The warehouse supervisor, at a local automobile service station, uses it to track inventory of the spares.

Conditional Formatting Based On Another Cell: Product Inventory Sample Data in Google Sheets

He needs to place a new order for a product if the quantity in stock (column B), falls below the threshold of 20. There are a large number of products and the values in column B change over time. So, he finds it difficult to identify the products that are going out of stock. Even if he were to color the cells manually to easily fish out the cells later, that would double up his work. So, that is not an ideal solution.

The conditional formatting functionality comes to our rescue, with which we can change the cell colors based on the cell value in Google Sheets. To apply this formatting, first, we will select all the cells in column B (i.e. range B2:B900).

Now navigate to Format > Conditional formatting. A sidebar opens up on the right side of the screen. We’ll notice that in the Apply to range field, the value is already set to B2:B900. This is because we have already selected the data. In any case, if we wish to change the range, we can do that.

We will choose Less than or equal to in the Format cells if drop-down field. And then, key in the value 20 in the immediate next field. Essentially we are configuring Google Sheets to format cells in a certain way if it satisfies this condition. We’ll choose a Formatting style that’s on the reddish side, so as to suit the situation. Finally, we click on the Done button.

This is just a one-time configuration and automatically updates the formatting even if there are future changes.

We used a direct condition for this particular example. We can also change the cell colors based on the cell value in Google Sheets, by making use of a formula as well. For that, we just have to ensure the value in the Format cells if the drop-down field is Custom formula is, and accordingly key in the formula in the immediate next field.

Example 2: Heatmaps

Here’s a spreadsheet that holds the scores of fifth-grade students in a school.

The class teacher wants to quickly zero in on those students who scored on the higher side. To the naked eye, this is difficult. Color scale within the conditional formatting functionality can help us here. Just like we did in the previous example, we will select all the cells (F2:F31) and navigate to Format > Conditional formatting. Then we select the Color scale tab on the sidebar that pops on the right side of the screen.

We make sure the fields are as shown in the snapshot below, and click Done. Instantly we see that the conditional formatting utility did change the cell colors based on the cell value in Google Sheets. Higher values are in green, while the lower values are in red.

Congratulations! We successfully used the conditional formatting to our benefit to aid our eyes with visual cues that make the data identification process a lot easier!

Learn more about alternated colors in the following blog post: How to create an alternated colors list in Google Sheets?


Featured image designed by ijeab / Freepik

Share This