Experts know the importance of serial numbers. Serial numbers are like salt. You never feel their presence but their absence makes your data tasteless. Show
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. 14 Ways to Insert Serial Number Column in ExcelAnd 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. Choose one of the below methods as per your need and if you think you have a way, then please share it with me in the comment section. Method #1Row Numbers as Serial NumbersBy default, in a spreadsheet, you have row numbers which act as a serial number for the data. If you don’t want to use serial numbers for filtering or any other purpose then it’s better to use the row headers. Otherwise, follow ahead mention methods to create a separate column of serial numbers. Method #2Use Fill Handle to Add Serial NumbersTo simply add serial numbers using fill handle you can use the following steps.
Quick 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. Method #3 (Fastest)Fill Series To Automatically Add Serial NumbersNow 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.
The best part of this method is it works like a serial number generator and you can use it even if you want up to one million. Quick Note: This method is also not dynamic. If you want to extend the list, you need to insert serial numbers again. Method #4Use ROW Function to Drag Serial NumbersROW 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 entered it. Here's the simple steps.
Quick 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. Method #5 (My Favorite ?)Generate Serial Numbers by Adding One in the Previous NumberThis is my favorite method and believes me, it's lightning fast. Follow these steps
Quick Note: It will create an 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. Method #6Get Serial Numbers with COUNTA FunctionUsing COUNTA 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.
Quick Note: 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. Method #7Add Roman Numbers as Serial NumbersAnd, if you want to add roman numbers as serial numbers. You can use the roman function with ROW. Follow these simple steps:
Method #8 (VBA Code)Add Serial Numbers with a VBA CodeThis 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 Check this out: Top 100 Useful Excel Macro [VBA] Codes Examples If you want to add every serial number twice, 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 Method #9Dynamic Serial Numbers for FiltersLet’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. First, whenever 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) After that, drag this formula up to the last cell of the table. Now. whenever you filter data you’ll dynamic serial number. Quick 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. Method #10Serial Numbers in a TableThe 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. Method #11Incremental Serial Number by MultiplyingIn 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. Method #12 (For Pivot Table Lovers)Serial Numbers in a Pivot tableTo add serial numbers to a pivot table you need to add an extra column in the source data. And one more point which you need to understand that this number column will be placed after row item columns, just like below. For this, please follow the below steps:
Now you have a new column in your pivot table with staring from one to the maximum row. Quick Note: This is a dynamic column and when you filter pivot numbers will change automatically. Method #13Add a Serial Dates in a ColumnNow, 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. Quick 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. Method #14Convert Serial Numbers to Serial DatesIf you already have serial numbers in your worksheet and you want to convert them to serial dates then 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 have serial numbers starting from A1, then in cell B1, add below formula. After that, drag-down it up to the last serial number cell and you’ll get a sequence of dates. Quick Note: Once you convert these numbers into dates then you can use method 10 to insert serial dates further. ConclusionIf 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 in the data, no matter which situation you are in. I hope you found this useful, but now, tell me one thing. Which method do you prefer to insert serial numbers? Please share your views with me in the comment section. I'd love to hear from you and please don’t forget to share it with your friends, I am sure they will appreciate it. This tutorial is a part of our Basic Excel Skills, and if you want to sharpen your existing Excel Skills, checkout these Excel Tips and Tricks. But if you are just getting started with Excel, check out this tutorial on Excel Basics. More Tips
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain. |