12 Automatic Ways of Numbering in Excel - ExcelDemy (2023)

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.

Download Practice Workbook

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

12 Methods to Do Automatic Numbering in Excel

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

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.

Read more: How to Autofill Numbers in Excel

2. Using Fill Series Command

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. To do so, follow the steps below.

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”.

Read more: How to Autocomplete Cells or Columns From List in Excel

3. Adding 1 to the Previous Row Number

You can add 1 to the previous row and drag it down for the other cells. It will assign automatic numbering to your cells.

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.

4. Applying Name Manager

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

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. Utilizing the OFFSET Function

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.

Read more: How to Use Autofill Formula in Excel

7. Applying the ROW Function

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.

Similar Readings:

• Autofill Dates in Excel (3 Suitable Methods)
• How to AutoFill Cell Based on Another Cell in Excel (5 Methods)

8. Employing the COUNTA Function

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

Using the same dataset, we will apply the basic 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 a Table in Excel

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

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. Incorporating VBA Code

A VBA Macros makes your dataset’s auto numbering quite easy. For this reason, you will get the auto number for the cells. Follow the steps.

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 Integeri = InputBox("Put Value", "Automatic Numbering")For i = 1 To iActiveCell.Value = iActiveCell.Offset(1, 0).ActivateNext iEnd 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.

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. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.

Further Readings

• How to Auto Populate Cells in Excel Based on Another Cell
• Fix: Excel Autofill Not Working (7 Issues)
• How to Perform Predictive Autofill in Excel (5 Methods)