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.

#### Method #1

#### Method #2

#### Method #3 (Fastest)

#### Method #4

#### Method #5 (My Favorite ?)

#### Method #6

#### Method #7

#### Method #8 (VBA Code)

#### Method #9

#### Method #10

#### Method #11

#### Method #12 (For Pivot Table Lovers)

#### Method #13

#### Method #14

## 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.

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. But if you are just getting started with Excel, check out this tutorial on Excel Basics.

## More Tips

- Excel Quick Access Toolbar
- How to Insert a Timestamp in Excel
- Excel Formula Bar
- How to Apply Strikethrough in Excel
- How to Insert Bullet Points in Excel
- How to Convert Negative Number into Positive in Excel
- How to Insert a Check Mark Symbol in Excel
- How to Conbine a RANGE of Cells in Excel
- How to Convert a Formula to Value in Excel
- How to Insert/Type DEGREE Symbol in Excel
- How to Insert Delta Symbol in Excel
- How to Multiply in Excel using Paste Special
- Fill Justify in Excel
- How to use Format Painter in Excel
- How to Select Non-Contiguous Cells in Excel
- How to Highlight Blank Cells in Excel

About the Author

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

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.

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)