Have you ever wanted to highlight certain cells in Google Sheets based on the value of another cell? Well, you’re in luck! Conditional formatting in Google Sheets allows you to analyze the value in one cell and format other cells accordingly. This powerful feature can be used to apply custom formatting based on the content of another cell.
Let’s say you have a spreadsheet with student scores in a subject, and you want to highlight the students who scored less than 35 or those who scored more than 80. By using conditional formatting in Google Sheets, you can achieve this effortlessly.
Consider a Course
Conditional formatting based on another cell is an intermediate skill in spreadsheets. If you find this guide beyond your current understanding, we recommend taking a comprehensive course on Google Sheets to enhance your overall skills.
Conditional Formatting in Google Sheets Based on Another Cell Value Using the Formatting Menu
Let’s take the example of student scores and see how you can use the color scale to highlight student names based on their scores.
Assuming you have a dataset like the one below and you want to highlight names when the score is less than 35.
Here are the steps to follow:
Step 1: Select the cells you want to highlight (student names in this example)
Step 2: Click on 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: From the “Formatting rules” drop-down menu, select “Custom formula is”
Step 6: In the “Value or formula” field, enter the following formula: =B2<35
Step 7: In the “Formatting style” option, select the formatting you want to apply. In this case, I’ll use the color red for the background color of our cell.
Step 8: Click “Done”
The above steps will instantly highlight the cells (in red color) that contain the names of students who scored less than 35.
Now, let me explain how this works.
Since I selected the cells containing the student names, those are the cells that will be highlighted.
And for conditional formatting, to decide whether the selected formatting should be applied or not to a cell, it simply checks the specified condition. If the condition returns TRUE for the cell, then the formatting is applied; otherwise, it’s not.
In our case, I used the formula =B2<35 to check each cell.
So, when the conditional formatting checks cell A2, it checks the condition =B2<=35, and when the conditional formatting checks 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 thus cell A3 is highlighted. Similarly, it also highlights cells A8 and A9.
Conditional Formatting Based on Another Range of Cells
You can apply the above method to a range of cells instead of an individual cell. You just need to use the range as an argument, as shown in the following example.
Step 1: Select the cells you want to highlight.
Step 2: Click on 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: From the “Formatting rules” drop-down menu, select “Custom formula is”.
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 you want to apply. In this case, I’ll use the color red.
Step 8: Click “Done”.
As you can see in the image above, cells A3, A4, A8, and A9 are highlighted because all the data values in B2:B11 are less than 50.
Conditional Formatting if Another Cell Contains Text
If you’re working with text values for conditional formatting based on another cell in Google Sheets instead of numbers, the formula will be slightly different. This is because you can’t use logical expressions like “less than” or “greater than” with text values.
Instead, you can directly reference the text or a part of the text using wildcard characters.
Here’s how to use conditional formatting in Google Sheets if another cell contains text:
Step 1: Select the cells you want to highlight (student grades in this example).
Step 2: Click on 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: From the “Formatting rules” drop-down menu, select “Custom formula is”.
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 you want to apply. In this case, I’ll use the color red.
Step 8: Click “Done”.
The above steps will instantly highlight the cells (in red color) that contain the grades of the student “Ruben Layman”. This way, you can use conditional formatting in Google Sheets based on another cell containing text.
Conditional Formatting Based on Another Non-Empty Cell
You can use the following formula after selecting “Custom formula” in the conditional formatting menu.
=NOT(ISBLANK([Cell#))
Simply replace Cell# with the reference of the cell or range.
Related Content: Alternating Row Colors in Google Sheets.
Highlighting Cells Using Conditional Formatting Based on Multiple Other Cell Values in Google Sheets
In the above example, I showed you how to highlight one cell based on the value of another cell.
You can also extend the same logic and highlight a cell based on the values of multiple other cells. Let’s see how to use conditional formatting in Google Sheets based on other cells.
For example, let’s say I have a dataset like the one below, and I want to highlight all student names who scored less than 35 in any one of the three subjects.
To make this work, I need to analyze the values from three different cells and highlight the student’s name if any of the cell values (score) is less than 35.
Here are the steps for conditional formatting in Google Sheets based on another cell:
Step 1: Select the cells you want to highlight (student names in this example)
Step 2: Click on the “Format” option
Step 3: Click on “Conditional formatting”
Step 4: Make sure “Single color” is selected (select if not)
Step 5: From the “Formatting rules” drop-down menu, select “Custom formula is”
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 you want to apply. In this case, I’ll use the color red.
Step 8: Click “Done”
The above steps will analyze each cell for the name of a student, and if any of the subject scores is less than 35, the cell will be highlighted in red as a form of conditional formatting based on another cell in Google Sheets.
You can also use the formula below instead of the formula used above:
=OR(ARRAYFORMULA(B2:D2<35))
So, there are two examples where I used a simple formula to apply conditional formatting based on another cell value (or multiple cell values) in Google Sheets.
One of the advantages of conditional formatting in Google Sheets is that it is dynamic and will automatically update and highlight cells with student names who scored less than 35 in any of the subjects.
You can also use the same steps above to apply multiple conditional formatting rules. For example, if you also want to highlight all cells where students scored more than 80 in all subjects, you can simply use another formula. Each cell will then be checked for both conditions and highlighted accordingly.
Frequently Asked Questions
Can I use an IF formula in conditional formatting for Google Sheets?
Yes, you can use an IF formula with the conditional formatting formula in Google Sheets based on another cell when applying conditional formatting rules:
- Under “Formatting rules,” select “Custom formula is” from the drop-down menu.
- Enter your IF formula in the box below.
- 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 alone without doing anything to the format of a cell. It simply works with values unless you want 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 I conditionally format a cell based on the text in another cell? / How do I change the color of a cell in Google Sheets based on the 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 the text in another cell. You will use the following syntax:
=OR(Cell#,"text")
Simply replace Cell# with the cell reference or range, and text with the appropriate text you’re checking for. As in the following example:
How do I apply conditional formatting to a column based on another column?
Conditional formatting in Google Sheets based on another column can be set by clicking on the column header, then going to “Format” > “Conditional formatting.”
Then, select “Custom formula is” and write a formula to check a row. In the example below, we checked the entire N row for numbers greater than 5.
In Conclusion
I hope you found this tutorial on conditional formatting in Google Sheets based on another cell helpful! Just remember, you can choose your own custom formula when working with other cells, and you should be able to figure it out.
If you enjoyed this tutorial, you’ll love our premium templates for Google Sheets. You can use the code SSP to save 50% on all templates.
Other Google Sheets tutorials that might interest you:
- How to copy conditional formatting in Google Sheets
- How to display negative numbers in red in Google Sheets
- How to zoom in and out in Google Sheets (shortcuts)
- How to apply a formula to an entire column in Google Sheets
- How to highlight the highest or lowest value in Google Sheets
