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, 2022 in Database by Edureka
• 13,690 points
1,737 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, 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,060 points
703 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
1,977 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,768 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,817 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,542 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
599 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,294 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,058 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