EXCEL How to automatically add serial number in Excel Table using formula that is immune to filtering sorting

Question

I want to create an Excel Table where the first column is the "SL" (serial number) column that starts from 1 and then increases by 1 for each subsequent entry. I want the serial number to automatically increase as I add more rows to the table.

I have tried using all manners of "=ROWS" functions, all manners of "=COUNTA" functions, and all other functions used in tutorial that I found in the web. None of them are immune from sorting or filtering. That is, if I sort the "Name" column from A to Z, the serial number that was assigned to its respective row entry changes because of how these formulae are written. For example:

This is the Original List. As you can see, Dragon Fruit's serial number is 1. I have used the "=COUNTA(B$2:[@[NAME]])" function in this example.

As you can see, when I sorted the "Name" column from A to Z, Dragon Fruit's serial number went from 1 to 2, Acai went from 4 to 1, Guava went from 9 to 3, and so on. But I want the serial numbers to be static and locked to their corresponding "Name".

Is this possible to do in Excel without manually typing the numbers in the SL column?

Answer 1

That's a great question, and it's a difficult one to handle. However, I'm not sure if this is a question for SuperUser.

The key is to use absolute rather than relative cell references. Excel filters do not perform well with relative references, as you have already discovered. We do not, however, want to manually add absolute references.

To simulate absolute cell-reference behaviour, precede the row reference with a sheet-reference, which should magically counteract the typical formula-behavior and convert them into genuine absolute cell-references:

In column A2, use the following formula to auto-fill the 'SL' column:

=ROW(Sheet1!A1)