Row Numbers in Excel Table

Question

I have the following basic table:

1|
2|    Title....
3|
4|
5|    | Row Index | Type | Etc... |
6|    | 1         | abc  | .....  |
7|    | 2         | def  | .....  |
8|    | 3         | ghi  | .....  |
9|    | 4         | jkl  | .....  |

Note that the table does not start on Excel row 1. Technically ROW()-5 would work, but I do not want to hardcode the actual row the table starts on.

My formula for Row Index is:

=ROW()-CELL("row")+1

This works fine, except for when you edit another cell in the table. It seems that the formula assumes the row you edit is index 0 and starts the row count from there.

For instance, if I were to edit a cell in row 3 in the above table, the Row Index values would look like this:

| Row Index | Type | Etc... |
| -1        | abc  | .....  |
| 0         | def  | .....  |
| 1         | ghi  | .....  |
| 2         | jkl  | .....  |

After each edit, I think have to re-edit a cell in the top row to get the Row Index values correct again.

Is there a reliable way to display row numbers in a table?

Answer 1

If it is an actual Excel Table (Insert tab > Table or Home tab > Format as Table):

=ROW()-ROW([#Headers])

or

=ROW()-ROW(Table1)+1

Otherwise, you can use the absolute address:

=ROW()-ROW($5:$5)

Answer 2

Remove the CELL("row") and just use the formula

=ROW() - 5

The ROW function returns the row number of the cell containing the formula, which is what you want.

The CELL function, on the other hand, returns information about the last changed cell, which is why you see the strange behavior.

CELL(info_type, [reference])

• Reference   Optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. ...

Even if CELL returns information about the current cell, what you would get from ROW() - CELL("Row", <current_cell>) + 1 would be the constant 1 because the two functions cancel each other.