Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in prices over months.)

I can do it per cell and format-drag it, but I would like a general formula to apply to the whole worksheet.

Thanks!

Solution 1:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Solution 2:

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.

Solution 3:

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(...).

Solution 4:

If you change your cell reference to use R1C1 notation (Tools|Options, General tab), then you can use a simple notation and paste it into any cell.

Now your formula is simply:

=RC[-1]

Solution 5:

Instead of writing the very long:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

You can simply write:

=OFFSET(*Name of your Cell*,0,-1)

Thus for example you can write into Cell B2:

=OFFSET(B2,0,-1)

to reference to cell B1

Still thanks Jason Young!! I would have never come up with this solution without your answer!