Reference a cell of the previous row in the same table in Excel?

Question

How can I reference the cell of

• the Color column,
• in the previous row,
• of the same table,

from a cell of the same table? Without relying on the absolute row / column numbers of the worksheet if possible.

Like [[Color],-1] something...

Answer 1

If you want to use the structured reference format, try

=OFFSET([@Colour],-1,0)

You can also do this without using structured references. For example, in row 2 of the table (the first data row), enter

=D1

... if "Colour" is in column D. (Adjust to your situation). The latter will be much faster in large datasets, since Offset() is volatile and will recalculate whenever ANY cell in the workbook changes. That can greatly impact performance.

Answer 2

I almost always create a separate column in my table named "I" for index, which is numbered 1,2,3,4... Then to get the previous value use INDEX([Colour],[@I]-1).

You can also number it the column starting from 0 and adjust the formula accordingly, since you almost always access the row before.

The volatile OFFSET function causes my large workbooks to crash so I recommend to avoid at all costs.

Answer 3

I know this is an old post but just like I needed something similar now, i'm sure this may be of help for someone later. If what you need is to do a running sum on a table column, for example:

• Column 1 has the "income" data (positive and negative values)
• Column 2 is the "balance" of the account

For each row, you need to add the previous balance (Column 2, previous row) and the income (Column 1, current row) to calculate the current balance.

One way to do that is using this formula in the Column 2, based on teylyn's answer:

=SUM([@Column1];OFFSET([@[Column2]];-1;0))

This will handle some errors occurring in the first row

Answer 4

Want to follow up on some other answers and comments.

Some folks have suggested to use a formula such as suggested by @teylyn on 2013-06-29 10:47:

=OFFSET([@Colour],-1,0)

But as @DasistLars pointed out in an answer on 2018-11-09 07:35, this is a volatile function, so is not good to use if your workbook will have lots of data. @DasistLars suggested to create a column called "I" numbered 1,2,3,4,etc... and use the formula:

=INDEX([Colour],[@I]-1)

... which is a great suggestion, as INDEX is not volatile.

I will add to this that a way to get column "I" to autopopulate with the line number is to use this formula (example with the formula in cell A2, with that being the first non-header row in the table):

=IFERROR(A1+1,1)

This will add 1 to the value in the row above, which can't be done for the first row, so it enters 1 in that case. It's important to have the formula in the first row of the table, as new rows take the value/formula from the first one.

In the comment thread to @DasistLars answer, there are a couple of comments suggesting to use a formula with the function ROW in it, but that function is also volatile so doesn't really solve the problem.

Answer 5

I follow the suggestion of @DasistLars to prefer INDEX (and not OFFSET). To preserve consistency when sorting or filtering the table by any column, I apply the INDEX as follows:

INDEX([column1],MATCH([@I]-1,[I],0))

Answer 6

For auto-filling of an index in a table use:

=ROW()-ROW(TBL_Rates[[#Headers],[INDEX]])