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

6 Answers 6


If you want to use the structured reference format, try


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


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


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.

  • How do you build the index in a table that gets new data all the time? Manually? Or with a formula? The latter would be catch 22, right?
    – teylyn
    Jan 13, 2019 at 0:56
  • @teylyn I normally do it manually. This is perferred for me since when I sort data indexes do not change. I am sure you can get creative using formulas depending on your application e.g. use something like =ROW()-2
    – DasIstLars
    Jan 29, 2019 at 19:13
  • 2
    If you use a formula to create the index, then refer to that index in another formula, you may as well nest the formula for the index into the second formula.
    – teylyn
    Jan 29, 2019 at 23:14
  • 2
    You could use ROW() - ROW([#Headers]) - 1. Nov 21, 2019 at 18:24

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:


This will handle some errors occurring in the first row


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:


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:


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


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.


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:

  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jun 14, 2022 at 11:17

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


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .