Excel formula to reference 'CELL TO THE LEFT'

Miki_58807 0
Viewed 346024

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.

excel-2007 excel-formula excel
14 Answers
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
SuperJason_238370

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

Michael_Haren_290

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]
user_598010

You could use a VBA script that changes the conditional formatting of a selection (you might have to adjust the condition & formatting accordingly):

For Each i In Selection
i.FormatConditions.Delete
i.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & i.Offset(0, -1).Address
With i.FormatConditions(1).Font
    .Bold = True
End With
Next i
sdfx_34450

Make a named formula "LeftCell"

For those looking for a non-volatile answer, you can accomplish this by using the INDEX function in a named formula.

  1. Select Cell A2

  2. Open Name Manager (Ctrl+F3)

  3. Click New

  4. Name it 'LeftCell' (or whatever you prefer)

  5. For Scope:, select Workbook

  6. In Refers to:, enter the formula:

    =INDEX(!A1:!A2, 1)

  7. Click OK and close Name Manager

This tells Excel to always look at the value immediately to the left of the current cell, and will change dynamically as different cells are selected. If the name is used alone it provides the cell's value, but in a range it uses the reference. Credit to this answer about cell references for the idea.

user_42692700

I think this is the easiest answer.

Use a "Name" to reference the offset.

Say you want to sum a column (Column A) all the way to, but not including, the cell holding the summation (say Cell A100); do this:

(I assume you are using A1 referencing when creating the Name; R1C1 can subsequently be switched to)

  1. Click anywhere in the sheet not on the top row - say Cell D9
  2. Define a Named Range called, say "OneCellAbove", but overwrite the 'RefersTo' box with "=D8" (no quotes)
  3. Now, in Cell A100 you can use the formula
    =SUM(A1:OneCellAbove)
user_114934190

Please select the entire sheet and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:: 

=A1<>XFD1

Format..., select choice of formatting, OK, OK.

user_15051200
Related