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")

**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

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)

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

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)

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?