Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Automatically Add Serial Numbers In Excel

Serial numbers are like salt. Their presence is not felt, but absence makes your data tasteless. Yes, they are important.

With a serial number, you will have a unique identity to each data entry. But the sad news is, adding them 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 Excel.

And today, in this post, I’d like to share with you 6 quick methods which will tell you how to insert serial numbers in Excel without wasting much time.

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.

So without further ado, here are the methods.

1. Use Fill Handle To Add Serial Numbers

Fill handle is one the easiest ways to get serial numbers. Here are the steps.

  1. Enter 1 in a cell and 2 in the next cell downward.
  2. Select both the cells and drag down with fill handle (a small dark box at right bottom of your selection) up to up to the cell where you want serial number.
  3. Once you get serial numbers up to you want, just release your mouse from the selection.
fill handle to add serial number in excel

Important Note

  1. This is not a dynamic method, you have to insert serial numbers again when you update your data.
  2. 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.

2. Fill Series To Automatically Add Serial Numbers

Now, let’s say you want to add numbering 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.

  1. ​Select the cell from where you want to start your serial numbers and insert “1” in it.
  2. Now, go to home tab -> editing -> fill -> series.
  3. In the series window, do the following.
    1. Series In = Column.
    2. Step Value = 1
    3. Stop Value = 10000 or whatever you want up to.
    4. Click OK.
how to automatically add serial numbers in excel with fill series

The best part of this method is it will automatically add serial numbers even if you want up to one million.

Important Note:

  1. This method is not dynamic. If you want to extend serial numbers you have to insert them again.

Check this out ==> Bullet Points in Excel

3. Use ROW Function To Drag Serial Numbers

Row Function 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 inserted it.

Try these simple steps.

  1. Go to cell A1 & enter the =ROW() formula in it.
  2. Now drag the formula to down, up to the serial number you want.
Use ROW Function To Add Serial Number In Excel

Important Note:

  1. This method is dynamic and you can increase your serial number just by drag down.
  2. 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.
  3. It has a benefit over another methods that it will work even you sort your data. You will always get serial numbers in ascending order.

4. Generate Serial Numbers By Adding One in the Previous Number

This is my favorite method and believe me, it's lighting fast.

Follow these steps.

  1. Enter 1 in the cell from where you want to start your serial numbers.
  2. In next down cell, enter formula =G1+1 (G1 is the starting cell here).
  3. Drag this formula to down, up to the serial numbers you want.
Add One Number To Above Serial Number To Add Serial Number In Excel

Important Note:

  1. ​This method will create incremental number by adding 1 to the previous number.
  2. This is a dynamic method to use in your data. You just have drag the formula when you want to add a serial number.

Check this out ==> Check Mark in Excel

5. Get Serial Numbers with COUNTA Function

Using COUNTA Function 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.
Use COUNTA Function To Add Serial Numbers In Excel

Important Notes:

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

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

Follow these simple steps:

  • Enter the following formula in the cell from where you want to start.
  • =ROMAN(ROW(),0)
  • Drag down the formula up to the serial number your want.
Use ROMAN Function To Add Serial Number In Excel

Check this out ==> Add Leading Zeros in Excel

7. Add Serial Numbers With a VBA Code

This macro code code can be useful if you want to add serial 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 Sub

Sample File

Download this sample file from here to follow along.

Conclusion

As I said serial numbers are one of the most important parts of your data and it makes it easy manage it a unique identity to each entry.

As I said serial numbers are one of the most important parts of your data and it makes it easy manage it a unique identity to each entry.

​I hope you found these methods useful.

​Now tell me one thing. Do you know any other method for this? And which one is your favorite method from above all? Share your views in the comment section, I would love to hear from you.

😃

And, please don’t forget to share this charting tip with your friends.

  • mma173

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

    • Puneet Gogia

      Wow, MMA,

      Thanks for sharing.

      • Torstein S Johnsen

        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

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

  • Derek

    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

      Thanks Derek

  • ratanak

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

    • Puneet Gogia

      Thanks for sharing.

  • Mohd Amir

    Its owesome Yaar

    • Puneet Gogia

      Thanks for your words. 🙂

  • Vikas Dagar

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

    • Puneet Gogia

      Multiply with three and drag down.