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

0 votes

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:

Original List

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.

Sorted List

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?

Mar 23, 2022 in Database by Edureka
• 13,690 points
2,135 views

1 answer to this question.

0 votes

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:

enter image description here

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

=ROW(Sheet1!A1)

answered Mar 24, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,020 points
770 views
0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24, 2022 in Database by Edureka
• 13,690 points
2,064 views
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
4,920 views
0 votes
1 answer

How to transform multiple tables in one excel sheet to one table with Power BI?

If my prediction is correct- You have a ...READ MORE

answered Apr 4, 2022 in Database by Edureka
• 13,690 points
2,913 views
0 votes
1 answer
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
1,661 views
0 votes
1 answer

Remove duplicates within a row

To check for duplicates, choose the cells ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
664 views
0 votes
1 answer

Excel Remove Decimal Places

1) E2 is the cell from which ...READ MORE

answered Mar 24, 2022 in Database by gaurav
• 23,260 points
26,870 views
0 votes
1 answer
0 votes
1 answer

How to Calculate DATEDIF formula in Excel?

Simply divide one date by the other. ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
1,110 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP