Automatically Add Serial Numbers in Excel (New Methods)

This Tutorial is Written by Puneet Gogia (Microsoft Excel MVP) was last updated on 01-Feb-2026

Is there any benefit of using Serial Numbers in Data?

Yes, there are quite a few benefits of using serial numbers in your data. One of the biggest benefits is that serial numbers give a unique identity to each entry in the dataset.

They also allow you to filter entries easily based on the serial number. For example, if you want to filter the first 50 entries, you can simply apply a filter and select the first 50 or even the last 50 entries. This makes filtering and analyzing data much more convenient.

In addition to that, serial numbers instantly give you a clear count of your data. If you have, say, 500 entries, you can immediately identify that there are 500 records just by looking at the serial numbers.

One of my favorite benefits of using serial numbers is that they allow you to flip your data instantly. Whether you’re using normal serial numbers or date serial numbers, serials make sorting extremely easy.

For example, if your data is sorted from 1 to 500, you can instantly reverse the order and sort it from 500 to 1. This flexibility is one of the biggest advantages when working with data and analyzing it, and it’s a benefit that I personally find extremely useful.

Simple Formula to Add Serial Numbers in Excel

If you ask me the easiest way to add serial numbers in Excel, I will say it’s by using a formula and then dragging that formula down. This is the simplest method to add serial numbers.

All you need to do is select the first cell from where you want to start your serial numbers and enter 1 in that cell. When you press Enter, the active cell moves down to the next cell. In this second cell, enter a simple formula that refers to the cell above where you entered 1. In my case, that cell is A2, and then add 1 to it using the addition operator. When you press Enter, you’ll see 2 in the second cell.

After that, use the fill handle to drag this formula down to the number of cells where you want serial numbers. For example, if you want 10 serial numbers, simply drag the formula down until the last cell shows 10.

s

That’s the easiest way to add serial numbers in Excel.

Another Way

Even if you don’t want to use a formula to get serial numbers, there’s another simple way to do it. You just need to enter 1 in a cell and then drag that value down to the cells where you want serial numbers.

When you do this, you won’t get serial numbers immediately, instead, you’ll see the value 1 repeated in all the selected cells. After dragging, you’ll notice a small drop-down at the end of the range called Auto Fill Options.

When you click on this drop-down, you’ll see a few additional options. From there, select Fill Series. The moment you choose this option, all the repeated 1s in the range are converted into a sequence of numbers, which become your serial numbers. We’ll cover the Fill Series option in more detail in a later method.

Using the Sequence Function to Add Serial Numbers Automatically (The Best Way)

With the new SEQUENCE function, you can easily add serial numbers in one go. For example, if you want to insert 1,000 serial numbers into your data, you just need to write a simple formula using the SEQUENCE function, and it will add all those serial numbers for you automatically.

=SEQUENCE(1000,1,1)

The idea of using the SEQUENCE function here is to generate a sequence of numbers, and that sequence becomes our column of serial numbers. When you enter the SEQUENCE function in the cell from where you want to start your serial numbers, the first argument is rows. In the rows argument, you need to specify the total number of serial numbers you want.

For example, if you want 1,000 serial numbers from 1 to 1,000, you need to enter 1,000 here. After that, the second argument is columns, where you can enter 1, or you can also skip this argument. The third argument is start, where you specify the first serial number. So, if you want to start your serial numbers from 1, you will enter 1 here. Once you enter 1 and close the function, it will start from 1 and generate 1,000 serial numbers automatically.

The best part of using the SEQUENCE function is that you have full control over how you want to add your serial numbers. For example, if you want to generate serial numbers with a gap of one number, such as 1, 3, 5, you can specify that value in the step argument.

=SEQUENCE(1000,1,1,2)

Let’s say you want to add a letter or some text along with the serial numbers. In that case, you can use simple text with a concatenate method along with the SEQUENCE function, and it will return that letter or any text you want together with the serial numbers.

In my case, I’m using the letter S and a hyphen before the serial number, so this formula works accordingly. The formula will look something like the one shown below.

=”S-“&SEQUENCE(1000,1,1,2)

If you want to connect your SEQUENCE function with a different column to test whether a value exists in that column, you can do that as well. For example, let’s say you want to add serial numbers in column A, and you have your actual data in column B. Now, you want to add serial numbers only when a value is present in column B.

Suppose you have three entries in column B and you want serial numbers from 1 to 3. The SEQUENCE function provides a dynamic method that not only inserts serial numbers but also checks the second column, which is column B, and then adds serial numbers based on the entries there. In my example, I’m using the SEQUENCE function along with the COUNTA function, where the reference range is column B from B2 to B1000.

Now, when you enter one more value, say, a fourth entry, in column B, the serial numbers automatically expand from 3 to 4.

=SEQUENCE(COUNTA(B2:B1000))

Use Fill Series to Generate Serial Numbers in Excel by Dragging

In Excel, there is another way to insert serial numbers instantly, and that is by using the Series option. If you go to the Home tab, you’ll find a drop-down called Fill, and within that Fill drop-down, there is the Series option. To use this option, you first need to enter the initial serial number in a cell. For example, if you want to start from 1, simply enter 1 in a cell.

After that, go to the Home tab again, open the Fill drop-down, and select the Series option.

This opens a small dialog box with a few options to choose from. From the Series in section, select the Columns option. In the Step value field, you can change the increment if needed, but here we’ll keep it as 1. In the Stop value field, enter the maximum serial number you want. For instance, if you want serial numbers from 1 to 1000, enter 1000.

Since 1 is already entered in the selected cell, the moment you click OK, Excel generates serial numbers from 1 to 1000 automatically. This is a simple way to use the Fill Series option to add serial numbers. The only thing you need to keep in mind is that this method is not dynamic. If you want to extend the serial numbers further, you’ll need to use the Series option again.

Using ROWS Function to Add Serial Numbers

If you are using a different version of Excel and you don’t have the SEQUENCE function available, there’s no need to worry. There is another way to write a formula that can generate serial numbers. This method uses the ROW function to add serial numbers.

=ROWS($A$2:A2)

While using this formula, you need to change the cell reference based on where you want to start your serial numbers. In my case, I’m starting the serial numbers from cell A2, which is why I’m using A2 as the reference. But, if you want to start with cell C2, A1, or any other cell, you simply need to change the reference to that specific cell. In the reference, both the first and the last cell will be the same. The only difference is that in the first cell of the reference, you need to freeze the reference using the dollar sign.

Using an Excel Table to Automatically Add Serial Numbers

If you convert your normal data into an Excel table, adding serial numbers automatically becomes much easier than any other method. If you look at my sample data here, I have two columns: Date and Amount. First, I’ll add one more column before the Date column and name it Serial Number, or simply Serial No, to define it as a serial number column.

Next, I’ll enter 1 in the first row as the serial number for the first entry. In the second cell, I’ll enter a simple formula that refers to the first serial number cell and adds 1 to it. This gives me serial numbers 1 and 2. After that, I’ll drag this formula down to the last row, which gives me serial numbers from 1 to 9.

Once this is done, I’ll convert the data into an Excel table using the keyboard shortcut Ctrl + T.

Now I have an Excel table with three columns: Serial Number, Date, and Amount. From this point on, whenever I add a new entry, let’s say I enter the next date, 10 Jan 2026, the moment I press Enter, Excel automatically adds the next serial number, which is 9, without needing to drag the formula down.

This works because Excel tables automatically extend formulas. It’s a one-time setup, and after that, the table will continue to add serial numbers automatically every time you add a new entry.

Creating Dynamic Serial Numbers with Filters

Now let’s talk about a different way of adding serial numbers. Suppose you’re working with data where you apply filters frequently, and you want a dynamic formula that always shows serial numbers starting from 1, even after filtering the data.

For example, I have region-wise data and you want the serial numbers to restart from 1 whenever you filter the data for a specific region. To do this, you first add one more column to your data and name it Serial Number. After that, you use a simple formula based on the SUBTOTAL function to generate the serial numbers dynamically.

=SUBTOTAL(3,B$2:B2)

In this formula, the range starts and ends with the same cell, but the first cell is fixed while the second cell is relative. As you drag the formula down, SUBTOTAL keeps the starting cell fixed and keeps extending the ending cell. This creates an expanding range, which gives you the running count of non-blank cells.

This method is not just for generating serial numbers, the real magic of SUBTOTAL happens when you filter your data. When you apply a filter to a particular region, instead of showing serial numbers for the entire dataset, SUBTOTAL recalculates everything based on the filtered results and returns serial numbers only for the visible rows.

For example, if you filter the data for the South region, the serial numbers automatically restart from 1 and continue up to the last visible entry, which in my case is 192. This makes the serial numbers fully dynamic and responsive to the applied filter.

This behavior is not limited to the Region column. It works with any column in your data. So, if you filter the data for a specific state, the serial numbers will again start from 1 and run up to the last entry for that state.

Macro to Add Serial Numbers in one Click

You can also use a VBA code to insert serial numbers in just one click. With the VBA code that I’ve written for you, you can add serial numbers not only as numbers, but also in Roman numerals and alphabets.

For example, if you want serial numbers as numbers, you can select that option. If you want them in Roman numerals or alphabets, you can choose the required option from the message box. All you need to do is add this VBA code as a macro in the Visual Basic Editor and then run it. Once you run the macro, it will insert the serial numbers automatically.

Steps: Developer Tab > Visual Basic > Insert > Module > Paste the Code into the Code Window.

Simple Code to Add Serial Numbers:

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

If you want to add every serial number twice:

Sub AddSerialNumbers2()

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

And if you want to have the option to choose the serial number type:

Option Explicit

Sub AddSerialNumbers_ByChoice()

    Dim rng As Range

    Dim choice As Variant

    Dim i As Long

    ‘Make sure user selected cells

    If TypeName(Selection) <> “Range” Then Exit Sub

    Set rng = Selection

    ‘Ask for type

    choice = Application.InputBox( _

        Prompt:=”Enter serial type:” & vbCrLf & _

                “1 = Numbers (1,2,3…)” & vbCrLf & _

                “2 = Roman (I,II,III…)” & vbCrLf & _

                “3 = Alphabets (A,B,C…)”, _

        Title:=”Serial Number Type”, _

        Type:=1)

    If choice = False Then Exit Sub ‘Cancel pressed

    Application.ScreenUpdating = False

    Select Case CLng(choice)

        Case 1 ‘Numbers

            For i = 1 To rng.Cells.Count

                rng.Cells(i).Value = i

            Next i

        Case 2 ‘Roman

            For i = 1 To rng.Cells.Count

                rng.Cells(i).Value = Application.WorksheetFunction.Roman(i, 0)

            Next i

        Case 3 ‘Alphabets (A..Z, AA..)

            For i = 1 To rng.Cells.Count

                rng.Cells(i).Value = ColumnLetters(i)

            Next i

        Case Else

            MsgBox “Invalid choice. Enter 1, 2, or 3.”, vbExclamation, “Serial Numbers”

    End Select

    Application.ScreenUpdating = True

End Sub

‘Converts 1->A, 2->B … 26->Z, 27->AA …

Private Function ColumnLetters(ByVal n As Long) As String

    Dim s As String

    s = “”

    Do While n > 0

        n = n – 1

        s = Chr$(65 + (n Mod 26)) & s

        n = n \ 26

    Loop

    ColumnLetters = s

End Function

Add Date Serial Numbers in a Column

Sometimes you don’t just need a list of serial numbers, you may also need date serials that start from a specific date and extend for a certain number of days. In such cases, you can use the same methods we discussed earlier; the only thing you need to change is the initial value.

The key thing to understand here is that Excel stores dates as numbers. For example, if you have a date like 1 January 2026, it is actually stored as a number in the background. When you add 1 to that date, Excel automatically returns the next date.

Let’s say you want to start with the date 1 January 2026 and add 30 serial dates, which means you want dates from 1 January to 31 January 2026. The idea here is quite simple.

First, move to the next cell below the starting date and enter a simple formula. Use an equals sign, refer to the cell where you have the first date, and then add 1 to it. This gives you the next date after 1 January 2026, which is 2 January 2026.

After that, use the fill handle to drag this formula downward to the cell where you want the last date. In this case, since you need dates up to 31 January 2026, you just drag the formula down until that date appears. This way, you can generate all the serial dates in one go.

Apart from this method, you can also use the Fill Series option to get serial dates instantly in your worksheet. The idea is the same—you need to have the first date entered in a cell. In my case, I have 1 January 2026, and I want 30 more dates.

To do this, go to the Home tab, click the Fill drop-down, and then select Series. In the dialog box, choose Columns under the Series in option. The Date type is already selected, and the Date unit is also set to Date. In the Stop value, instead of entering the count of serial numbers or serial dates, you need to enter the actual date. Here, you must use a specific date format, which is mm/dd/yyyy. So, I’ll enter 01/31/2026 as the stop value. When you click OK, Excel generates dates up to 31 January 2026 automatically.

Is there a keyboard Shortcut to Insert Serial Numbers?

By default, there is no built-in keyboard shortcut in Excel to insert serial numbers. But you can build a workaround that assigns a keyboard shortcut, so you can insert serial numbers quickly just by using that shortcut.

To do this, the first thing you need is a macro that can insert serial numbers for you, within a specific range, for example from 1 to 1000. You then need to add that code into the Visual Basic Editor. You’ve already learned this in the earlier section where I shared three different macros to insert serial numbers, so the setup is the same here as well. We just need a macro for this method too.

Option Explicit

Sub AddSerialNumbers_FromSelectedCell()

    Dim startCell As Range

    Dim n As Variant

    Dim i As Long

    ‘Start from selected cell

    If TypeName(Selection) <> “Range” Then Exit Sub

    Set startCell = ActiveCell

    ‘Ask how many serial numbers

    n = Application.InputBox(“How many serial numbers do you want to add?”, “Add Serial Numbers”, Type:=1)

    If n = False Then Exit Sub ‘Cancel

    If CLng(n) <= 0 Then Exit Sub

    ‘Fill serial numbers downward

    For i = 0 To CLng(n) – 1

        startCell.Offset(i, 0).Value = i + 1

    Next i

End Sub

You can use the macro I shared above. Just go to the Developer tab, click Visual Basic, insert a new Module, paste the code into the code window, and then close the Visual Basic Editor. Now, once you add this code to the Visual Basic Editor, the next step is to add this macro to the Quick Access Toolbar. When you add the macro there, you can run it using a simple keyboard shortcut with the Alt key. That’s the workaround we’re going to use here.

Now, once you add this code to the Visual Basic Editor, the next step is to add this macro to the Quick Access Toolbar. When you add the macro there, you can run it using a simple keyboard shortcut with the Alt key. That’s the workaround we’re going to use here.

From here, you need to go to the File tab and click on Options.

When you do that, the Excel Options dialog box opens. In this dialog box, go to Quick Access Toolbar. Here, you’ll see two sections: one that shows all the available commands, and another that shows the commands already added to the Quick Access Toolbar.

Now, from the Choose commands from drop-down, select Macros. This will display all the macros available in your workbook. At this point, I would also suggest learning more about the Personal Macro Workbook, which allows you to use VBA code across all workbooks instead of just one. In my case, I already have a Personal Macro Workbook, and the code is saved there, which is why I’m selecting the macro from it. Next, select the macro you want to use and click Add. The macro is now added to the Quick Access Toolbar. To make it easier to use with the Alt key, you should move this macro to the first, second, or third position. This position decides the shortcut key you’ll use.

After that, select the same macro on the right side and click Modify. Here, you can assign an icon that helps you identify this macro as one used for serial numbers.

You can choose any icon you like, here, I’m selecting a green icon. Click OK, and then click OK again to close the dialog box.

Now, if you look at the Quick Access Toolbar, you’ll see the green icon added there. When you press the Alt key on your keyboard, Excel displays numbers on the Quick Access Toolbar.

Since this macro is in the second position, pressing Alt + 2 runs the macro. When the macro runs, it asks how many serial numbers you want to add. If you enter 1000, it inserts 1000 serial numbers starting from the selected cell. This way, you can use a keyboard shortcut every time you want to insert serial numbers.

Once again, I recommend learning more about the Personal Macro Workbook, as it allows you to store useful VBA codes and use them across all your Excel workbooks.

Frequently Asked Questions