How can you create a conditional format in Google Sheets based on the value from another cell or column regardless of whether that cell is on the same sheet or another?
To reference any cell or column in the
field in Google Sheets’ conditional formatting section use the
function referencing that cell or adjacent column.
Recently I had a simple requirement where I wanted to highlight a range of cells according to the
of a cell containing a date value.
For example, I had a cell on a
sheet which I gave it the Named Range value of
. The cells I wanted to highlight had the name of the month and I wanted to highlight these cells that matched the month of the
I knew my formula would initially look something like this, where
where I wanted the
Remember when setting a Custom Formula the first cell needs to be inserted into the formula. Google Sheets then automatically replaces this cell reference with the next cell in the range where Conditional Formatting will apply.
This is a very important point as you’ll soon see why.
Here’s what happened when I inserted my formula into the Custom formula is field in the Conditional formatting section, as you can see it didn’t work:
It doesn’t matter whether a Named Range value or the actual range reference (eg.
) is inserted into the formula the result is still the same:
How To Get Around
Error With Cell References
To get around the
error in the
field in the
section simply replace the cell reference (whether an actual reference or Named Range value) by wrapping it into the
function translates the value inserted into the parameters of the function into a value that is used by the remaining formula, so my initial function changed to this:
And this enabled the Custom Function to work.
Conditional Formatting And
The purpose of a
used in a conditional format is to structure the formula so that it is a boolean operation. While you can use an
statement as above it needs to return boolean values so Google Sheets knows when to apply the format.
Therefore, if the
function is already returning boolean values and
in the correct order
is the first value returned after the statement, and
is the last value returned) then the
statement is redundant and can be removed.
My custom formula would now look like this:
function has the boolean values swapped – i.e.
then you still can remove the
statement and instead wrap your
section with the function
For example, if I wanted to highlight cells that were not empty using a custom formula I could write the following:
This conditional format works on the range where it is applied (in
) and will highlight the cell if the cell is not empty.
simply reverses the boolean result inside. The original function inside states
is empty, therefore, should a cell be empty the
whereas if a cell is not empty, being
For Custom Formula?
The reason why the
function is needed in the
is because of the
by Google Sheets of cell references when it applies the logic to other ranges in the
With my formula above you can see that there is one cell reference inserted into the formula, in my case, the cell
Apply to range
field (as shown in the picture above) is set to
Therefore, when Google Sheets applies my
to the next cell in my
range it finds the only cell reference in the formula and replaces it with the next cell, therefore,
would look like this for my custom formula:
Hopefully, you can see why inserting more than one cell reference into a custom formula does not work, and the only way to get a formula to work where other cells are referenced is to use the
Conditional Format Based On Another Column
What if there were data in an adjacent column as you tracked down the column you wanted to perform the conditional formatting? Can the Custom Formula use adjacent or same length columns to perform a comparison?
Suppose you have the following set of data in your spreadsheet which lists the total amount of widgets sold in a month per sales staff member:
If you wanted to highlight the name of the staff member who had sold more than 200,000 you could use a Custom Formula on column A. Upon selecting all the cells you want the Conditional Formatting to apply insert the following formula:
This formula uses the handy
formula and starts by using the column name
where the information is stored to compare and then joins it to the corresponding cell used by Google Sheets in the range for the conditional formatting to be applied. As the range is
Google Sheets will replace this cell with each cell in the range.
Here’s how this looks:
You could further change the formula by creating another cell and setting the Named Range of that cell to
could reference an additional cell:
The important thing to remember when inserting a Custom Formula into the conditional formatting section is that there can only be one cell reference and this must be within the range to whom the formatting is applied.
Google Sheets Relative Referencing With Conditional Formatting
formula can also take relative references. Instead of using
you can also use the distance in rows or columns a range is from the active cell. This technique is known as
To use relative referencing in the
formula simply insert the relative reference using the distance in rows and columns away from the active cell.
For example, if the active cell were
and you were to use the
formula with the relative reference of
in the active cell, this relative reference would
the row by 1, and
the column by 1. Therefore, this relative reference would copy the contents of cell
To use the
as the active cell don’t insert any numbers after the
tag in the relative reference.
Therefore, continuing with the example above, instead of using
in the custom conditional formula, you could substitute this with the relative reference:
Notice with the first instance of the
formula in the above condition that the parameters used in this formula have changed to
and a second parameter
has been added to assert that the indirect formula reference is a relative reference that
does NOT use A1 notation
(by default this parameter is set to
Depending on your use case and which you prefer to use, relative referencing can just as easily be used and swapped out for
type referencing when using the
Conditional Format Custom Formulas: Common Errors
If you find your formula isn’t highlighting the right cells after you’ve entered your new Custom Formula there are a couple of things to check:
1. Is the formula correct?
Test your formula by inserting it somewhere on your sheet and seeing if it does work. In the case of the example above with the Sales staff I copied the formula into an adjacent column and checked to see if it would work, and it’s very easy to do, as shown below:
Copy the same formula used in the
into the top most cell and then copy this cell down checking that the cell reference (
) changes too.
2. Is the cell reference in the Custom Formula “Apply to range” range?
Check the Apply to range field and verify the cell contained within your Custom formula will actually be found in that range.
For example, if the formula contained references to
(which is an easy mistake to make when you’re thinking about other columns!) then this will not work as anticipated.
3. Is the cell reference in the Custom Formula starting in upper-left-most range?
Check your cell reference in the Custom Formula matches the first cell range in the Apply To Range field. You can get strange behaviours if you use other cell references, so check the cell reference is the first cell in the range.
4. The background colour doesn’t match the conditional formatting set?
If you have a cell in your conditional format range that has a background colour not matching the rules of the format then this could be due to the cell’s background being set manually .
Simply look for the Fill Color icon and see if the background colour has a colour matching what you are seeing. If so, remove the background colour so that it doesn’t conflict with any conditional format rules you have set for the same range.
How To Remove Conditional Formatting
To remove a conditional format click first on a cell where you know formatting is applied, or, if in doubt, select all cells in the active sheet. Then click on the Format menu item followed by the Conditional formatting sub-menu item.
This will bring up a sidebar of the conditional formats available for either the selected cell or all cells you have selected.
To remove the desired condition simply hover your mouse over the row containing the formatting rules and you will see a rubbish bin icon appear and a tooltip will pop up stating Remove rule . To remove the rule simply click this button and the rules for the format will be deleted.
If you can’t find a conditional format rule try selecting
in the active sheet (click that grey box which doesn’t have any letters or numbers in it – it’s found to the left of label
for column A, and above label
for row 1). Here’s what this looks like when you do:
To enable references in the
field in the
section of Google Sheets, insert either the reference or the Named Range into the
The reason why the
function is needed is that only one cell reference can be used in the formula for Google Sheets to replace and apply the formula to other cells in the conditional formatting range.