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.

Is this possible to do in Excel without manually typing the numbers in the SL column?
Mar 23 in Database by Edureka
• 13,640 points
124 views

1 answer to this question.

0 votes

These techniques can add a running column of numbers or create numbers up to a certain limit.
Serial Numbers for the Rows
Row numbers, which operate as a serial identifier for the data, are included by default in spreadsheets.

If you don't want to use serial numbers for filtering or anything else, row headers are a better option.

row number as serial numbers in excel

Other than these you can use this method as well 

Fill Series To Add Serial Numbers Automatically
Let's imagine you want to automatically number rows up to 10,000 or 100,000 in a single operation.

Fill handle is a hassle, but you can use fill series to get a column containing serial numbers all at once.

The steps are as follows.

Select the cell where you want your serial numbers to begin and type "1" into it.

Go to the editing fill series tab on the main tab now.
Perform the following in the series window:
Column = Series In
Stop Value = 10000 (or whatever you want up to) Step Value = 1
After that, press OK.

how to automatically add serial numbers in excel with fill series

answered Mar 24 by gaurav
• 13,560 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 in Database by Neha
• 8,560 points
67 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 in Database by Edureka
• 13,640 points
94 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 in Database by gaurav
• 13,560 points
77 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 in Database by Edureka
• 13,640 points
96 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 in Database by gaurav
• 13,560 points
94 views
0 votes
1 answer

Remove duplicates within a row

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

answered Mar 15 in Database by gaurav
• 13,560 points
43 views
0 votes
1 answer

Excel Remove Decimal Places

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

answered Mar 24 in Database by gaurav
• 13,560 points
96 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 in Database by gaurav
• 13,560 points
48 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP