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 🚀)

top excel tips tricks which can make you a pro this year

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

You must Read these Next

  1. Bullet Points in Excel: Unlike Word, in Excel, there is no default option to insert bullet points. But there are totals...
  2. Check Mark in Excel: Eventually today morning, I thought maybe there is more than one way to add a check mark...
  3. Strikethrough in Excel: When it comes to Excel, we don’t have any direct option to apply strikethrough to a cell...
  4. Degree Symbol in Excel: While working on that data we have found that in Excel you can enter/type a it using...
  5. Insert a Timestamp in Excel: In general, it contains the current date and time, and we use it to capture completing...
  6. Insert Square Root Symbol: Once you calculate a square root from a number, the next thing you can do is to...
  7. Delta Symbol in Excel: Take an example of Delta (Δ) symbol which we can use to present the difference between...
  8. Add Leading Zeros: Whenever you try to insert a zero before a number, Excel removes it and you’ll only get the number starting from...

Content Protection by DMCA.com
2018-11-27T07:01:07+00:00

18 Comments

  1. RISHABH JAIN 6 Dec, 18 at 10:15 am - Reply

    excel

  2. Lindsay 29 Nov, 18 at 2:21 am - Reply

    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.

  3. Shalini 14 Sep, 17 at 11:37 am - Reply

    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.

  4. Pramita Srimany 29 Aug, 17 at 10:37 am - Reply

    i want to get a series like 1,1,2,2,3,3,4,4,5,5,.. and so on. How to do that?

    • Puneet Gogia 31 Aug, 17 at 12:08 pm - Reply

      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

    • Plastic Cup 1 Sep, 17 at 11:32 pm - Reply

      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.

  5. Vikas Dagar 27 Jul, 17 at 3:57 am - Reply

    hii
    i want to make auto fill column like 3 9 27 81

    • Puneet Gogia 2 Aug, 17 at 6:11 pm - Reply

      Multiply with three and drag down.

  6. Mohd Amir 20 Jun, 17 at 12:12 pm - Reply

    Its owesome Yaar

    • Puneet Gogia 6 Jul, 17 at 2:31 am - Reply

      Thanks for your words. 🙂

  7. ratanak 5 Jan, 17 at 10:23 pm - Reply

    I often use COUNTA(A$1:A1) to add serial number.for filering i’d love SUBTOTAL(3,B$1:B1)

    • Puneet Gogia 6 Jan, 17 at 7:47 am - Reply

      Thanks for sharing.

  8. Derek 21 Apr, 16 at 7:05 pm - Reply

    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

    • Puneet Gogia 22 Apr, 16 at 4:48 am - Reply

      Thanks Derek

  9. mma173 20 Apr, 16 at 4:56 pm - Reply

    Well,
    this is my favorite as it works with filtering:
    =subtotal(3,a$1:a1)

    • Puneet Gogia 20 Apr, 16 at 5:14 pm - Reply

      Wow, MMA,

      Thanks for sharing.

      • Torstein S Johnsen 23 Apr, 16 at 12:32 pm - Reply

        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?

        • Puneet Gogia 23 Apr, 16 at 12:46 pm - Reply

          formula should be =SUBTOTAL(3,B$1:B1)

Leave A Comment