Google Sheets conditional formatting based on another cell

Table of contentsChapter 1Chapter 2Chapter 3Conditional Formatting Based onAnother CellsConditional Formatting Based onMultiple Other Cells‍Conditional formatting is widely used in

Google Sheets conditional formatting based on another cell

Table of contents

Chapter 1

Chapter 2

Chapter 3

  1. Conditional Formatting Based onAnother Cells
  2. Conditional Formatting Based onMultiple Other Cells

‍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.

Sign up for more articles like thisThank you! Your submission has been received!Oops! Something went wrong while submitting the form.SHARESECTIONSMORE RESOURCESBlog

Tutorials

Templates

eCommerce Metrics

Marketing Metrics

Video liên quan