You can use Conditional Formatting in Google Sheets to format a cell based on its value.

For example, suppose you have a data set of students scores in a test (as shown below). You can use Conditional Formatting to highlight cells with the score less than 35 in red and with more than 80 in green.

But what if you want to highlight a cell based on other cells value.

For example, highlight the name of students who got less than 35 in Math.

You can highlight cells using conditional formatting based on another cell value in Google Sheets.

Suppose you have the data set as shown below:

Here are the steps to highlight cells with names based on the scores:

- Select the cells that have the names (A2:A11).
- Go to the Format Tab.
- Click on Conditional Formatting.
- In the Conditional Formatting rules pane, select Single Color.
- From the ‘Format Cells if’ drop down, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =B2<35
- Specify the format by clicking on the Formatting Style drop down.
- Click on Done.

You will see that the cells that contain the names are highlighted based on the scores in the adjacent cells.

**How does this work?**

Conditional formula highlights the cell when a given condition evaluates to TRUE; else it does nothing.

In the above example, while we selected the cells that contain names (A2:A11), the formula used the values from the adjacent column.

For example, for cell A2, conditional formatting evaluated the formula B2<35. Since the value in B2 is 12 and the formula evaluates to TRUE, it got highlighted.

Similarly, for cell B3, the value in cell A3 is evaluated and so on.

In the above example, you saw how to use highlight cells using conditional formatting based on another cell value in Google Sheet.

However, what if you want to evaluate multiple cells and then highlight cells based on the result.

For example, suppose I have a data set as shown below:

Now suppose you are the teacher and you want to highlight the students who have failed in one or more than one subjects. At the same time, you also want to highlight the names of the students who have scored more than 80 in all the three subjects.

Here are the steps to do this:

- Select the cells that have the names (A2:A11).
- Go to the Format Tab.
- Click on Conditional Formatting.
- In the Conditional Formatting rules pane, select Single Color.
- From the ‘Format Cells if’ drop down, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =OR(B2<35,C2<35,D2<35)
- Specify the format by clicking on the Formatting Style drop down. Since we are highlighting students who failed, I chose the red color.
- Click on Done.
- In the Conditional Formatting Pane, click on the ‘Add new rule’ option.
- In the Conditional Formatting rules pane, from the ‘Format Cells if’ drop down, select ‘Custom Formula is’.
- In the Formula field, enter the formula: =AND(B2>80,C2>80,D2>80)
- Specify the format by clicking on the Formatting Style drop down.
- Click on Done.

This would instantly highlight the names as shown below:

**You May Also Like the Following Google Sheets Tutorials:**

- Creating a Heat Map in Google Sheets (Step-by-Step Tutorial).
- How to Color Alternate Rows in Google Sheets.
- How to Zoom In and Zoom Out in Google Sheets.
- How to Insert an Image in a Cell in Google Sheets.

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X

How to Use OR Function in Google Sheets (with Examples)

How to Use IMPORTRANGE Function in Google Sheets (with Examples)

How to Use IMPORTDATA function in Google Sheets

Calculate the Number of Days Between Two Dates in Google Sheets

Using IFS Function in Google Sheets to Test Multiple Conditions