In this article:

How to Use Conditional Formatting Based On Another Cell

May 8, 2024

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:

  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.
Same table as above, but with the Format tab highlighted and Conditional Formatting in the dropdown menu highlighted underneath
  1. Select the single color tab.
Conditional format rules tab open with Single Color highlighted
  1. Hover over Format Rules and choose the “Custom formula if” option.
Drop down menu with Custom Formula is highlighted in red
  1. 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!
  2. In the formatting style option, you can choose any format that you want for the highlight.
  3. 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:

  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. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->