Tags

, , ,

Excel tables provide a lot of support for data processing and the use of structured references can improve readability.

We want to number rows in an Excel table and we can do it really easily using structured references.

=ROW(Table01[@])-ROW(Table01[[#Headers],[RowId]])

We simply find the row number of the current cell [@] using ROW and then subtract the row number of the column title in the table header. Excel auto-completes the formula as you type and click.

  1. Type “=ROW([@]) -Row(“
  2. then click the table header
  3. Excel enters the cell reference.
  4. Close the brackets and click Enter
  5. job done.

range2

— Update 2020-04-18 —

See my more recent post for an alternative method that uses Power Query Editor.