# Serial Number in Excel (With Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn about creating the serial number in Excel. We will show you how to create a serial number in Excel using built-in features and formulas. We will also demonstrate how to insert a serial number in Excel with some other features.

You will also learn VBA code to create serial numbers as well. Lastly, you will see some other practical use cases of serial numbers in Excel.

Serial numbers are important for sorting, ordering, and maintaining the consistency of data in Excel. It plays a vital role in tracking and analyzing data as well. Further, you will see some practical examples of serial numbers in Excel in this guide.

## Add Serial Number in Excel with Built-in Features

Here in the dataset, we have the Names and CGPAs of multiple students, but the Serial Number column is blank. We will generate serial numbers using the built-in features of Excel.

### 1. Use the Fill Handle Feature

• Input 1 in cell B5 and 2 in cell B6 manually.
• Select range B5:B6 and use Fill Handle to AutoFill data in range B7:B14.

### 2. Apply the Fill Series Option of Excel

• Put 1 in cell B5.
• Go to the Home tab and select the Fill option from the EditingÂ group.
• Select Series from the drop-down menu of Fill.

• From the Series dialog box, select the Columns series and put 10 as the Stop value. Then, click OK.

Note: The StepÂ value is set to 1 by default.

• You will see the serial number in the dataset.

## Use Formula to Add Serial Number in Excel

### 1. Use Excel SEQUENCE Function

`=SEQUENCE(10)`

### 2. Use the ROW Function to Serial Number in Excel

`=ROW()-ROW(\$B\$4)`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

Here, we subtract the row number of the previous cell from the existing cell.

### 3. Apply the COUNTA Function for Serial Numbers

`=COUNTA(\$C\$5:C5)`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

Read More: Automatically Number Columns in Excel

### 4. Use the SUBTOTAL Function for Serial Numbers

`=SUBTOTAL(3,\$C\$5:C5)`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

### 5. Combine IF and ROW Functions

• Go to cell B5 and insert a formula based on the combination of IF and ROWÂ functions.
`=IF(C5="","",ROW()-ROW(\$B\$4))`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

### 6. Use of OFFSET Function to Add Serial Number in Excel

`=OFFSET(B5,-1,0)+1`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

Note: Make sure that the cell above the formula cell is blank otherwise there will be a #VALUE error. Here, cell B4 is blank.

Read More:Â How to Use Number with Filter in Excel

### 7. Combination of ROW and TEXT Functions for Serial Numbers

• Go to cell B5 and insert a formula based on the combination of ROW and TEXT functions.
`=TEXT(ROW()-ROW(\$B\$5)+1,"0")`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

### 8. Add 1 to the Previous Number to Get the Serial Number of Cells

• Put 1 in cell B5.
• Go to cell B6 and put this formula into the cell.
`=B5+1`
• Press Enter and use the Fill Handle to AutoFill data in range B7:B14.

## Insert Serial Number Using Other Features of Excel

### 1. Use Excel Name Manager to Get Serial Numbers

• Go to Formulas tab >> Define Name.

• In the New Name dialog box, add a name. We have added Serial_Number.
• Type this formula in the Refers to box and press OK.
`=INDIRECT("R[-1]C",FALSE)`

`=SUM(Serial_Number,1)`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

### 2. Insert Dynamic Serial Numbers for Filters

• Go to cell B5 and insert the following formula.
`=SUBTOTAL(3,\$C\$5:C5)`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

• Select Sort & Filter from the Home tab. Click on the Filter option from the drop-down menu.

• Click on a drop-down arrow and apply the filter according to your preference. We have unchecked serial numbers 4, 5, 6.
• Press OK to proceed.

• You will see the serial numbers updated automatically.

Read More:Â Automatically Number Rows in Excel

### 3. Automatically Add Serial Number in an Excel Table

• Put 1 in cell B5.
• Go to cell B6 and insert this formula.
`=B5+1`

• Press Ctrl+T to open the Create Table dialog box.
• Put range \$B\$4:\$D\$6 in the box of the Create Table dialog box. Also, check the My table has headers option and press OK to create a table.

• You will see a table created with the dataset.

• Type Rebecca in cell C7 and press the Tab button.
• You will see the serial number is generated automatically.

### 4. Serial Number in a Pivot Table

• Add a new column in the source data with 1 in each cell.

• Select a cell in the table.
• Go to Insert tab >> Pivot Table >> From Table/RangeÂ option.

• You will see the source data range is added automatically in the Table/RangeÂ section.
• Set the location of the pivot table in the dialog box and press OK.

• A pivot table will be created.
• Check and place the Serial Number and Name in the PivotTable Fields as shown in the image below.

• Click on Sum of Serial Numbers below and select Value Field Settings.

