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?

CodePudding user response：

Good question and a tricky situation to deal with. I'm not sure if the question is better suited for SuperUser though.

The trick here is to somehow use absolute cell-references instead of relative ones. As you have now experienced, Excel filters won't work well with relative references. However, manually adding absolute references is not what we want to do.

To mimic the absolute cell-reference behaviour you can preceed the row reference with a sheet-reference which magically should counter the normal formula-behaviour and turn them into actual absolute cell-references:

Formula in A2 (which will auto-fill the 'SL' column):

=ROW(Sheet1!A1)

Data when filtered A-Z on 'Name':

Data when tabbed a new row:

CodePudding user response：

You could use PowerQuery in Excel to add an index in front.

1. Remove the ID from your source.
2. Make your source a table
3. Import into PowerQuery and add an index
4. Load the output to another sheet. In this sheet you can filter and sort and everything you want.