Excel: Non-Volatile Relative references

One question that pops up is how to always refer to the cell above.

There are ways like the following but they all have disadvantages:

1. Hardcoding
   If you hardcode the text then any changes in the cell above are not reflected in the cell below.

2. Relative References
   =A1 if you are in cell A2
   If you insert a row or move or delete A1, the relative reference will change to match. This will mean that you will need to manually update a cell reference if you add a row. This is not always obvious and will result in errors unless you re-create the column each time.

3. Indirect
   =INDIRECT(ADDRESS(ROW()-1,COLUMN())) or =INDIRECT("R[-1]C[0]",0)
   Using Indirect is usually not a good idea because it is a slow volatile function. For further information about volatile functions see https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance

4. Offset
   =OFFSET(A2;-1;0) if you are in cell A2
   Using Offset may be OK because it is generally fast and it is probably the easiest to understand. But it is still a volatile function so there are some performance limitations still. For further information about volatile functions see https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance

Index and Row as a relative reference

There is a way to do it without Volatile functions. The trick is to use Index and Row to count the numbers:

=INDEX(A:A,ROW(A2)-1)

where A2 is the cell itself and A is the column the cell is in. Because it refers to itself, the formula will still work if you copy it down or add or delete rows.

Here is another method using Excel Table "Structured References", using Index and Rows:

=INDEX([Heading5],ROWS(Table1[[#Headers],[Heading5]]:[@Heading5])-2)

where Heading5 is the name of the heading, and Table1 is the table name. You need to subtract 2 because we need to subtract the extra heading row, and we also want to subtract 1 for the row above the cell.

Here is another method using Excel Table "Structured References", using Index and Row which may be a little easier to understand but is a little longer:

=INDEX([Heading6],ROW([@Heading6])-ROW(Table1[[#Headers],[Heading6]])-1)

where Heading6 is the name of the heading, and Table1 is the table name.

See also https://chandoo.org/wp/relative-references-in-excel-tables/ for a similar example.