Serial numbers are an important part of data. Even every small data table in excel should have serial numbers. But, adding them 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
Fill Handle Fill Series Row Function Adding Number CountA Function Roman Number 1. Use Fill Handle To Add Serial Numbers
Fill handle is one the easiest ways to generate serial numbers.
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. Important Note: This is not a dynamic method to insert serial numbers. 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. 2. 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.
The best part of this method is it will automatically add serial numbers even you want up to one million.
Important Note: This method is not dynamic. If you want to extend serial numbers you have to insert them again using the same method. 3. Use ROW Function To Add Serial Numbers In Excel
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. Important Note: This method is dynamic and you can increase your serial number just by drag down. 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 another method that it will work even you sort your data. You will always get serial numbers in ascending order. 4. 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. Important Note: This method will create incremental number by adding 1 to the previous number. This is a dynamic method to use in your data. You just have drag the formula when you want to add a serial number. 5. With COUNTA Function
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. 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. 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. Important Notes: This also a dynamic method to add serial numbers to your worksheet. 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. [Bonus Tip] VBA Code To Add Serial Number
VBA code can be useful if you want to add serial number 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
End Sub Sample File 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.