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 Valu**e 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,

`=B5+1`

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.

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

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.

`=SUM(AutoNumber,1)`

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.

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

**Formula Breakdown**

**MAX($B$4:B4)+1→ **The function returns the maximum value in the **B4** cell. As there is a text value so the function returns **0 **and the **+1 **has been added to the result.

**IF(C5=””,””,MAX($B$4:B4)+1)→ **The function returns the blank cells if there is a blank, then the auto numbering stays blank for that cell.

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

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

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.

`=ROW()-4`

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.

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

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.

`=SEQUENCE(14)`

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.

`=SEQUENCE(1,6)`

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.

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

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.

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

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.

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

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**