Automatic Numbering in Excel (9 Approaches)

getting result

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.

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.

creating table

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

creating table

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.

final result

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

creating table

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.

auto numbering

And our auto numbering is done.

auto number

Step-3:

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

auto number row

Click “OK” to get the auto-numbered cells.

final result

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,

=B4+1

This formula will add “1” to every previous cell.

creating table

Press “Enter” to get the result.

applying formula

Step-2:

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

getting result

4. Using the OFFSET Function

Step-1:

Using the “OFFSETfunction 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,

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

Where,

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

applying formula

Press “Enter” to get the result.

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

final result

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,

=ROW()-3

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.

creating table

Step-2:

Press “Enter” to get the automatic numbering. Now click and drag to auto number the remaining cells.

applying formula


Similar Readings:


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.

creating table

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

=IF(ISBLANK(C4),””,COUNTA($C$4:C4))

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.

creating table

Press “Enter” to continue.

Appying formula

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.

getting result

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,

=SEQUENCE(14)

Where “14” is the number of rows.

creating table

Press “Enter” to get the auto numbering column.

getting result

Step-2:

You can also auto-number the rows using the “SEQUENCE” function. In this case, the formula is,

=SEQUENCE(1,6)

Here “1” is the row argument and “6” is the number of columns that we want to auto-number.

Row numbering

Press “Enter” to get the result.

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

creating table

Step-2:

In the newly appeared window, check “My Table has Headers”.

creating table

And click on “OK” to create the table.

creating table

Step-3:

Now in the column “B4” apply the “ROW” function. The formula is,

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

Here “Table3” is the name of our table.

applying formula

Click “OK” to auto number all the required cells.

getting result

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.

testing formula

And we can see that the numbers are automatically updated.

final result

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.

creating table

Step-2:

Now change the order in the column “Sale 1” from Largest to smallest.

filtering

Click “OK” we can see that the numbers are not in ascending order.

scattered number

Step-3:

To keep the auto numbering updated, apply the “SUBTOTAL” function in cell “B4”.

The final formula is

=SUBTOTAL(3,$C$4:C4)

Here, “3” is function number (COUNTA function), and the reference is $C$4:C4.

applying formula

Press “Enter” to activate the function.

getting result

Step-4:

Now let’s again change the sort order of the “Sale 1” column.

testing formula

Click “OK”. And we can see that the “Serial No.” is automatically numbered. So our task is done!

final result

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.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo