Excel formula to reference 'CELL TO THE LEFT'
The shortest most compatible version is:
=INDIRECT("RC[-1]",0)
"RC[-1]" means one column to the left. "R[1]C[-1]" is bottom-left.
The second parameter 0 means that the first parameter is interpreted using R1C1 notation.
The other options:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
Too long in my opinion. But useful if the relative value is dynamic/derived from another cell. e.g.:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)
The most simple option:
= RC[-1]
has the disadvantage that you need to turn on R1C1 notation using options, which is a no-go when other people have to use the excel.
When creating your conditional formatting, set the range to which it applies to what you want (the whole sheet), then enter a relative formula (remove the $ signs) as if you were only formatting the upper-left corner.
Excel will properly apply the formatting to the rest of the cells accordingly.
In this example, starting in B1, the left cell would be A1. Just use that--no advanced formula required.
If you're looking for something more advanced, you can play around with column(), row(), and indirect(...).
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)