Last Updated on November 2, 2023 by Jake Sheridan
In this tutorial, you will learn how to use conditional formatting based on another sheet in Google Sheets
How To Use Conditional Formatting Based On Another Sheet in Google Sheets
Conditional formatting is a helpful feature in Google Sheets that allows you to apply formatting to cells based on certain conditions.
One way to use conditional formatting is to format cells based on the values in another sheet. This can be helpful if you have a large spreadsheet with multiple sheets, and you want to quickly see which cells meet certain criteria. We can use the INDIRECT function in our custom formula to return values from references outside the current sheet.
In this guide, we will show you how to use conditional formatting based on another sheet in Google Sheets.
How To Highlight Cells Based On Values on Another Google Sheet
Here’s how to highlight cells based on values on another Google Sheet
Select the range you want to add conditional formatting to.
Next, click on the Conditional formatting option found under the Format menu.
Select the Custom formula is option from the dropdown menu.
Identify the location of the value you want to base your formatting on. In this example, we want to highlight rows with a quantity less than the minimum specified in cell B1 on Sheet2.
We’ll use the formula =$C2<INDIRECT(“Sheet2!B1”) to compare each row’s quantity with the minimum quantity stated on another sheet. The INDIRECT function returns a reference specified by the text string argument.
After typing the formula into the provided textbox, you may now specify how the cell should be highlighted. Click on Done to apply the conditional formatting.
Your range should now have conditional formatting based on values stored on another sheet.
We can modify the values in the other sheet as needed.
The range with conditional formatting will automatically update to only highlight rows that follow the specified criteria.
This guide should be everything you need to use conditional formatting based on another sheet in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.
More ways to do conditional formatting Google Sheets:
- Remove Conditional Formatting
- How To Do Conditional Formatting
- Copy Conditional Formatting
- Conditional Formatting Based On Another Cell
- Conditional Formatting for Duplicates
- Conditional Formatting with a Checkbox
- Conditional Formatting with Multiple Conditions
- Apply Conditional Formatting Across An Entire Row
- Conditional Formatting to Compare Two Columns
- Conditional Data Validation
- Color Scale Based On Another Cell
- Relative Reference
- Conditional Formulas
- Conditional Formatting Based On Another Cell Not Empty
- Conditional Formatting Based On Another Cell Date
- If Cell Contains Text in
- Highlight Cells
- Highlight Duplicate Cells
- Make Negative Numbers Red