How to Automatically Add Serial Numbers in Excel

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.

5 Ways to Insert Serial Number Column in Excel

 And today, in this post, I’d like to share with you 5-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.

 

Otherwise, follow ahead mention methods 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. 

 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.

 

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

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.

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.

#5. Use ROW Function to Drag Serial Numbers.

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

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.

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.