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

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 in Google Sheets on the cell that has the name based on the score in another cell.

To boil it down, you simply have to use the Custom formula option and use the appropriate cell references in the formula you create.

In this tutorial, I will show you a step-by-step guide on how to use conditional format based on another cell.

Consider a Course

Conditional formatting based on another cell is an intermediate spreadsheet skill. If you find the guide to be beyond your current understanding, we recommend a comprehensive Google Sheets course to brush up on your overall skills first.

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 use the color scale to highlight the students’ names 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.

Below are the steps to do this:

Step 1: Select the cells that you want to highlight (student’s names in this example)

Step 2: Click the Format option

Step 3: Click on Conditional Formatting. This will open the Conditional Formatting pane on the right

Step 4: Make sure ‘Single color’ is selected

Step 5: In the ‘Format rules’ drop-down, select the ‘Custom formula is’ option

Step 6: In the ‘Value or Formula’ field, enter the following formula: =B2<35

Step 7: In the Formatting style option, select the formatting that you want to apply. In this case, I will go with the red color for our cell background color.

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

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 formatting, 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 cells A8 and A9.

Conditional Formatting Based on Another Cell Range

You can apply the above method to a cell range instead of an individual cell. You just have to use the range as the argument, like in the following example.

Step 1: Select the cells that you want to highlight.

Step 2: Click the Format option.

Step 3: Click on Conditional Formatting. This will open the Conditional Formatting pane on the right.

Step 4: Make sure ‘Single color’ is selected.

Step 5: In the ‘Format rules’ drop-down, select the ‘Custom formula is’ option.

Step 6: In the ‘Value or Formula’ field, enter the following formula: =B2:B11<50

Step 7: In the Formatting style option, select the formatting that you want to apply. In this case, I will go with the red color.

Step 8: Click on Done.

As you can see in the above image, cells A3, A4, A8, and A9 are highlighted as all the data values in B2:B11 are <50.

Conditional Formatting if Another Cell Contains Text

If you’re working with text values for conditional formatting based on another cell Google Sheets instead of numbers, then the formula will differ slightly. This is because you can’t use logical expressions like less than or greater than with text values.

Instead, you can reference the text directly or part of the text using wildcards.

Here’s how to use Google Sheets conditional formatting if another cell contains text:

Step 1: Select the cells that you want to highlight (student’s marks in this example)

Step 2: Click the Format option.

Step 3: Click on Conditional Formatting. This will open the Conditional Formatting pane on the right.

Step 4: Make sure ‘Single color’ is selected.

Step 5: In the ‘Format rules’ drop-down, select the ‘Custom formula is’ option.

Step 6: In the ‘Value or Formula’ field, enter the following formula: =A2=”Ruben Layman”

Step 7: In the Formatting style option, select the formatting that you want to apply. In this case, I will go with the red color.

Step 8: Click on Done.

The above steps would instantly highlight those cells (in red color) that have the marks of the student Ruben Layman. This way, you can use Google Sheets conditional formatting based on another cell text.

Conditional Formatting Based on Another Cell Not Being Empty

You could use the following formula after selecting Custom formula is in the conditional formatting menu.

=NOT(ISBLANK([Cell#))

Just replace Cell# with the cell reference or range.

Related Content: Color Alternate Rows in Google Sheets

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 let’s take a look at conditional formatting in Google Sheets based on other cells.

For example, suppose I have the data set 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.

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 for Google spreadsheet conditional formatting based on another cell:

Step 1: Select the cells that you want to highlight (student’s names in this example)

Step 2: Click the Format option

Step 3: Click on the Conditional Formatting option

Step 4: Make sure that the ‘Single color’ is selected (select in case it’s not)

Step 5: In the ‘Format rules’ drop-down, select the ‘Custom format is’ option

Step 6: In the ‘Value or Formula’ field, enter the following formula:

=OR(B2<35,C2<35,D2<35)

Step 7: In the Formatting style option, select the formatting that you want to apply. In this case, I will go with the red color.

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

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 in Google Sheets 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.

Frequently Asked Questions

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

Yes, you can usean IF formula with the Google Sheets conditional formatting formula based on another cell, when applying the conditional formatting rules:

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 in Google Sheets is a subset of formatting 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? / How Do I Change Cell Color in Google Sheets Based on Text Input in Another Cell?

Google Sheets can color a cell based on values from other cells. You can use the OR function to highlight a cell based on text in another cell. You’d use the following syntax:

=OR(Cell#,"text")

Just replace Cell# with the cell reference or range and text with the appropriate text you’re checking against. Like in the following example:

How Do I Apply Conditional Formatting to a Column Based on Another Column?

Google Sheets conditional formatting based on another column can be set by clicking on the column header and then heading to Format > Conditional formatting.

Afterward, select Custom formula is and write a formula to check against a row. In the below example, we checked all of row N for figures greater than 5.

Wrapping up Conditional Formatting Google Sheets Based on Another Cell

Hope you found this tutorial on Google Sheets conditional formatting based on another cell useful! Just remember you choose your own custom formula when working with other cells, and you should be able to figure it all out.

If this tutorial is to your liking, you’ll love our Google Sheets paid templates. You can use the code SSP to save 50% on all templates.

Other Google Sheets tutorials you may like:

• • How to Copy Conditional Formatting in Google Sheets
  • How to Show Negative Numbers in Red in Google Sheets
  • How to Zoom-In and Zoom-Out in Google Sheets (Shortcuts)
  • How to Apply a Formula to an Entire Column in Google Sheets
  • How to Highlight Highest or Lowest Value in Google Sheets