Serial numbers are usually required in the very first column of almost every excel sheet. They are the sequence of number normally started from one and incremented by one for each subsequent row, hence providing a unique number to each row.
If you’re a regular Excel user and well up in it you may already know at least one method of inserting serial numbers automatically instead of having to type number for each cell. If not, here are some methods for you to quickly insert serial numbers.
Use Fill Handle to Add Serial Numbers
To add serial numbers by simply using fill handle you can use the following easy steps.
Enter 1 in a cell wherefrom you want to start the serial number and 2 in the very next cell. That should be enough with manual inserting. Now select both the cells and drag down with fill handle which is a tiny dark box at the bottom-right cornet of the last selected cell. Grab and hold down this box using left mouse and while the left mouse is down you can drag down to build the range up to the last cell you want the serial numbers to grow.
Now release your mouse and all the empty cells under your selection will be filled with the serial numbers in the sequential order as desired. This is, by and large, the most used method, although quite tedious when generating large numbers.
Fill Series To Automatically Add Serial Numbers
This method is quite handy when you’re required to fill a fairly large range of cells with serial numbers. The above mentioned Fill Handle method does work but it will take plenty of time and manual effort to generate the desired range. Here’s the magic:
- Select the cell from where you want to start your serial numbers and insert “1” or the desired start up number in it.
- From Home tab locate Editing section and click Fill -> Series from here.
- From within the Series popup window, make following selection.
- Select Columns from Series In section.
- Enter 1 in the Step Value box
- Enter Stop Value = 15000 or whatever you want.
- Click OK.
Use ROW() Function to Generate Serial Numbers
Row() function in Excel formula returns the row number of the row of the cell in question. We can take advantage of this function as well to generate serial numbers.
Put your anchor on a cell, e.g. A1 and enter the =ROW() formula in it. Now while being on this cell use any copy/paste method and voila… whichever cell or range of cells you paste, the cells will show up the row number of the row they are in.
Remember that you’re not confined to enter a series of numbers incremented by one, you can also apply any formula, no matter how much it is complex to generate numbers in a typical sequence, for instance you may apply =ROW()*10 to insert numbers in the sequence 10,20,30…
Generate Numbers in Sequence by Adding 1 in the Number in Previous Cell
This is perhaps the most simple to “build” and extremely fast method.
- Enter 1 in the cell from where you want to start your serial numbers, for instance B1
- In next down cell (B2), enter formula =B1+1.
- Now just drag this formula to down, up to the serial numbers you want. You can also copy/paste the cell B2 to the range of target cells.
Again notice that you can not only generate a serial number list but you can also generate a list with specific numbers by using some other formula. For instance, you can use formula “=B1 * 2” for B2 and paste it downward to generate a number series where every next number is 2 times greater than the previous one.
Generate Serial Numbers Using Excel Macros in VBA Code
If you’re well familiar with how to create and edit Macros in Excel then you can do hell of things to automate your tasks and surprise your siblings. Writing and running Macros in Excel is a piece of cake provided that you have a good know how of programming in VBA. Let’s assume you have a basic knowledge of VBA programming at least.
In order to create and use Macros in Excel, the Developer tab should be visible. If it’s not, click here to know how to make it visible. You can record macros and then use them at your convenience but we’re going to create Macros directly by using VBA.
- Click Macros under Developer Tab. It will show up Macro dialog box.
- Under Macro name, provide the name of the Macro. Here you’ll type in “GenerateSerialNums” without quotes.
- Click Create button
- Enter the following code under Sub GenerateSerialNums()
Sub GenerateSerialNums()
Dim i As Integer
On Error GoTo ExitQuick
i = InputBox("Enter The Last Number in the Range", "Enter Serial Numbers")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
ExitQuick:
Exit Sub
End Sub
- When you’re done, close VBA code window.
- Now while being on the cell from where you want to start the sequence to generate, click Macros under Developer Tab. Select your macro and click Run.
- Enter the last number you want in the sequence, say 1000 and click Ok and Voila… as expected, you’ll see all of the cells including current one and below it are set up – up to the last number you just specified.
Generate Roman Numbers as Serial Numbers
Just use this formula: =ROMAN(ROW()) and copy/paste in the subsequent rows up to where you the numbers to show up, and behold the magic.
