How to Do Automatic Numbering in Excel (12 Simple Ways)

While dealing with a large dataset, you may need to assign automatic numbers to your cells. However, manually applying the number is a tedious task. Also, it is not a time-efficient way to give the serial number manually. Excel doesn’t provide you with any features for automatic numbering. But there are some methods to assign automatic numbers to the cells in Excel. In this article, we’ll show you how to add automatic numbering in Excel. Let’s get started.

For automatic numbering, we have taken a dataset of Country wise Sales lists for different Products. Now, we want to put the automatic serial number for the list.

Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.

1. Using Fill Handle Tool to Do Automatic Numbering in Excel

The Fill Handle feature can easily catch the pattern from a few filled cells and then auto-fill the entire column. It is the most widely used and common feature.

Steps:

• In the “Serial No.” column, first, fill the first and second cells with 1 and 2. Then select those two cells and move your mouse cursor to the bottom right corner of the selected cell until you see this icon + (plus sign).
• When you see the cross icon, double-click on this icon and it will automatically number the cells to the end of the data row. You can drag it down also with the mouse.

Finally, you will get the automatic number, as shown in the image below.

2. Using Fill Series Command in Excel to Get Automatic Numbering

The Fill Series feature provides you with a lot more control over your datasheet than the Fill Handle. You can do the operation both for the column and the rows. Follow the steps below to create a number sequence in Excel without dragging.

Steps:

• We use the same dataset. To use the Fill Series method, first, insert 1 in your cell from where you want to start the auto number. Then go to the Home tab >> click on the Fill option>> and select Series.

• Apparently, A new Series window appeared. Select Column as we want to auto number the columns, Step Value is 1, and Stop Value is 14 because we have 14 rows to number. Click OK to continue.

Eventually, your automatic numbering will be ready (see the image).

Note: You can also auto-number rows by using the same procedures. Just change the cell option from “Column” to “Row”.

3. Adding 1 to the Previous Row Number for Automatic Numbering

You can add 1 to the previous row and drag it down for the other cells. It will assign automatic numbering to your cells. Let’s follow the instructions below to increment row number with Excel Formula.

Steps:

• By adding one in the simple form of “Sum” we can do automatic numbering in excel. First, insert 1 in the first cell from where you want to start automatic numbering (B5). In the second cell, input this formula,

This formula adds “1” to every previous cell.

Consequently, you get the below result after dragging it down.

Read More: Automatically Number Rows in Excel

4. Applying Name Manager to Do Automatic Numbering in Excel

You can set a name for your worksheet and then use the SUM function to create the automatic function. We use the INDIRECT function here to make the reference cell. Follow the below steps to perform the operation.

Steps:

• Firstly, go to the Formulas tab and select Define Name from the Defined Names section.

• Apparently, a New Name dialog wizard appears. Write a random name in the Name box. In our case we put AutoNumber.
• After that, write the following code in the Refers to the box.

Here, the above function reference is for the active cell, and [-1] indicates one row above the reference cell.

• Press OK.

• Now, go to cell B5 and insert the formula.

It will add 1 to the previous number.

Finally, you will get the result after dragging it down.

5. Combining the IF and MAX Functions for Automatic Numbering

Sometimes, you may have blank cells in the column from where you want to create automatic numbering. Obviously, you don’t want to assign a number to the blank cells. In this method, we have combined the IF and MAX functions to create the automatic numbering for dealing with such a situation. The MAX function returns the maximum value, and the IF function checks for the given argument whether the cell is blank or not. Follow the below steps to use the method.

Steps:

• Primarily, go to cell B5 and insert the formula.

• Eventually, press ENTER and drag it down.

As a result, the serial number is auto-numbered, as shown in the image below.

6. Using the OFFSET Function to Get Automatic Numbering in Excel

You can use the OFFSET function to use automatic numbering. The function starts off with a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.

Steps:

• Firstly, go to cell B6 and insert the formula.

Where Reference is B5, Row is -1, Cols is 0

The OFFSET function is an array function that takes the reference cell as B5 and the row number is -1 which is before that row and the column number is 0.

Sequentially, you get the result like the image below.

7. Applying the ROW Function to Do Automatic Numbering in Excel

We use the ROW function to give automatic numbers. But we have to input a reference cell first. The ROW function returns the row number for a given reference. The reference may be a cell or cell range. If the reference is not specified (as the argument is optional), the ROW function automatically considers the cell containing the formula as a reference. To get a proper realization, follow the steps below.

