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.
- Type “=ROW([@]) -Row(“
- then click the table header
- Excel enters the cell reference.
- Close the brackets and click Enter
- job done.
— Update 2020-04-18 —
See my more recent post for an alternative method that uses Power Query Editor.
Dr James Bayley 
9 responses to “How to add a row number to an Excel table”
Excellent! I have been looking for this kind of solution for some time.
Now, I have a couple of questions:
1) How to sort the table by column without recalculating row numbers – is this possible at all?
2) How to refer to table cell from outside, using the table row number and column name rather than the sheet cell coordinates?
Many thanks in advance.
Abbas
1) Yes. Create an new column RowFixed and copy and paste value into it. Use it for sorting
2) I think either OFFSET or INDEX is your friend. You will need to have a go
Good luck
Dr James
Million thanks for this!! –
Have been losing my sleep over how to have Data Table specific Row Numbers!
=ROW()-ROW(Table1[#Headers])
works inside the table.
Thanks for this inspiration.
I was hoping not to need Table1 but it seems to insist on that despite being inside the table; wonder if I missed something. Also pondering how to make it immutable for sorting, but as you say, paste value for now.
These are both good points.
“I was hoping not to need Table1” – unfortunately there does not seem to be a way to refer to the current table (https://support.office.com/en-gb/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e).
“immutable for sorting” – this seems to be an intrinsic problem with this method.
Excel has improved greatly since 2013 and I would now recommend using the building in ETL (Extract, Transform & Load) functions that you can find on the Data tab. I will create a new post on how to do this.
My new post is here – https://blog.jamesbayley.com/2020/04/18/how-to-add-a-row-number-to-an-excel-table-2020-using-power-query-editor/
[…] in 2013 I wrote post on how to add a row number to an Excel table. This post is still popular and the method still works well. But it is now 2020 and I would do it […]
Huge help, thank you!