If items are deleted, the row numbers should respond accordingly.
The article below explains several options.
The best option for you will depend on what Excel version you use, and on your particular requirements.
Row numbers with the Fill Handle
The examples below use formulas to automatically generate row numbers.
The trick is to enter the first two numbers to help Excel understand what you want.
The SEQUENCE function generates a list of sequential numbers thatspilldirectly on the worksheet.
The challenge in this example is how to calculate the number of rows needed for the list or table.
One option is to subtract a different number from COUNTA.
This formula will continue to work as long as rows are not added or deletedabovethe first row of data.
In the worksheet shown, the header is in row 4.
See this articlefor a detailed explanation.
In other words, we are asking INDEX for the first (upper left) cell in the range.
The advantage of this formula is that it is portable.
It won’t break when the formula is moved, and it will work with any named range.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
COUNTA does not count empty cells.
