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.

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.

So without further ado, here are the methods.

1. Row Numbers as Serial Numbers

By default, in a spreadsheet, you have row numbers which act as a serial number for the date.

If you don’t want to use serial numbers for filtering or any other purpose then it’s better use the row headers.

row number as serial numbers in excel

Otherwise, follow ahead mention ahead to create a separate column of serial numbers.

2. 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
Note: 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.

  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's works like a serial number generator and you can use it even if you want up to one million.

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 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
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 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
Note: It will create 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 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).
  • Drag down the formula up to the serial number your want.
Use COUNTA Function To Add Serial Numbers In Excel

Important: 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 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

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 Sub

If you want to add every serial number twice like Pramita, then you can use below code for 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

9. Dynamic Serial Numbers for Filters

Let’s 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.

dynamic use subtotal function serial number in excel

First, when ever 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)

dynamic serial number in excel with subtotal function

After that, drag this formula up to the last cell of the table. Now. when ever you filter data you’ll dynamic serial number.

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. That means you don’t have to change it.

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.

automatically add a serial number in a table

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.

serial number increment by multiplication

12. Serial Numbers in a Pivot table

To add serial numbers in a pivot table you need to add a calculated field and one more which you need to understand that this number column will be placed after row item columns.

Just like below.

a pivot table with serial number column

For this, please follow below steps:

  • First of all, add a new column in your source data with 1 in each cell.
  • 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.

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.

add serial dates in excel with adding one

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.

add serial dates in excel with fill handle

Just like serial numbers, you will get a sequence of dates in the column.

Note: 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, 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 you have serial numbers starting from A1, then in cell B1, add below formula.

=DATE(2017,1,A1)

convert to serial dates from serial number in excel

After that, drag-down it up to the last serial number cell and you’ll get a sequence of dates.

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 from your date, no matter which situation you are in.

I hope you found this useful.

Now tell me one thing. Do you know another method for this? Please share with me in the comment section, I’d love to hear from you.

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

Must Read Next

  1. Top 8-Methods to Insert Bullet Points in Excel for Lists
  2. How to Insert a Timestamp in Excel
  3. Insert a Check Mark Symbol in Excel
  4. Insert/Enter Degree Symbol in Excel
  5. Apply Strikethrough in Excel [Shortcut + Command]

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

  • Pramita Srimany

    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

      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

      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.

  • Shalini

    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.