How to Do Automatic Numbering in Excel (12 Methods)

Dataset Overview

For automatic numbering, we will be working with a dataset of countrywide sales lists for different products.

Method 1 – Using Fill Handle Tool

The Fill Handle feature in Excel is widely used and common. It allows you to automatically fill an entire column based on a pattern detected from a few filled cells.

Open your Excel workbook and navigate to the sheet where you want to add automatic numbering.
- In the Serial No. column (or any other column where you want to add numbers), manually enter the first two numbers (e.g., 1 and 2).
- Select both cells containing these numbers.
- Move your mouse cursor to the bottom right corner of the selected cell until you see the + sign (the Fill Handle).
- Double-click on the Fill Handle, and Excel will automatically fill the entire column with a sequential series of numbers.
- Alternatively, you can click and drag the Fill Handle down to extend the numbering to the desired range.

Here’s an example of how it works:

Remember to adjust the starting numbers and customize the range as needed for your specific dataset.

Method 2 – Using Fill Series Command

The Fill Series feature in Excel provides more control over your datasheet than the Fill Handle. You can apply this operation to both columns and rows. Follow the steps below to create a number sequence without manually dragging:

Enter the value 1 in the cell where you want to begin the automatic numbering.
Go to the Home tab.
Click on the Fill option.
Select Series from the dropdown menu.

A new Series window will appear.
Choose Column since we want to auto-number the columns.
Set the Step Value to 1.
Specify the Stop Value as 14 (since we have 14 rows to number).
Click OK to proceed.

Your automatic numbering sequence will now be generated.

Note: You can also apply the same procedure to auto-number rows by selecting Row instead of Column.

Method 3 – Adding 1 to the Previous Row Number

Enter the value 1 in the first cell where you want to start the automatic numbering (e.g., cell B5).
In the second cell (e.g., the cell below B5), enter the following formula:

=B5+1

- This formula adds 1 to the value in the previous cell.

Drag the formula down to fill the subsequent cells.
As a result, each cell will automatically increment by 1 from the previous row.

You’ll achieve the desired automatic numbering sequence after performing these steps.

Read More: Automatically Number Rows in Excel

Method 4 – Using the Name Manager

Go to the Formulas tab.
Select Define Name from the Defined Names section.

A New Name dialog wizard will appear.
Enter a random name in the Name box (for example, AutoNumber).
In the Refers to box, enter the following code:

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

The above function reference is for the active cell, and [-1] indicates one row above the reference cell.
Press OK to save the defined name.

Go to cell B5 (or any other cell where you want to start the automatic numbering).
Insert the following formula:

=SUM(AutoNumber,1)

This formula adds 1 to the previous number.

Drag the formula down to fill the subsequent cells.
As a result, each cell will automatically increment by 1 from the previous row.

Method 5 – Combining IF and MAX Functions

Scenario: Sometimes, you may encounter blank cells in a column where you want to create automatic numbering. In such cases, you wouldn’t want to assign a number to those blank cells. This method combines the IF and MAX functions to handle this situation effectively.

Steps:
- Go to cell B5 (or any other cell where you want to begin the automatic numbering).
- Insert the following formula:

=IF(C5="","",MAX($B$4:B4)+1)

- Formula Breakdown:
  - MAX($B$4:B4) + 1: This part of the formula returns the maximum value in the range from cell B4 to the cell above the current cell (B4 to the cell immediately above the active cell). If the cell above is blank (contains text), the function returns 0, and then we add 1 to it.
  - IF(C5=“”, “”, …): The IF function checks whether cell C5 is blank. If it is, the result remains blank; otherwise, it calculates the next auto-number by adding 1 to the maximum value.

Press ENTER to apply the formula.
Drag the formula down to fill the subsequent cells in the column.
As a result, each cell will automatically increment by 1 from the previous row, excluding blank cells.

You’ll achieve the desired automatic numbering sequence, as shown in the image below:

Method 6 – Using the OFFSET Function

Select cell B6.
Enter the following formula:

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

- The B6 reference indicates the starting cell.
- The -1row offset means moving one row up from the starting cell (to cell B5).
- The 0 column offset keeps the same column (no horizontal movement).

The result will be a sequential numbering pattern, as shown in the image below:

Method 7 – Using the ROW Function

Select cell B5.
Enter the following formula:

=ROW()-4

- The ROW( ) function returns the row number of the specified reference cell (in this case, B5).
- By subtracting 4 from the result, we obtain the desired numbering.