• Set up the dialog box as shown: Value Field Settings dialog box >> open the Show Values As tab >> select the Running Total In option from the drop-down list >> select Name as the Base field >> click OK.

• You will see a pivot table with serial numbers.

Note: Here, we get the serial number based on the Name column in ascending order.

### 5. Use Power Pivot to Add Serial Number

• Select a cell in your dataset.
• Go to Power Pivot >> Add to Data Model.

• Check the range shown in the Create TableÂ window.
• Mark My table has headers option and press OK.

• This will open a power query window.
• Add a column named Index and put the formula in the first cell of the column.
`=RANK.EQ( Table8[Name], Table8[Name], ASC )`

Here, Table8 is the name of the data model table and we are ranking based on the name.

• Go to Insert tab >> Pivot Table >> From Data ModelÂ option.

• Select the location of the pivot table and press OK.

• In the PivotTable Fields, select Name and Index from Table8.

• You will see the pivot table with the serial number in the Sum of IndexÂ column.

Note: Power Pivot is not available in all versions of Excel. It is available from Excel 2013 to the latest versions. To find it in your version follow these steps:

• Go to File >> Options >> Add-ins.
• Select COM Add-ins and press Go.

• If you have the Power Pivot in your version of Excel, you will see Microsoft Power Pivot for Excel in your dialog box. Check it and press OK.

### 6. Add Serial Numbers Using Index Column in Power Query

• Select a cell of your table.
• Go to the Data tab >> From Table/Range.

• This will open up the Power Query Editor.
• Go to Add Column tab >> Index Column command >> From 1.

• You will see the dataset with the Index column.

• Go to Home tab >> Close & Load >> Close and Load to.

• Select the location of the table and press OK.

• You will see an additional table with an IndexÂ column.

### 7. Use the Connection Properties for Connected Tables

• Create a connected table as shown in the previous method.

• Select any cell of your connected table.
• Go to the Data tab >> Properties.

• Check the Include row numbers option and press OK.

• Go to the Data tab >> press Refresh.

• You will see the serial number starting from 0.

Note: Here the serial number starts with 0 by default. This is because starting with 0 aligns with the 0-based indexing used in the data structure. This is the desired convention of Excel connection properties.

## Add Serial Number with VBA Code

• Go to the Developer tab >> Visual Basic.

• Go to Insert >> Module.

• Put the following code in your VBA Macro Editor and press the Run key or F5 to run the code.

``````Sub sequence()
Â Â Â  'variable declaration
Â Â Â  Dim WS As Worksheet
Â Â Â  'set the variable
Â Â Â  Set WS = ActiveSheet
Â Â Â  'use sequence function to generate serial number
Â Â Â  WS.Range("B5:B14") = WorksheetFunction.sequence(10)
End Sub``````
• You will see the serial number in your dataset.

## Examples of Inserting Serial Numbers in Practical Life

### 1. Add Roman Numbers as Serial Numbers

• Go to cell B5 and insert a formula based on the combination of ROMAN and ROWÂ functions.
`=ROMAN(ROW()-ROW(\$B\$4))`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

Note: The ROMAN function converts the numeric number you get from the ROW function.

### 2. Add a Serial Dates in a Column

In the dataset, we have the Names and CGPAs of students. Data are sorted in descending order of CGPA. The interview date will be in a serial according to their CGPAs.

• Go to cell B5 and insert the date.
`=1/1/2023`
• Press Enter and use the Fill Handle to AutoFill data in range B6:B14.

### 3. Convert Serial Numbers to Serial Dates

`=DATE(2023,1,B5)`
• Press Enter and use the Fill Handle to AutoFill data in range C6:C14.

## Things to Remember

There are a few things to remember while generating serial numbers in Excel:

• You may need to put the initial value.
• While using Fill Handle, be aware that the step number is always 1. If you want a custom step number, you can use the Fill SeriesÂ option.
• While using the OFFSET function, make sure that the cell above the one where you put the formula is blank.

## Conclusion

In this article, we have discussed how to generate the serial number in Excel. This article will allow users to use Excel more efficiently and create serial numbers in Excel more effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.

1. Can I generate alphanumeric serial numbers in Excel?

Yes, you can generate alphanumeric serial numbers in Excel. You can combine the ROW function with other functions like the CHAR function to convert numbers to corresponding characters.

2. Is it possible to automatically increment the serial number when adding new rows or entries?

Yes, it is possible to automatically increment the serial number when adding new rows or entries. You can use formulas that dynamically update based on the number of rows or entries in your data range. You can also use Excel Table.

3. Is there a way to generate random serial numbers in Excel?

Yes, you can generate random serial numbers in Excel using the RAND function or by creating a custom VBA macro that generates random numbers within a specific range.

## Serial Number in Excel: Knowledge Hub

<< Go Back to Numbering in Excel | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF