Automatic numbering in Excel is a great way to save time and avoid boredom while you are dealing with a large database. Unlike other Excel Office programs, Excel does not provide any function or option to number data automatically. But we can auto-number our data by using many methods. Today in this article we will learn some effective methods to auto number your data in Excel.

**Table of Contents**hide

## Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

## Auto Number in Excel (9 Ways)

In this section, we will discuss nine methods to auto number your data. Of course, there will be other ways to do it but I think these are methods that you can use quite comfortably.

### 1. Using Fill Handle

The **â€śFill Handleâ€ť** feature can easily catch the pattern from a few filled cells and then can auto-fill the entire column.

**Step-1:**

Consider an example where the** â€śNameâ€ť, â€śCountryâ€ť, â€śProductâ€ť, â€śSale1â€ť,** and **â€śSale 2â€ť** are given for some sales reps. We will now number them automatically.

**Step-2:**

In the **â€śSerial No.â€ť **column, first, fill 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

**Step-3:**

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.

**Read more:** **How to Autofill Numbers in Excel**

### 2. Using Fill Series

**â€śFill Seriesâ€ť** will give you a lot more control over your datasheet than the **â€śFill Handleâ€ť.**

**Step-1:**

We will use the same datasheet. To use the **â€śFill Seriesâ€ť** method, first, insert **1** in your cell from where you want to start the auto number. Then go to **â€śHomeâ€ť,** click on the** â€śFillâ€ť **option and select **â€śSeriesâ€ť.**

**Step-2:**

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

And our auto numbering is done.

**Step-3**:

You can also auto number rows by using the same procedures. Just change the cell option from **â€śColumnâ€ť** to **â€śRowâ€ť.**

Click** â€śOKâ€ť** to get the auto-numbered cells.

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

### 3. Adding One to the Previous Row Number

**Step-1:**

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 (**B4**). In the second cell, input this formula,

This formula will add **â€ś1â€ť** to every previous cell.

Press **â€śEnterâ€ť** to get the result.

**Step-2:**

Click and drag to auto number the rest of the cells.

### 4. Using the OFFSET Function

**Step-1:**

Using the **â€śOFFSETâ€ť **function we can auto-number the cells. To show this method we will use the same datasheet. First, add a blank row above the cell from where you want to start auto numbering cells. Now in the starting cell, input this formula,

Where,

**Reference**is**B5****Row**is**-1****Cols**is**0**

Press** â€śEnterâ€ť** to get the result.

**Step-2:**

Now move your cursor to the bottom right of the cell then click and drag to apply this formula to the rest of the cells.

**Read more:** **How to Use Autofill Formula in Excel**

### 5. Using the ROW Function

**Step-1:**

In the starting cell (B4), apply the **â€śROWâ€ť** function. The formula is,

As the** â€śROWâ€ť **function will give you the cell number from the starting row (**B1**), minus **3** from the function so that we can get the desired number from the function.

**Step-2:**

Press **â€śEnterâ€ť** to get the automatic numbering. Now click and drag to auto number the remaining cells.

**Similar Readings:**

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

### 6. Using the COUNTA Function

The** â€śCOUNTAâ€ť** function will allow you to auto number only the filled cells and will ignore the blank cells. Letâ€™s learn!

**Step-1:**

Letâ€™s say we have a dataset where some of the cells are blank. We need to number the filled cells.

**Step-2:**

In the starting cell (**B4**), from where you will start automatic numbering, apply the combo of the **â€śIFâ€ť **and the **â€śCOUNTAâ€ť** function. The formula is

Where,

**Logical_test**is**ISBLANK(C4)****[value_if_true]**is**( â€ť â€ť )**. For the true value, the formula will return a Blank space**[value_if_false]**is**COUNTA($C$4:C4)**. 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.

Press **â€śEnterâ€ť **to continue.

**Step-3:**

Now since the formula is working, apply this to the remaining cells. From the result, we can see only the filled cells are auto-numbered. The others remain blank.

### 7. Using the SEQUENCE Function

**Step-1:**

Using the same dataset, we will apply the basic **SEQUENCE** function to complete our task.

In cell **â€śB4â€ť**, apply the **â€śSEQUENCEâ€ť** function for columns. The formula is,

Where **â€ś14â€ť** is the number of rows.

Press** â€śEnterâ€ť** to get the auto numbering column.

**Step-2:**

You can also auto-number the rows using the **â€śSEQUENCEâ€ť **function. In this case, the formula is,

Here **â€ś1â€ť** is the row argument and **â€ś6â€ť** is the number of columns that we want to auto-number.

Press** â€śEnterâ€ť** to get the result.

### 8. By Creating a Table in Excel

A table in excel has some unique features including automatic numbering. We will discuss this now.

**Step-1:**

First, we will create a table using the same dataset. To do this, select the whole data set including headers, go to** â€śInsertâ€ť,** select** â€śTableâ€ť**.

**Step-2:**

In the newly appeared window, check **â€śMy Table has Headersâ€ť**.

And click on **â€śOKâ€ť** to create the table.

**Step-3:**

Now in the column **â€śB4â€ť** apply the **â€śROWâ€ť **function. The formula is,

Here **â€śTable3â€ť **is the name of our table.

Click **â€śOKâ€ť** to auto number all the required cells.

**Step-4:**

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

And we can see that the numbers are automatically updated.

### 9. Using SUBTOTAL for Filtered Data

In some cases, when we need to filter our data in the datasheets, the serial numbers get scattered. There is a way where we can keep the auto numbering even after the filtering. Letâ€™s discuss.

**Step-1:**

First, add filter icons to your headers using the filter option.

**Step-2:**

Now change the order in the column **â€śSale 1â€ť** from Largest to smallest.

Click **â€śOKâ€ť** we can see that the numbers are not in ascending order.

**Step-3:**

To keep the auto numbering updated, apply the** â€śSUBTOTALâ€ť** function in cell **â€śB4â€ť**.

The final formula is

Here, **â€ś3â€ť** is function number (**COUNTA **function), and the reference is **$C$4:C4.**

Press** â€śEnterâ€ť** to activate the function.

**Step-4:**

Now letâ€™s again change the sort order of the **â€śSale 1â€ť** column.

Click **â€śOKâ€ť**. And we can see that the** â€śSerial No.â€ť** is automatically numbered. So our task is done!

## 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 from 1.

âž¤ The **â€śSEQUENCEâ€ť** function is available for **â€śExcel 365â€ť** and other updated versions.

## Conclusion

Automatic numbering cells in Excel are discussed in this article using some effective methods. If you have any kind of confusion or thoughts, we will be happy to see your comment in the comment section.