The numbers will be automatically added to your dataset.

Method 8 – Using the COUNTA Function

The COUNTA function allows you to automatically number only the filled cells while ignoring the blank ones. Here’s how to do it:

Select cell B5.
Enter the following formula:

=IF(ISBLANK(C5),"",COUNTA($C$5:C5))

- The ISBLANK(C5) logical test checks whether the adjacent cell in column Serial No. (C5) is empty.
- If the cell is empty, the formula returns a blank space.
- If the cell is not empty, it returns the count of all filled cells in the range from C5 to the current row (C5 to C5).

The result will provide automatic numbering based on the filled cells.

Method 9 – Using the SEQUENCE Function

In this method, we’ll utilize the SEQUENCE function to automatically generate sequential numbers in an Excel worksheet. Follow the steps below to achieve this:

Open your Excel workbook containing the dataset.
Navigate to cell B5 (or any other cell where you want the numbering to start).
Enter the following formula:

=SEQUENCE(14)

Replace 14 with the desired number of rows.
Press ENTER to get the result.

To generate a sequence of column numbers, go to cell B3 (or any other cell).
Enter the formula:

=SEQUENCE(1,6)

Here, the syntax SEQUENCE(1, 6) produces a sequence of columns. The first argument (1) represents the row number (always 1 in this case), and the second argument (6) specifies the number of columns.

Method 10 – Creating an Excel Table

Excel tables offer unique features, including automatic numbering. Let’s create a table:

Select the entire dataset.
Go to the Insert tab and choose Table.

In the Create Table dialog box, check the My table has headers option and click OK.

In cell B5 (or any other cell within the table), enter the following formula:

=ROW()-ROW(Table3[#Headers])

This formula adds 1 to the Serial No. column.

Drag the formula down to fill the entire column.

The beauty of this method is that the table’s numbering will automatically update. Try deleting some rows from the table, and you’ll see the numbering adjust accordingly.

Your Excel table now handles automatic numbering seamlessly.

Method 11 – Using SUBTOTAL Function

The SUBTOTAL function provides a way to count cells while allowing flexibility for different operations. Here’s how you can use it for automatic numbering:

Open your Excel workbook containing the dataset.
Go to cell B5 (or any other cell where you want the numbering to appear).
Enter the following formula:

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

In this formula:

- The 3 represents the func_num, which corresponds to the COUNTA function (i.e., counting non-empty cells).
- $C$5:C5 defines the reference range. The first part ($C$5:C5) is an absolute reference to the starting cell, and the second part (C5) is a relative reference that adjusts as you drag the formula down.

Press ENTER to calculate the result.
The SUBTOTAL function will count the non-empty cells in the specified range, providing automatic numbering.

And there you have it! Your Excel sheet now handles automatic numbering using the SUBTOTAL function.

Method 12 – Using VBA Code

A VBA macro simplifies the process of auto-numbering your dataset. By following these steps, you’ll be able to generate automatic serial numbers using Excel VBA:

Open your Excel workbook containing the dataset.
Hover over to the Developer tab.
Choose Visual Basic to open the Visual Basic for Applications (VBA) editor.

In the VBA editor, select the Insert tab.
Choose Module to create a new module (e.g., Module 1).

Within the newly created module, enter the following code in the General box:

Sub AutoNum()
Dim i As Integer
i = InputBox("Put Value", "Automatic Numbering")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
End Sub

Press the F5 key to run the code.
An Enter Serial Numbers dialog box will appear.
Enter the last serial number (e.g., 14) in the box and click OK.

The active cell will now display the auto-generated numbers.
Drag the formula down to fill the desired range.

Your Excel sheet now handles automatic numbering using VBA.

How to Automatically Number Columns in Excel

To automatically number columns in Excel, you can use the COLUMN function. Here’s how:

Go to cell C4 (or any other cell where you want to start numbering).
Insert the following formula:

=COLUMN(B4)-COLUMN($A$3)

This formula subtracts the column number of cell B4 from the column number of cell A3.

Press ENTER.
Drag the formula to the right across the columns where you want the numbering to continue.

Your columns will now be automatically numbered.

Things to Remember

Using the OFFSET Function:
- When using the OFFSET function, make sure to keep a blank cell above the starting cell. Otherwise, the function may not operate correctly.
Using the ROW Function:
- To start automatic numbering at 1, subtract the number of rows from the starting row when using the ROW function.
SEQUENCE Function Availability:
- The SEQUENCE function is available in Excel 365 and other updated versions.

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.