In this tutorial, you will learn how to add a relative reference in conditional formatting in Google Sheets.
Relative Reference in Conditional Formatting in Google Sheets
Quick Navigation
Conditional formatting is a useful feature in Google Sheets that can be used to highlight cells that meet certain criteria. You can use conditional formatting to format cells based on their values, or you can use it to format cells based on the values of other cells.
In some situations, you may be required to refer to external sheets to evaluate your criteria. Since conditional formatting does not allow relative references for external sheets, we’ll have to find a clever way to work around this limitation.
In this guide, we will show you how to use the INDIRECT and ROW functions to create a relative reference in conditional formatting
Working with Relative References in Conditional Formatting in Google Sheets
Here’s how to work with relative references in conditional formatting in Google Sheets
Step 1
First, select the cell range you want to add conditional formatting to.
In this example, we want to highlight rows in the range A1:A17.
Step 2
Select the Conditional formatting option under the Format menu.
Step 3
Next, look for the Format rules section in the Conditional format rules panel. Select the option “Custom formula is” in the provided drop-down list.
Step 4
Before writing the custom formula, identify the location of the relevant data you want to convert into a relative reference. In this example, we have a second sheet labeled Sheet2. The scores in this page are in the same row number and order as the entries in the first sheet.
We can use the following for our custom formula: =INDIRECT(“Sheet2!B”&ROW())>9. This formula uses the ROW function to get the current row and concatenates it to the string “Sheet2!B”. The newly-formed string will be used as an argument for the INDIRECT function. This function will use the string reference to retrieve the value and compare it to 9.
Since the ROW function updates the cell reference for each row in our range, we’ve effectively created a relative reference to another sheet.
You may now edit the formatting style as desired. Click on Done to proceed.
Step 5
Your range should now highlight cells that follow the specified criteria.
Our custom formula enabled us to use a relative reference to a table outside the current sheet.
Summary
This guide should be everything you need to use relative references in conditional formatting in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.