Google Sheets: Conditional Formatting from Another Sheet


You can use the custom formula function in Google Sheets to apply conditional formatting based on a cell value from another sheet.

The following example shows how to use the custom formula function in practice.

Example: Conditional Formatting Based on Another Sheet

Suppose we have the following dataset in Sheet1 that shows the total points scored by various basketball teams:

And suppose we have the following dataset in Sheet2 that shows the total points allowed by the same list of teams:

Suppose we’d like to highlight each of the cells in the Team column of Sheet1 if the value in the Points Scored column is greater than the value in the Points Allowed column in Sheet2.

To do so, we can highlight the cells in the range A2:A11, then click the Format tab, then click Conditional formatting:

In the Conditional format rules panel that appears on the right side of the screen, click the Format cells if dropdown, then choose Custom formula is, then type in the following formula:

=B2>INDIRECT("Sheet2!B2")

Google Sheets conditional formatting based on another sheet

Note: It’s important that you include the equal sign (=) at the beginning of the formula, otherwise the conditional formatting won’t work.

Once you click Done, each of the cells in the Team column where the value in the Points Scored column is greater than the value in the Points Allowed column in Sheet2 will be highlighted with a green background:

The only Team values that have a green background are the ones where the value in the Points Scored column in Sheet1 is greater than the Points Allowed column in Sheet2.

Note that if the sheet name you’re referencing has spaces in the name, be sure to include single quotes around the sheet name.

For example, if your sheet is called Sheet 2 then you should use the following syntax when defining the formatting rule:

=B2>INDIRECT("'Sheet 2'!B2")

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: Conditional Formatting If Date is Before Today
Google Sheets: Conditional Formatting with Multiple Conditions
Google Sheets: Conditional Formatting if Another Cell Contains Text

4 Replies to “Google Sheets: Conditional Formatting from Another Sheet”

  1. I believe this is incorrect. The “Indirect” is only pulling from B2 for its data, it doesn’t change to the next cell down. so when you want to be comparing B3 to B3 on the sheet2, you are actually comparing B3 to B2 on sheet 2. (Spurs should be highlighted but its not)

  2. This is so helpful, thank you! I was wondering if you could help me with a similar formula.

    I’m trying to get a range on a sheet named “Instagram, 2023” to reflect a single cell’s value on a separate sheet, named “2023 Overview” using conditional formatting.
    – The single cell is EP39 on “2023 Overview.”
    – The data range I’d like to apply conditional formatting to is DU20:DU69 on “Instagram, 2023.”
    – I’d like for any value LESS THAN the number in EP39 to be addressed in the conditional formatting.
    – I also want to adjust this formula slightly for other ranges and values; I.e. sub less than with GREATER THAN or GREATER THAN OR EQUAL TO or LESS THAN OR EQUAL TO, if that makes any sense.

    This is the formula I’m working with. So far, it has automatically formatted the entire range, rather than just the values less than the number in EP39:
    =EP39<INDIRECT("2023 Overview!EP39")

    This is important, as the EO39's value might change overtime. A successful conditional formatting formula will allow the formatting to automatically reflect across this sheet and others, thus avoiding confusion!

    Any help would be greatly appreciated!

    Thank you,
    Katie

  3. I needed to apply this to a range of cells, highlighting when the value in the current sheet didn’t match the other sheet. Here is what I used.
    =A1!=OFFSET(INDIRECT(“OtherSheet!A1”),ROW()-1,COLUMN()-1)

  4. Hello! Is there a way to make this work while applying to other cells across the same sheet? For example – I want Cell D20 to compare to Cell D20 in another sheet and apply the same thing to cells D21 – D30 for example. Is there a way to do this without manually changing the conditional format formula?

Leave a Reply

Your email address will not be published. Required fields are marked *