Google Sheets: conditional formatting across a range, based on a formula involving related cells

SheetsCondForm10

“…related cells” was the challenge.

This will do as a mock requirement:

  • As a user checking their payslips
  • I want formatting in a spreadsheet to alert me to a change in an element of my pay or deductions from month to month
  • So that I don’t have to focus on the numbers to do that

More details…

Show me if the current month’s element differs from the previous month: I don’t care if it goes up or down, just that it changes.

This is our spreadsheet before any formatting is applied. Note that the changes (basic pay, tax code, tax) are kind of small, so not that obvious.

SheetsCondForm01

First, we’ll apply the principle to cell B4 relative to cell B3, as we can see there is a change. (Assumption: the basics of conditional formatting in a spreadsheet are not new to you). Right click the B3:B4 range, and pick Conditional formatting:

SheetsCondForm02

This is the default dialog from that:

SheetsCondForm03

We don’t care about the alert colour, so we’ll leave as is. Now get to the highlighted point below which says “B4 does not equal B3”:

SheetsCondForm04

Trouble is… that is a literal reference, so when I copy it down, it will take “=B3<>B4” with it. (This is not the same as an absolute reference, i.e. $B$3 style).

We now have to introduce the technique which dynamically assesses the intended cell, and that means bringing in “indirect()” and “row()” (“col()” would be needed in a different context – this is fine for me).

Cutting to the chase, this is what we want in the custom formula:

=indirect("B"&ROW()) <> indirect("B"&ROW()-1)

The screen now appears thus, confirming that Yes, a difference has been detected:

SheetsCondForm05

We ain’t quite Done, as we want to expand the principle to all the columns and rows where we want to detect a change. For us, the columns are (say) B, C, and D.

If you change your range to look as shown here:

SheetsCondForm07SheetsCondForm08

… then this is how your grid now appears:

SheetsCondForm09

As you can see, there are some wrinkles. I’ll leave you to work those out. You know you want to 🙂

 

Advertisements