how to automatically add serial numbers in excelSerial numbers are an important part of data.

Even every small data table in excel should have serial numbers.

But, adding serial numbers manually is a pain.

So, today I’ll show you 6 quick methods to automatically add serial numbers in excel.

You can use any of these methods which you think is perfect for you.

Table of Content

  1. Fill Handle
  2. Fill Series
  3. Row Function
  4. Adding Number
  5. CountA Function
  6. Roman Number
Instant Access: Download this quick PDF guide to learn about adding serial numbers in excel.

Use Fill Handle To Add Serial Numbers

Fill handle is one the easiest ways to generate serial numbers. You just have to drag down your mouse.

Here are the steps.

  • Enter 1 in a cell and 2 in the next cell downward.
  • 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
  • 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 to insert serial numbers. 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.

Fill Series To Automatically Add Serial Numbers

Now, let’s say you want to add serial numbers up to 10000 or 100000. And, you want to do this in a one shot.

Maybe it’s a pain with fill handle. But, you can use fill series to automatically add serial numbers in a one shot.

Here are the steps.

  • Select the cell from where you want to start your serial numbers and insert “1” in it.
  • Now, go to home tab -> editing -> fill -> series.
  • In the series window, do the following.
    • Series In = Column
    • Step Value = 1
    • Stop Value = 10000 or whatever you want up to.
    • 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 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 using the same method.

Use ROW Function To Add Serial Numbers In Excel

Using row function is an effective way to insert serial numbers in your worksheet. It can return the row number of a reference. And, If you skip referring any cell it will just return row number.

Let’s try these simple steps.

  • Go to cell A1 & enter the following formula in it.

=ROW()

  • Now drag the formula to down, up to the serial number you want.

Use ROW FunctionTo 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 method that it will work even you sort your data. You will always get serial numbers in ascending order.

Add One Number To Above Serial Number

This is my favorite method to create serial numbers. And, believe me, this lighting fast.

Follow these steps.

  • Enter 1 in the cell from where you want to start your serial numbers.
  • In next down cell, enter this formula. (G1 is the starting cell here.)

=G1+1

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

With COUNTA Function

Using COUNTA Function is a nice way to add serial numbers in to your data.

By using it you can actually count your data entries.

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 Number In Excel

Important Notes:

  1. This also a dynamic method to add serial numbers.
  2. The benefit of this method is, it will return blank if you have any blank row in your data.

Use ROMAN Function to Add Serial Numbers

And, If you want to add the roman numbers as a serial number. 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 automatically add serial number in excel

Important Notes:

  1. This also a dynamic method to add serial numbers to your worksheet.
  2. As you are using ROW Function in this method it will work even you sort your data. You will always get serial numbers in ascending order.

Sample File

download sample file to learn about how to automatically add serial numbers in excel

Conclusion

If you ask me, I always add one in number from above cell to add a serial number in excel. I always found it easy and dynamic.

But other ways are also cool to use.

I hope you have your favorite one to add a serial number in excel. So please share with me in the comment box.

More Tips



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