[excel] 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!

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

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

Even simpler:

=indirect(address(row(), column() - 1))

OFFSET returns a reference relative to the current reference, so if indirect returns the correct reference, you don't need it.

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

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

Why not just use:

=ADDRESS(ROW(),COLUMN()-1)

I stumbled upon this thread because I wanted to always reference the "cell to the left" but CRUCIALLY in a non-volatile way (no OFFSET, INDIRECT and similar disasters). Looking the web up and down, no answers. (This thread does not actually provide an answer either.) After some tinkering about I stumbled upon the most astonishing method, which I like to share with this community:

Suppose a starting value of 100 in E6. Suppose I enter a delta to this value in F5, say 5. We would then calculate the continuation value (105) in F6 = E6+F5. If you want to add another step, easy: just copy column F to column G and enter a new delta in G5.

This is what we do, periodically. Each column has a date and these dates MUST BE in chronological order (to help with MATCH etc). Every so often it happens that we forget to enter a step. Now suppose you want to insert a column between F and G (to catch up with your omission) and copy F into the new G (to repopulate the continuation formula). This is NOTHING SHORT of a total disaster. Try it - H6 will now say =F6+H5 and NOT (as we absolutely need it to) =G6+H5. (The new G6 will be correct.)

To make this work, we can obfuscate this banal calculation in the most astonishing manner F6=index($E6:F6;1;columns($E1:F1)-1)+F5. Copy right and you get G6=index($E6:G6;1;columns($E1:G1)-1)+G5.

This should never work, right? Circular reference, clearly! Try it out and be amazed. Excel seems to realize that although the INDEX range spans the cell we are recalculating, that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference.

So now I am home and dry. Insert a column between F and G and we get exactly what we need: The continuation value in the old H will refer back to the continuation value we inserted in the new G.

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.

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.

Why not just use:

=ADDRESS(ROW(),COLUMN()-1)

I stumbled upon this thread because I wanted to always reference the "cell to the left" but CRUCIALLY in a non-volatile way (no OFFSET, INDIRECT and similar disasters). Looking the web up and down, no answers. (This thread does not actually provide an answer either.) After some tinkering about I stumbled upon the most astonishing method, which I like to share with this community:

Suppose a starting value of 100 in E6. Suppose I enter a delta to this value in F5, say 5. We would then calculate the continuation value (105) in F6 = E6+F5. If you want to add another step, easy: just copy column F to column G and enter a new delta in G5.

This is what we do, periodically. Each column has a date and these dates MUST BE in chronological order (to help with MATCH etc). Every so often it happens that we forget to enter a step. Now suppose you want to insert a column between F and G (to catch up with your omission) and copy F into the new G (to repopulate the continuation formula). This is NOTHING SHORT of a total disaster. Try it - H6 will now say =F6+H5 and NOT (as we absolutely need it to) =G6+H5. (The new G6 will be correct.)

To make this work, we can obfuscate this banal calculation in the most astonishing manner F6=index($E6:F6;1;columns($E1:F1)-1)+F5. Copy right and you get G6=index($E6:G6;1;columns($E1:G1)-1)+G5.

This should never work, right? Circular reference, clearly! Try it out and be amazed. Excel seems to realize that although the INDEX range spans the cell we are recalculating, that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference.

So now I am home and dry. Insert a column between F and G and we get exactly what we need: The continuation value in the old H will refer back to the continuation value we inserted in the new G.

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!

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.

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

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.

When creating a User Defined Function, I found out that the other answers involving the functions OFFSET and INDIRECT cannot be applied.

Instead, you have to use Application.Caller to refer to the cell the User Defined Function (UDF) has been used in. In a second step, you convert the column's index to the corresponding column's name.
Finally, you are able to reference the left cell using the active worksheet's Range function.

Function my_user_defined_function(argument1, argument2)
    ' Way to convert a column number to its name copied from StackOverflow
    ' http://stackoverflow.com/a/10107264
    ' Answer by Siddarth Rout (http://stackoverflow.com/users/1140579/siddharth-rout)
    ' License (if applicable due to the small amount of code): CC BY-SA 3.0
    colName = Split(Cells(, (Application.Caller(1).Column - 1)).Address, "$")(1)

    rowNumber = Application.Caller(1).Row

    left_cell_value = ActiveSheet.Range(colName & rowNumber).Value

    ' Now do something with left_cell_value

As this was the only way to answer @zipper

The reason why your formula:

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

doesn't conditionally format, is because the formula litteraly asks ="THIS PHRASE" is equal to "THAT PHRASE", try writing it like this: =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="xyz" where "xyz" is your Text and "-1" is the number of columns to the left you want to search.

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

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

As this was the only way to answer @zipper

The reason why your formula:

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

doesn't conditionally format, is because the formula litteraly asks ="THIS PHRASE" is equal to "THAT PHRASE", try writing it like this: =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="xyz" where "xyz" is your Text and "-1" is the number of columns to the left you want to search.

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!

fill the A1 cell, with the following formula :

 =IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"

Then autoextend to right, you get

 1|  A  |  B  |  C  |  ect ect
 2|    1|   11|  111|  ect ect

If offset is outside the range of the available cell, you get the #REF! error.

Hope you enjoy.

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]

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)

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]

fill the A1 cell, with the following formula :

 =IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"

Then autoextend to right, you get

 1|  A  |  B  |  C  |  ect ect
 2|    1|   11|  111|  ect ect

If offset is outside the range of the available cell, you get the #REF! error.

Hope you enjoy.

When creating a User Defined Function, I found out that the other answers involving the functions OFFSET and INDIRECT cannot be applied.

Instead, you have to use Application.Caller to refer to the cell the User Defined Function (UDF) has been used in. In a second step, you convert the column's index to the corresponding column's name.
Finally, you are able to reference the left cell using the active worksheet's Range function.

Function my_user_defined_function(argument1, argument2)
    ' Way to convert a column number to its name copied from StackOverflow
    ' http://stackoverflow.com/a/10107264
    ' Answer by Siddarth Rout (http://stackoverflow.com/users/1140579/siddharth-rout)
    ' License (if applicable due to the small amount of code): CC BY-SA 3.0
    colName = Split(Cells(, (Application.Caller(1).Column - 1)).Address, "$")(1)

    rowNumber = Application.Caller(1).Row

    left_cell_value = ActiveSheet.Range(colName & rowNumber).Value

    ' Now do something with left_cell_value

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.

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

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)

Even simpler:

=indirect(address(row(), column() - 1))

OFFSET returns a reference relative to the current reference, so if indirect returns the correct reference, you don't need it.