If you have created a table in Excel (Insert- Tables-Table or CTRL+ T) you may want to number the rows in it instead of relying on the rows numbers in Excel on the left of the screen, which may not always correspond to where your table is on your worksheet.
This allows the table to be placed anywhere on the worksheet and your rows numbers will always be correct even if the table is filtered, sorted, rows are added or deleted.
It is really easy to do, there is a simple formula which you can enter into the first cell of the first column of table where you want your row numbers, then drag it down to the bottom of the table to autofill the formula. The formula look like this:-
This formula uses the SUBTOTAL function, with an argument of 3 (COUNTA), the formula returns a count of all the cells in Column D, starting with the header row and ending with the row that is one cell above the cell with the formula. You can see that the formula references column D but is in column C- this avoids a circular reference.
You can see in the figures below the row numbers continue to be correct even if rows are added.
Also if rows are deleted.
Give this a go and turbo charge your tables!
Other Excel Tips You Might Like