Google Sheets Conditional Formatting Based on Another Cell

Google Sheets conditional formatting based on another cell 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.

Google Sheets Conditional Formatting Based on Another Cell Value Using the Formating Menu

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. You can even use Google Sheet conditional formatting based on another column.

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 for Google Sheets to format a cell based on another cell range:

  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 as a means of conditional formatting based on another cell in Google Sheets.

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.

Google Sheet Conditional Formatting Based On Another Cell FAQ

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

Yes, you can, when applying the conditional formatting:

  1. Under the Format rules select Custom formula is from the dropdown menu
  2. Enter you IF formula in the box underneath
  3. Click Done

What Is the Difference Between Conditional Formatting and the IF Function?

Conditional formatting is a subset of rules that uses the IF function to change the format of a cell. You can use the IF function on its own without doing anything to the formatting of a cell. It just works with values unless you want it to apply conditional formatting. That being said, you can manually use the IF function to format cells, but using the Format menu is much easier.

How Do You Conditional Format a Cell Based on Another Cell’s Text?

The drop down menu in the conditional formatting menu gives you plenty of options to format based on text.
Possible Text based conditional formatting

For example, if you want to use Google Sheets conditional formatting if another cell contains text you could use text contains and put letters as the individual values.

 

Hope you found this tutorial of Google Sheets conditional formatting based on another cell useful!

Other Google Sheets tutorials you may like:

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

2 thoughts on “Google Sheets Conditional Formatting Based on Another Cell”

  1. Hi Sumit,

    So how about referring to a value on cell?
    example, my value is on cell C2, and the formatting range is on A1:A5.
    how to make it works? I’ve tried to input =A1:A5<C2 but it doesn't work.

    Regards,
    Larry

    Reply
  2. How do I do this method for text contains. Currently I’m using match function to find single values, but I want to combine this to look for any one of the values, such as (new, old, used, returned) & highlight cell if any one of these is there.

    I combining the match functions with OR, but I get formula parse error.
    =OR(MATCH(“*used*”,U2,0),MATCH(“*new*”,U2,0),MATCH(“*old*”,U2,0))

    Reply

Leave a Comment

Best Online Google Sheets Courses

Become a Google Sheets expert today!
View Course