Experts know the importance of serial numbers. Serial numbers are like salt. You never feel their presence but their absence makes your data tasteless.
Yes, they are important.
Because with a serial number you can have a unique identity to each entry of your data.
But the sad news is, adding them manually is a pain. It’s really hard to add a number in every row one after another.
The good news is, there are some ways which we can use to automatically add serial numbers in a column.
14 Ways to Insert Serial Number Column in Excel
And today, in this post, I’d like to share with you 14-Quick Methods. You can use any of these methods which you think is perfect for you.
These methods can generate numbers up to a specific number or can add a running column of numbers.
Choose one of the below methods as per your need and if you think you have a way, then please share it with me in the comment section.
1. Row Numbers as Serial Numbers
By default, in a spreadsheet, you have row numbers which act as a serial number for the data.
If you don’t want to use serial numbers for filtering or any other purpose then it’s better to use the row headers.
Otherwise, follow ahead mention methods to create a separate column of serial numbers.
2. Use Fill Handle to Add Serial Numbers
To simply add serial numbers using fill handle you can use the following steps.
- Enter 1 in a cell and 2 in the next cell downward.
- Select both the cells and drag down with fill handle (a small dark box at the right bottom of your selection) up to the cell where you want the last serial number.
- Once you get serial numbers up to you want, just release your mouse from the selection.
This is not a dynamic method, you have to insert serial numbers again when you update your data.
Inserting 1 and 2 in the cells is a must. If you try to drag by using only 1, it will start to repeat 1 in all cells.
3. Fill Series To Automatically Add Serial Numbers
Now let’s say you want to automatically number rows up to 10000 or 100000 and you want to do this in a one go.
Maybe it’s a pain with fill handle but, you can use fill series to generate a column with serial numbers in a one go. Here are the steps.
- Select the cell from where you want to start your serial numbers and insert “1” in it.
- Now, go to home tab ➜ editing ➜ fill ➜ series.
- In the series window, do the following.
- Series In = Column.
- Step Value = 1
- Stop Value = 10000 or whatever you want up to.
- Click OK.
The best part of this method is it works like a serial number generator and you can use it even if you want up to one million.
Quick Note: This method is also not dynamic. If you want to extend the list, you need to insert serial numbers again.
4. Use ROW Function to Drag Serial Numbers
ROW is an effective way to insert serial numbers in your worksheet.
It can return the row number of a reference and when you skip referring to any cell it will return the row number of cell in which you have entered it. Here’s the simple steps.
- Go to cell A1, edit it and enter the =ROW() formula in it.
- Now drag the formula to down, up to the serial number you want.
Quick Note: If you are not starting your serial numbers from row 1, you have to add the count of rows in the formula which you have skipped.
It has a benefit over other methods that it works even when you sort your data.
5. Generate Serial Numbers by Adding One in the Previous Number
This is my favorite method and believes me, it’s lightning fast.
Follow these steps
- Enter 1 in the cell from where you want to start your serial numbers.
- In next down cell, enter formula =G1+1 (G1 is the starting cell here).
- Drag this formula to down, up to the serial numbers you want.
Quick Note: It will create an incremental number by adding 1 to the previous number.
It is also a dynamic method, you just have to drag the formula when you want to add a new serial number.
6. Get Serial Numbers with COUNTA Function
Using COUNTA is a nice way to add serial numbers to your data. By using it you can actually count your data entries and give them a number.
Follow these simple steps.
- Enter the following formula in the cell from where you want to start your serial numbers. (For example, your first data entry is starting from F1, enter below formula in E1). “=COUNTA(F$1:F1”
- Drag down the formula up to the serial number your want.
Quick Note: This also a dynamic method to add serial numbers.
The benefit of this method is, it will return blank if you have any blank row in your data.
7. Add Roman Numbers as Serial Numbers
And, if you want to add roman numbers as serial numbers. You can use the roman function with ROW.
Follow these simple steps:
- Enter the following formula in the cell from where you want to start.
=ROMAN(ROW())
- Drag down the formula up to the serial number you want.
8. Add Serial Numbers with a VBA Code
This macro code can be useful if you want to create a series of numbers without using formulas.
Sub AddSerialNumbers()
Dim i As Integer
On Error GoTo Last
i = InputBox("Enter Value", "Enter Serial Numbers")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
Last:
Exit Sub
End SubCheck this out: Top 100 Useful Excel Macro [VBA] Codes Examples
If you want to add every serial number twice, then you can use below code for that.
Sub AddSerialNumbers2()
Dim i As Integer
On Error GoTo Last
i = InputBox("Enter Value", "Enter Serial Numbers")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
Last:
Exit Sub
End Sub9. Dynamic Serial Numbers for Filters
Let’s say you have serial numbers in a data table and when you filter some specific data from it show you actual serials.
But, if you use a dynamic method to insert serial numbers then you can get double benefits.
First, whenever you filter data it’ll show you serial number according to the filtered item.
Second, if you want to paste that filtered data to somewhere else, you don’t have to insert them again. For this, you can use SUBTOTAL function and the formula will be like below.
=SUBTOTAL(3,B$2:B2)
After that, drag this formula up to the last cell of the table. Now. whenever you filter data you’ll dynamic serial number.
Quick Note: If you copy data from a table where you have dynamic serial numbers and then paste those numbers to any other place. The formula which you have used for the numbers will work in the same way.
10. Serial Numbers in a Table
The best way to automate your serial numbers is by using a table for your data. When you add a new entry, table automatically drag-down formula into the new cell.
You just need to insert a formula in first two one or two entries, after that, you’ll get it automatically.
In the below table when you enter a new entry in a new serial number automatically generates.
11. Incremental Serial Number by Multiplying
In the comment section, Vikas asked me to add serial numbers which number should be increased by multiple of 3.
A simple solution is to enter 1 in the first cell and then multiply it with 3 and drag the formula below.
12. Serial Numbers in a Pivot table
To add serial numbers to a pivot table you need to add an extra column in the source data.
And one more point which you need to understand that this number column will be placed after row item columns, just like below.
For this, please follow the below steps:
- First of all, add a new column in your source data with 1 in each cell.
- Next, create a pivot table with that data.
- After that, add that column as values.
- Now, right click on that column on values section and open “Value Field Settings”.
- From here go to “Show Values As” tab and select running total in and then click OK.
Now you have a new column in your pivot table with staring from one to the maximum row.
Quick Note: This is a dynamic column and when you filter pivot numbers will change automatically.
13. Add a Serial Dates in a Column
Now, let me show you something different. Imagine you want to enter serial wise dates in a column, you have two different methods for this.
First is, you can add starting date in a cell and then in the next cell downward refer to the above cell and add “1” into it.
As you know Excel stores dates as numbers and every date has a unique number. So, when you add “1” in a date it will return the next date in the result.
The second method is quite simple. Just enter the starting date into a cell and then use the fill handle to extend it to the desired date.
Just like serial numbers, you will get a sequence of dates in the column.
If you want to insert dates only for one time then the second method is perfect, but if you going to expand them frequently then go with the first one.
14. Convert Serial Numbers to Serial Dates
If you already have serial numbers in your worksheet and you want to convert them to serial dates then you can simply do that with the following formula.
Please note, with this method, you are using the serial number as a day and then you create a date.
Let’s say you have serial numbers starting from A1, then in cell B1, add below formula.
=DATE(2017,1,A1)
After that, drag-down it up to the last serial number cell and you’ll get a sequence of dates.
Quick Note: Once you convert these numbers into dates then you can use method 10 to insert serial dates further.
Conclusion
If you have data whether small or large it is must to add serial numbers to it. The one thing which you really need to understand that a serial number give a unique identity to each entry.
And, with all the methods you have learned above it’s no big deal to create a serial number column in the data, no matter which situation you are in.
I hope you found this useful, but now, tell me one thing.
Which method do you prefer to insert serial numbers?
Please share your views with me in the comment section. I’d love to hear from you and please don’t forget to share it with your friends, I am sure they will appreciate it.
More Tutorials
- Convert Negative Number into Positive in Excel
- Insert (Type) Degree Symbol in Excel
- Convert a Formula to Value in Excel
- Insert a Timestamp in Excel
- Insert Bullet Points in Excel
- Insert an Arrow in a Cell in Excel
- Insert Delta Symbol in Excel in a Cell
This tutorial is a part of our Basic Excel Skills, and if you want to sharpen your existing Excel Skills, checkout these Excel Tips and Tricks.
There is no formula in Method 10
I prefer row function
Thank you so much! Your fave is so efficient and I used that.
Need a unique and absolute value for each new entry (column of data) as I create it – none of these seem to stick to the data when resorting (thus not a serial number) or I am not getting it.
What I want is a blank or null field, and as I enter data into a new column, the field autopopulates with a unique (preferable sequential) number
I feel like I must be missing something here… thanks
Method 8
Method #12 (For Pivot Table Lovers)
Hi,
To the above method it gives serial correctly when it is a non filtered Pivot, what if I sort my Pivot value field to get my Dr+ and Cr- and there are Zero Pivot results at Value Field. which I need to ignore with manual filter out !, ie. I will apply a filter to Pivot Value Area and Remove my Values with Zero results Manually, such as -0.00,0.00, which will give me absolute list or +/- values, however what I am seeking is to have the series (calculated Field) should apply to only visible cells only, This works when I use Aggregate function outside the Pivot ! seek to maintain the same result at Pivot? any thoughts. Please seek your assistance urgently. Thank you in advance.
hi thanks for the ways in which sr no can be added but, unfortunately cant find a way that helps me. so posting to see if something can be done…
so i need serial no in coloumn A of csv file and in Coloumn B have previous serial no (old ones) and as same data is repeated in 3-4 rows hence has the same serial no so is there a way that new serial no can detect same data in coloumn B and based upon that give serial no…
In Column A…
=IF($B2<=$B1,"",MAX($A$1:A2)+1)
If your Col B serial is repeated, Statement is true and "" is given.
If Col B advances in series, statement is false and Col A will find the max value of the Column thus far (the last number in series) and and 1 to it.
I have tried in MS Excel 2007, but it is not working
hi,
can you plese give the formula/code for method #10
I followed your method number 2. But to add to it, it showed 2 in all the rows with a drop down option at the end showing autofill options in which I then clicked on one of the options of fill series. That did the trick.
Need some excell solve
1.search on textbox then show data in listbox
According to textbox search value
And when clicked on listbox item data come to
Textboxes.value i already done it
2.now need data update from textboxes value
To sheet specific range based on textbox search
Value
Solve it.
3.at the time of adding data in sheet i want row
By row data insert from form textbox billno date
Partyname will have onetime but item qty rate
Will have 6 or 7 or 8 item. When item insert row by
Row bill date partyname should insert automatically
Row by row solve it.
4.at the time of update bring data on user form
Textbox by trxtbox search then update in sheet.
I all done it using module linking with sheet
I have done all by anotherway .now need using
Form based.
Need some excell solve
1.search on textbox then show data in listbox
According to textbox search value
And when clicked on listbox item data come to
Textboxes.value i already done it
2.now need data update from textboxes value
To sheet specific range based on textbox search
Value
Solve it.
3.at the time of adding data in sheet i want row
By row data insert from form textbox billno date
Partyname will have onetime but item qty rate
Will have 6 or 7 or 8 item. When item insert row by
Row bill date partyname should insert automatically
Row by row solve it.
4.at the time of update bring data on user form
Textbox by trxtbox search then update in sheet.
Wanting to make list that will attach a new serial number to a persons name and building type(portable building bought) if I type it in. Also if I need to search for that person or serial number after it gets assigned will the Excel be able to go back and find it?
HOW TO PUT NEXT SERIAL NUMBER WHEN DATA REPETITION ENDS FILTERED TABLE COLUMN, FOR EXAMPLE BELOW
SR. DATA (FILTERED:SCHOOL+COLLEGE) [UNIVERSITY DE-SELECTED]
1 SCHOOL
1 SCHOOL
2 COLLEGE
2 COLLEGE
………..>
Hi Puneet,
I find the links to your 14 methods to be inactive..?
However, I hope you can help me;
I am looking for a way to auto add serial numbers in a column when a new row is inserted at the bottom of a table, but the serial numbers should filter with the records if I run a sort on another column..?
Thus, the serial number “marries” the record and move along with it when I run a sort.
So, when I run a sort on the serial number column afterwards, the table restores to its original form.
It works if the serial numbers are entered manually, thus fixed (not determined by formulas in the cells), but I need it to autofill when a new row is inserted at the bottom of the table..?
Thank you!
Will appreciate so much!!
this can be done with VBA and Excel Table
Dear Puneet,
I just came accross your site, Indeed its informative, however I am looking for one of the solution/ the way to insert serial no at column based to Random input on multiple criteria at row/column cells
Exampls, I am trying to maintain a Voucher Series with Alpha numeric as Sr.No. based on the date sequence, my row “Date” input for particular transaction might change any any given point, suppose row2,3,4/Catagory X has Date as 1-Jan-2020, than my sequence for voucher Sr will be 1,2,3, but if I change row4 Date input to 1-Dec-2020 by keeping the same catagory as X than my Voucher should have Sr. as 1 as my date is older then row 2,3 based to catagory. if catagory input criteria changed Z then it will be 1 only, same way if I change row 3 catagory as Z than keeping same date (1-Jan-20),than Voucher no should be 2
Hopefully you understand on what is desired.
Please to know me on my email
Voucher Sr, Catagory, Date,
1 X 1-Jan-2020
2 X 1-Jan-2020
3 (2) X (Z) 1-Jan-2020
4 (1) Z 1-Dec-2020
Thank you in advance
Best Regards.
I want to create serial number automatically, but they should then remain as absolute values and should not change, if i add or sort data in table. How to do that
I’m using spreadsheet to work on students results and their age simultaneously with my phone, coz I don’t have desktop computer. But the problem I’m encountering right now is the arrangement of serial numbers and date of birth. Like for instance, from numbers 1 to 73 is not in the same line with numbers 74 to 112. Numbers 1 to 73 is at the normal column, while numbers 74 to 112 is at the side of the column. Lastly, students which their date of birth is from 10 – 31. Like for example; 10 – 02 – 2007 will be at the normal column. But students which their date of birth are below 10 will be at the side of the column. For example; 09 – 02 – 2007. Please how can I arrange it in line properly. Thanks in anticipation.
It would be great if you share a snapshot with me -> puneet(at)excelchamps(dot)com
Sir Good morning..
Sir im not from vba background but I need to know we can make a booking calander via vba code in userform where I can show stay length in room with guest name..
Please help..
I’m searching this question’s answer from a long days back..
I want month wise serial no.in every click from dropdown list. either from sheet 1 or from sheet 2 sequence of sr.no.not to change.please explain..
THANK YOU VERY MUCH, I WOULD HAVE RENUMBER 1K CONTENTS.
THANK U SO MUCH
Totally not what I was looking for. I have a single excel file. I have a field at the top “work order number”. Each time I open the file and do a “save as”, I want it to increment. How do I do that?
how to insert 4 columns in between sl.no. 1 to 800 for example sl.no.1 4 empty columns, sl.no.2 4 empty columns, sl.no.3 4 empty columns, this through out the 800 sl.nos
i want to put serial no as below data in automatic (colon A having s/n, B having group data but i need sn in group data wise
1 HS Code
24031100
24031100
24031100
2 HS Code
22 02 99 90
3 HS Code
24031920
24031920
24031920
4 HS Code
24022010
5 HS Code
24021000
24021000
24021000
Dynamic Serial Number method good find.
Also Learnt usage of Row and CountA function in Serial number from this. Thank you 🙂
Thanks a lot for your explanations! it really helped me..I personally like method 3 and 5.
What formula will be use for this type of numbring
S.N. QUALITY
1 2/60 ERI
1 10 S SLUB COTT
1 25S ACRLYIC
2 2/60 ERI
2 2/20 COT
2 10 COTTON
2 2/40 COTTON
3 2/60 ERI
3 2/20 COT
3 10 COTTON
3 2/40 COTTON
4 2/20 COTTON
4 2/60 ERI
4 25 S ACRLYIC
did you get an answer in regards to this?
I am not clear with your Method #10. can you explain clearly?
I have a serial numbers as A,B,C,D,…Z. If I delete one of the row, the series of A,B,C,D,E…Z should not be changed. it should again shows A,B,C,D,E,…..Z. please help…its urgent.
I WANT TO ADD SR NUMBERS AS 1(18-19) AND THAN 2(18-19) IN A COLUMN BUT BECAUSE OF () I AM NOT ABLE TO ADD SERIAL NUMBERS PLS HELP
You need to use ROW function and then add (18-19) as a test so if I have to insert serial number in cell A1 then the formula would be =ROW()&”(18-19)”
How to add Serial no. having figure more than 15 digits, like 89080068770002819
Give me a better example. what do you mean by 15 digits? 🙂
what the formula for method 10
awesome work !
excel
I often use MAX to achieve automated numbering, with cell formatted as a number ending in decimal point (“#,##0.”), with a dependency on the cell to the right having a value in it.
For example
With Row 1 having column headings, I would have the following formula in call A2 –
=if( $B2 = “” , “” , max( $A$1:$A1) + 1
which can then be dragged / copied down to the bottom of the sheet or area you need it to apply to. Useful to apply step by step documentation of steps in a process, where blank lines between any steps do not get numbered.
I need to insert automatic serial no.s like :
TK001
TK002
TK003 ….and so on.
with condition that when there is some entry in the next coloum even then this serial no. has auto entered.
DID YOU GOT IT ? I AM SEARCHING FOR THE SAME
i want to get a series like 1,1,2,2,3,3,4,4,5,5,.. and so on. How to do that?
Sub AddSerialNumbers()
Dim i As Integer
On Error GoTo Last
i = InputBox(“Enter Value”, “Enter Serial Numbers”)
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
Last:
Exit Sub
End Sub
Let’s say your sequence begins in cell A2.
In cell A2, enter a 1
In cell A3, enter this formula:
=IF(COUNTIF($A$2:A2,A2)=1,A2,A2+1)
Use the fill handle to drag that down as far as needed.
Please help
To get serial chace
How to do that please help
hii
i want to make auto fill column like 3 9 27 81
Multiply with three and drag down.
Its owesome Yaar
Thanks for your words. 🙂
I often use COUNTA(A$1:A1) to add serial number.for filering i’d love SUBTOTAL(3,B$1:B1)
Thanks for sharing.
The other day I learned the following:
– in Cell A1 type the number 1
– while holding down CTRL button, use Fill Handle on bottom right hand corner of cell A1 and drag down
– you will see the numbers filled in sequential order as you drag down
Thanks Derek
Well,
this is my favorite as it works with filtering:
=subtotal(3,a$1:a1)
Wow, MMA,
Thanks for sharing.
Do I miss something here? I have put the subtotal function in A1 and copied it down. What I get is 0 (zero) in all my cells. I use the norwegian version and the 3 points to COUNTA, is that correct?
formula should be =SUBTOTAL(3,B$1:B1)