Excel formula to reference 'CELL TO THE LEFT'

Question

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.

...and in case someone's looking for it...to get the column to the right: `=INDIRECT("RC[1]",0)` — prograhammer, Sep 18 '14 at 21:08
This is a very great question. Unfortunately, I'm not sure if I'm understanding this correctly the the original poster wanted his cells to change without having to do conditional formatting and then pasting the format painter to each cell. Let's say we have months in the rows, and bananas, apples, and oranges in the columns. In the matrix are the prices. How would we be able to do something that would automatically COLOR A CELL RED if there was a price change from the previous month to the current month? If there also an automatic way to color the cells green if the price goes down and red if — Bryan, Jan 29 '15 at 18:33

score 112 · Accepted Answer · answered Apr 29 '09 at 13:46

112

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

answered Apr 29 '09 at 13:46

Jason Young

3,683
4
32
39

21

Using FIVE rarely-used need-to-look-up-the-usage functions instead a simple A1 or RC[-1] relative reference? That's like travelling from Brooklyn to the Bronx with a stop-over in Walla Walla WA! – John Machin Jul 09 '09 at 00:51
8

Why not this instead =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) – Prometheus Feb 02 '18 at 18:44
2

"Indirect" has so many problems, including (but not limited to) being (1) single-threaded, and (2) volatile (possibly cascade-forcing your entire workbook to recalculate with each change. Don't use it if performance matters. – John Joseph Aug 30 '18 at 21:05
In Conditional formatting I set the rule as `="OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=""xyz"""` but the cell is not formatted. What's wrong? – zipper Oct 28 '20 at 02:04

score 103 · Answer 2 · answered Oct 10 '11 at 14:07

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.

score 13 · Answer 3 · answered Feb 03 '09 at 14:27

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

score 7 · Answer 4 · answered Jul 08 '09 at 23:12

7

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]

answered Jul 08 '09 at 23:12

edoloughlin

5,821
4
32
61

score 7 · Answer 5 · edited May 02 '15 at 20:20

7

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!

edited May 02 '15 at 20:20

Yurets

3,999
17
54
74

answered May 02 '15 at 19:11

Sam Fed

159
1
4

6

This is not useful. If you know already that your cell is B2, you could just write B1 for the cell to the left and not use offsets at all. The point is to do something completely generic. – Matthew Read Mar 13 '17 at 02:17
1

It is quite convenient if you know from which cell you start, and only the offset that you want to do is dynamic say =OFFSET(B2,0,A2) for example. Which was the context in which I used it. – Sam Fed Jul 30 '18 at 16:00
This is the answer I was looking for! I wanted someone to be able to select a single cell that represents a whole row, and have everything automatically change to use the parameters in that row. Thanks! – B T May 31 '19 at 03:39

score 3 · Answer 6 · edited Apr 12 '17 at 15:30

3

Why not just use:

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

edited Apr 12 '17 at 15:30

Robert

5,278
43
65
115

answered Apr 12 '17 at 15:13

Andrew Algava

31
1

score 1 · Answer 7 · answered Aug 02 '12 at 14:42

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.

score 1 · Answer 8 · answered Mar 19 '14 at 16:07

1

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.

answered Mar 19 '14 at 16:07

Gerard ONeill

3,914
39
25

Never do this. INDIRECT breaks the dependency tree - it's a disaster. – Ollie2893 Jan 05 '17 at 12:01
I just discovered this function which I think is useful for conditional formatting depending on the first row of the column. Not sure if RC[] notation works for that – narrowtux May 14 '20 at 10:11

score 0 · Answer 9 · edited May 08 '22 at 05:18

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

Ollie2893 · Answer 10 · 2017-01-05T11:59:35.790

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.

A simpler version: `=INDEX(6:6,1,COLUMN()-1)+F5` – chris neilsen May 08 '22 at 05:16 — chris neilsen, May 08 '22 at 05:16

score 0 · Answer 11 · answered Feb 03 '09 at 14:37

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

score 0 · Answer 12 · edited Jun 20 '20 at 09:12

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.

Select Cell A2
Open Name Manager (Ctrl+F3)
Click New
Name it 'LeftCell' (or whatever you prefer)
For Scope:, select Workbook
In Refers to:, enter the formula:

=INDEX(!A1:!A2, 1)
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.

Your method is actually for ABOVE_CELL, not LEFT_CELL. To make a LEFT_CELL function the same procedure works with a few minor changes: 1. Select Cell B1 6. Enter formula: =INDEX(!A1:A1,,1) — Rares Oltean, Oct 09 '22 at 10:19

score 0 · Answer 13 · answered May 13 '19 at 14:37

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)

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

score -1 · Answer 14 · answered Jun 29 '19 at 10:30

-1

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.

answered Jun 29 '19 at 10:30

pnuts

58,317
11
87
139

Excel formula to reference 'CELL TO THE LEFT'

14 Answers14

Make a named formula "LeftCell"