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

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 24 in Big Data Hadoop by Edureka
• 9,320 points

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Big Data Hadoop

0 votes
1 answer

How to add column inside a table in Hive?

Hi, Yes, we can add column inside a ...READ MORE

answered May 15, 2019 in Big Data Hadoop by Gitika
• 65,850 points
0 votes
1 answer

How to add a column in Hive table?

Hi@akhtar, You can use the alter table command ...READ MORE

answered Oct 19, 2020 in Big Data Hadoop by MD
• 95,340 points
0 votes
1 answer

How Impala is fast compared to Hive in terms of query response?

Impala provides faster response as it uses MPP(massively ...READ MORE

answered Mar 21, 2018 in Big Data Hadoop by nitinrawat895
• 11,380 points
+1 vote
2 answers

How to authenticate username & password while using Connector for Cloudera Hadoop in Tableau?

Hadoop server installed was kerberos enabled server. ...READ MORE

answered Aug 21, 2018 in Big Data Hadoop by Priyaj
• 58,060 points
0 votes
11 answers
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 in Database by Edureka
• 8,820 points
0 votes
1 answer

Remove duplicates within a row

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

answered Mar 15 in Database by Edureka
• 8,820 points
Send OTP
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP