How to Add Automatic Serial Number with Formula in Excel

We will use a dataset that contains information about the Marks of some students. We will add automatic serial numbers to the dataset using some formulas.

Method 1 – Apply a Simple Formula to Add Automatic Serial Numbers in Excel

STEPS:

Type 1 in Cell B5.

Select Cell B6 and insert the formula below:

=B5+1

We are adding 1 to the previous cell value to get the serial numbers automatically. That’s why we need the first entry to be done manually.

Press Enter and drag the Fill Handle down.

You will see serial numbers like the below picture.

Method 2 – Adding Automatic Serial Numbers with the ROW Function

STEPS:

Select Cell B5 and use the formula below:

=ROW()-ROW($B$4)

In this formula, we have used the ROW function. If you type =ROW() in Cell B5, the output will be 5 because it indicates the 5th row. The output of ROW($B$4) will always be 4 because we have used absolute cell reference here. That is why we have subtracted ROW($B$4) from ROW().

Hit Enter and drag down the Fill Handle.

You will see the automatic serial numbers in the dataset.

Method 3 – Using the Name Manager to Add Automatic Serial Numbers in Excel

STEPS:

Go to the Formulas tab and select Define Name. It will open the New Name dialog box.

In the New Name dialog box, add a name. We have named the range as Serial.
Use the formula below in the Refers to box:

=INDIRECT(“R[-1]C”,FALSE)

Here, we have used the INDIRECT function. The INDIRECT function returns the cell reference of a given text string. The first output of this formula is 0. It indicates the previous cell.

Select Cell B5 and type the formula below:

=SUM(Serial,1)

In this formula, we have used the SUM function. It sums up the value of the Serial function and 1. In the first case, the output is 1. Now, if you apply the formula in Cell B8, then, the formula will act like =SUM($B$7,1). That means it will sum up the value of Cell B7 with 1 and show it in Cell B8.

Hit Enter and drag the Fill Handle down.

You will see automatic serial numbers like the below picture.

Method 4 – Using the COUNTA Function to Generate Automatic Serial Numbers

STEPS:

Use the formula below in Cell B5:

=COUNTA($C$5:C5)

In this formula, we have used the COUNTA function to count the number of cells in the range $C$5:C5. This count will give us the serial numbers automatically. In Cell B6, the formula will change into =COUNTA($C$5:C6), which sees two values.

Press Enter and drag the Fill Handle down.

You will be able to see the automatic serial numbers.

Method 5 – Creating Automatic Serial Numbers in Excel with the SUBTOTAL Function

STEPS:

Select Cell B5 and use the formula below:

=SUBTOTAL(3,$C$5:C5)

In this formula, we have used the SUBTOTAL function. The first argument indicates the function number. And function number 3 refers to the COUNTA function. So, it will count the cells in the range $C$5:C5.

Press Enter and drag down the Fill Handle.

The dataset will look like the picture below.

Method 6 – Applying the SEQUENCE Function to Add Automatic Serial Numbers with Formula in Excel

The SEQUENCE function is available in Microsoft 365.

STEPS:

Select Cell B5 and use the following formula:

=SEQUENCE(COUNTA(C5:C14))

In this formula, the output of COUNTA(C5:C14) is 10. So, the formula becomes =SEQUENCE(10) after the operation of the COUNTA function. The SEQUENCE function generates a sequential list from 1 to 10.

Hit Enter to see the results like the picture below.

Method 7 – Combining IF and ROW Functions to Add Automatic Serial Numbers in Excel

We will add a name in Column C and the serial number will be visible automatically in Column B.

STEPS:

Select Cell B5 and use this formula:

=IF(C5="","",ROW()-ROW($B$4))

In this formula, we have used the ROW function inside the IF function. The formula will check if Cell C5 is empty or not. If Cell C5 is empty, the output of the formula will also be empty. Otherwise, it will show the value of ROW()-ROW($B$4). In the case of Cell B5, the value of ROW()-ROW($B$4) is 1.

Drag down the Fill Handle to copy the formula in the range B6:B10.

If you type a name in Cell C5, you will see the serial in Cell B5.

Type a name in Cell C6 and C7, respectively, and you will get the serial numbers automatically.

Insert all values to get the serial numbers.

Method 8 – Using the OFFSET Function to Create Automatic Serial Numbers

STEPS:

Insert an empty row into the dataset to avoid errors. Here, Row 5 is the added empty row.

Select Cell B6 and insert the following formula:

=OFFSET(B6,-1,0)+1

In this formula, the first argument of the OFFSET function is Cell B6. The second argument is -1 which indicates the previous row. And the third argument is 0 which refers to the same column. So, the output of OFFSET(B6,-1,0) is 0 since the Cell B5 is empty. The output of the formula is 1 for Cell B6.