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

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

Solution 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.

Solution 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.

Solution 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