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. 

‍

‍

Here are the steps to do it:

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

4. Select the single color tab.

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

6. 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!
7. In the formatting style option, you can choose any format that you want for the highlight.
8. Click on done and your sheet is ready to go.
9. ‍

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.

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

1. Select the cells that you want to highlight. In this case, they are the names of your customers.
2. Click the format option in the tab.
3. Click on the Conditional Formatting to open a new pane on the right.
4. Select the single color tab.
5. Hover over Format Rules and choose the “Custom formula if” option.
6. 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!
7. 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. 

‍

Use our renewal tracking software to easily track renewals from your spreadsheet in just a few clicks. 

‍

If you enjoyed this article, you might also like our article on how to use conditional formatting in Google Sheets or our article on how to copy conditional formatting in Google Sheets. 

‍

If you want a copy of our Google Sheets address book, we also suggest checking out our detailed guide.