Steps:

• In cell, B5 insert the below formula.

As the ROW function will give you the cell number from the starting row (B1), subtract 4 from the function so that we can get the desired number from the function.

Eventually, the numbers will be added to your dataset.

Read More: How to Add Automatic Serial Number with Formula in Excel

8. Inserting the COUNTA Function for Automatic Numbering in Excel

The COUNTA function will allow you to auto-number only the filled cells and ignore the blank cells. Let’s learn!

Steps:

• Initially, go to cell B5 and input the formula.

Where Logical_test is ISBLANK(C5), [value_if_true] is ( ” ” ). For the true value, the formula will return a Blank space, [value_if_false] is COUNTA($C$5:C5). For the false value, the formula will return an auto number.

The IF function tests whether the adjacent cells in column “Serial No.” are empty or not. If the cell is empty, it returns a blank, but if it’s not, it returns the auto count of all the filled cells.

The result is in front of you. See the screenshot below for a better understanding.

9. Using the SEQUENCE Function to Get Automatic Numbering in Excel

Using the same dataset, we will apply the SEQUENCE function to complete our task. Follow the below steps to do that.

Steps:

• Firstly, go to cell B5 and enter the following formula.

Where, “14” is the number of rows.

After pressing ENTER, you get the result.

• You can also do the same operation for the column number.
• For doing that, go to cell B3 and write up the formula.

The SEQUENCE(1,6) syntax gives you the sequence of columns. Where 1 is the Row number and 6 is the Column number.

10. Creating an Excel Table to Get Automatic Numbering

You can create a table from which you can assign the automatic numbers to your dataset. A table in Excel has some unique features, including automatic numbering. So, creating a table does our job.

Steps:

• For creating a table, first of all, select the entire dataset and hover over to the Insert tab >> pick Table.

• A Create Table dialog box appears. Check the My table has headers box and hit OK.

• Consequently, in the B5 cell, input the formula.

It will add 1 in Serial No. column.

Finally, you get the result after dragging it down.

• This method has a unique ability that the number in the table can auto-update. To check this, delete some rows from the table.

Boom! The table has been updated automatically, as shown in the image below.

11. Using SUBTOTAL Function to Do Automatic Numbering in Excel

There is another function by which you can count the cells, and that is the SUBTOTAL function. There are several function numbers in the SUBTOTAL function from which you can do different operations.

Steps:

• Initially, go to cell B5 and write up the formula.

Here, the 3 in the function stands for the func_num which is basically the COUNTA function, and the $C$5:C5 cell is the reference cell.

Subsequently, you get the result like the image below.

12. Applying VBA Code to Get Automatic Numbering in Excel

A VBA macro makes your dataset’s auto numbering quite easy. For this reason, you will get the auto number for the cells. Follow the steps to generate an auto serial number with Excel VBA.

Steps:

• Firstly, hover over to the Developer tab >> choose Visual Basic.

• Consequently, select the Insert tab >> Module >> Module 1.

• In Module 1, write down the 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

• After that, Run the code with the F5 key and the Enter Serial Numbers dialog box appears. Enter the last serial number 14 in the box and hit OK.

Eventually, you will have obtained all of the auto-numbers from the active cell.

How to Number Columns Automatically in Excel

There are 3 suitable ways for automatically numbering columns. One of the most useful ways to do this is by using the COLUMN function.

• Firstly, go to the C4 cell and insert the formula.

Here, the function subtracts the $A$3 cell from the reference column.

• After that, press ENTER and drag it to the right side of the columns where you wanted to end the serial.

Finally, you get the result below.

Things to Remember

➤ While using the “OFFSET” function, you must keep a blank cell above the starting cell. Otherwise, the function won’t operate correctly.

➤ While using the “ROW” function, subtract the number of rows from the starting row. By doing this, the automatic numbering will start at 1.

➤ The “SEQUENCE” function is available for “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.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Conclusion

That’s all about today’s session. And these are some easy methods to generate automatic numbering in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Thanks for your patience in reading this article.

Related Articles

• How to Autofill in Excel with Repeated Sequential Numbers
• Auto Numbering in Excel After Row Insert
• How to Number Columns in Excel Automatically
• Auto Serial Number in Excel Based on Another Column
• Auto Generate Invoice Number in Excel

<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel