Apply Conditional Formatting based on Another Cell Value in Google Sheets

Conditional Formatting in Google Sheets works by analyzing the value in the cell and then formatting these cells based on the given condition.

In most cases, you would use the current value of the cell to apply the conditional formatting in it, but you can also use this to apply conditional formatting based on another cell value.

For example, if you have the scores of students in a subject, you can highlight those students who have scored less than 35 or those who have scored more than 80. In this case, we are applying the conditional formatting on the cell that has the name based on the score in another cell.

In this tutorial, I will show you how to use conditional formatting to highlight cells based on another cell value.

Highlight Cells Using Conditional Formatting Based On Another Cell Value in Google Sheets

Let’s take an example of a student’s score and see how you can highlight the names of the students based on their scores.

Suppose you have the dataset as shown below and you want to highlight names where the score is less than 35.

Dataset to highlight cells using conditional formatting

Below are the steps to do this:

  1. Select the cells that you want to highlight (student’s names in this example)
  2. Click the Format optionClick the Format tab
  3. Click on Conditional Formatting. This will open the Conditional Formatting pane on the rightClick on the Conditional Formatting option
  4. Make sure ‘Single color’ is selectedSelect Single Color in Conditional Formatting pane
  5. In the ‘Format rules’ drop-down, select the ‘Custom formula is’ optionSelect Custom Formula is
  6. In the ‘Value or Formula’ field, enter the following formula: =B2<35Enter the formula in the Conditional formatting field
  7. In the Formatting style option, select the formatting that you want to apply. In this case, I will go with the red color.Select the formatting to highlight the cell based on the value
  8. Click on Done.

The above steps would instantly highlight those cells (in red color) that have the names of the students who have scored less than 35.

conditional formatting applied based on another cell - marks less than 35

Now let me quickly explain how this works.

Since I have selected the cells with the names of the students, these will be the cells that are highlighted.

And for conditional formating to decide whether to apply the selected formatting to a cell or not is to simply check the condition specified. If the condition returns TRUE for the cell, then it applies the formatting, else it doesn’t.

In our case, I have used the formula, =B2<35 to check each cell.

So when conditional formatting is checking cell A2, it checks the condition =B2<=35, and when it ‘s checking cell A3, it checks the condition =B3<=35, and so on.

Since the value in cell B3 is 27, the condition =B3<35 returns TRUE and hence cell A3 is highlighted. Similarly, it also highlights cell A8 and A9.

Highlight Cells Using Conditional Formatting Based On Multiple Other Cell Values in Google Sheets

In the above example, I showed you how to highlight a cell based on the value in some other cell.

You can also extend the same logic and highlight a cell based on the values in multiple other cells.

For example, suppose I have the dataset as shown below and I want to highlight all the student names where they have scores less than 35 in any of the three subjects.

Dataset with three subject marks to be highlighted

For this to work, I would have to analyze three different cell values and highlight the student name if any of the cell value (score) is less than 35.

Below are the steps to do this:

  1. Select the cells that you want to highlight (student’s names in this example)
  2. Click the Format option
  3. Click on the Conditional Formatting option
  4. Make sure that the ‘Single color’ is selected (select in case it’s not)
  5. In the ‘Format rules’ drop-down, select the ‘Custom format is’ option
  6. In the ‘Value or Formula’ field, enter the following formula: =OR(B2<35,C2<35,D2<35)Formulat to highloght score less than 35 in any subject
  7. In the Formatting style option, select the formatting that you want to apply. In this case, I will go with the red color.
  8. Click on Done.

The above steps would analyze each cell for a student’s name and in case any of the subject scores are less than 35, the cell would be highlighted in red.

Apply Conditional Formatting based on Another Cell Value in Google Sheets - if scoree less than 35

You can also use the below formula instead of the formula used above:

=OR(ARRAYFORMULA(B2:D2<35))

So there are two examples where I have used a simple formula to apply Conditional Formatting based on another cell value (or multiple cell values) in Google Sheets.

One great thing about Conditional Formatting is that it’s dynamic and would automatically update and highlight the cells with student names who have scored less than 35 in any one subject.

You can also use the same steps above to apply multiple conditional formatting rules. For example, if you also want to highlight all the cells where students have scored more than 80 in all subjects, you can simply use another formula. Each cell would then be analyzed for both the conditions and highlighted accordingly.

Hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

Leave a Comment