How to Automatically Add Serial Numbers in Excel


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.

About the Author

puneet one point one

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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

40 thoughts

Leave a Comment

Your email address will not be published.

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

    Reply
  2. 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.

    Reply
  3. 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..

    Reply
  4. 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..

    Reply
  5. 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?

    Reply
  6. 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

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

    Reply
  8. Dynamic Serial Number method good find.
    Also Learnt usage of Row and CountA function in Serial number from this. Thank you 🙂

    Reply
  9. Thanks a lot for your explanations! it really helped me..I personally like method 3 and 5.

    Reply
  10. 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

    Reply
  11. 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.

    Reply
  12. 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

    Reply
    • 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)”

      Reply