How to Use Conditional Formatting Based On Another Cell

‍Conditional formatting is widely used in Google Sheets. By using this function, you can analyze the data and format the cells based on a specific condition. Usually, you will use the current value in applying conditional formatting. However, you can actually use conditional formatting based on another cell within the sheet.

‍

For example, you have a list of names of your customers and the payment they made for a certain month. If you want to highlight the names of the customers who have paid more than $1000 in a month, you can use conditional formatting based on another cell.

‍

With this article, we will help you learn how to use conditional formatting based on another cell on Google Sheets.

‍

Apply Conditional Formatting Based On Another Cell

To make it easier to explain, let us use the example of the monthly payments. If you have a long list of names and their corresponding payments on your sheet, you may want to highlight the names of those who have made payments larger than $1000.

‍

Google Sheets table on a spreadsheet with Customer Name and May Payment as headers

‍

Here are the steps to do it:

Select the cells that you want to highlight. In this case, they are the names of your customers.
Click the format option in the tab.
Click on the Conditional Formatting to open a new pane on the right.

Same table as above, but with the Format tab highlighted and Conditional Formatting in the dropdown menu highlighted underneath

Select the single color tab.

Conditional format rules tab open with Single Color highlighted

Hover over Format Rules and choose the “Custom formula if” option.

Drop down menu with Custom Formula is highlighted in red

In the field “Value or Formula”, enter the formula =B2>1000. This is only for example, but you can use any conditions that you want!
In the formatting style option, you can choose any format that you want for the highlight.
Click on done and your sheet is ready to go.
‍

Same table as above, now with Customer Names that have over $1000 in payments highlighted in red — The conditional formatting here automatically highlighted the names that have payments over $1000.

Apply Conditional Formatting Based on Multiple Other Cells

There will be times that you want to use conditional formatting based on multiple other cells. In our case, suppose we now have multiple months of sales data, and we want to see if a customer has spent over $1000 in at least one of the months.

Same table as above but now with columns of payments from June and July added on

To help you, here is a step-to-step process:

Select the cells that you want to highlight. In this case, they are the names of your customers.
Click the format option in the tab.
Click on the Conditional Formatting to open a new pane on the right.
Select the single color tab.
Hover over Format Rules and choose the “Custom formula if” option.
In the field “Value or Formula”, type this formula =OR(B2>1000,C2>1000,D2>1000). You can change this based on what condition you wanted!
Click Done and the cells will automatically be highlighted based on the criteria that you set.

Need more help with Google Sheets?

We want to help you be a Spreadsheet power user in no time! If you want to learn more about Google Sheets, make sure to check out our other Spreadsheet resources.

‍