How to Number Rows Automatically in Excel (8 Easy Methods)

In this tutorial, I am going to show you 8 easy methods to number rows automatically in Excel. Excel does not offer any features to number data while we enter/import them. It’s become difficult to understand any dataset without numbering. For better presentation & understanding, it’s necessary we serial the entries in any dataset. In this article, we discuss methods like Fill Handle, Fill Series, Adding Numbers, and various Functions to number rows automatically.

Suppose, we have a dataset of Obtained Marks in three subjects by students,

automatically number rows in excel


Download Practice Workbook


8 Easy Methods to Automatically Number Rows in Excel

Method 1: Using Fill Handle Feature

Step 1: First, enter 1 & 2 in cells B5, and B6.

Step 2: Then, select both of the cells (B5 and B6). A small green-colored Square will appear at the bottom of the cells that we selected. Move the cursor to the Square & it will turn into a black colored PLUS icon (Fill Handle).

Using Fill Handle to Number Rows Automatically in Excel

Step 3: Double-click on the PLUS icon. All other rows in the column will now have row numbers similar to the picture below.

result of fill handle

If you have data with adjacent blank entries, Double click on Fill Handle will not work. In that case, you have to Drag the Fill Handle up to your desired row & you’ll find results like the below image numbering both blank & non-blank entries.

Read More: How to Apply AutoFill Shortcut in Excel (7 Methods)


Method 2: Applying Fill Series Feature

Unlike Fill Handle, the Fill Series option in Excel offers various autofill ways such as Linear, Growth, Date, and Autofill. You can choose whichever you prefer.

Step 1: To begin with, enter 1 in B5.

Step 2: Next, go to Home Tab>> Click  Fill (in the Editing Section)>> Select Series (from the drop-down menu), and a dialog box will appear.

Using Fill Series to Number Rows Automatically in Excel

Step 2: After that in the dialog box, Select Columns (under Series in) & Linear (under Type).

Step 3: Thereafter, choose Step Value & Stop Value in the dialog box (in our case, Step Value 1, Stop Value 12) and click OK.

Setting Series Options to Number Rows Automatically in Excel

Step 4: Consequently, this should give you all the row numbers as in the image below.

Fill Series Result

Read More: Applications of Excel Fill Series (12 Easy Examples)


Method 3: Using ROW Function

Row numbers displayed through Fill Handle or Fill Series are constant in the position. If we move the entries, the row numbers will not update. For this reason, the ROW function is a better method to work with.

Step 1: Type the formula below in cell B5 and press Enter. Then also drag the Fill Handle down to copy this formula:

=ROW()-4

Using Row Function to Number Rows Automatically in Excel

Step 2: The outcome of the formula is shown below

Row Function Result

Since no reference cell is required, all the rows get numbered automatically.

The ROW Function numbers row irrespective of adjacent row’s entry types. If you want to ignore blank rows to be numbered, you can use =IF(ISBLANK(C5),””,ROW()-4) typed in B5. Then drag the Fill Handle to serial. Though the formula numbers blank cells without showing it.

Read More: How to Fill Down to Last Row with Data in Excel (4 Easy Ways)


Method 4: Utilizing COUNTA Function

The COUNTA Function only numbers rows with entries in them.

Step 1: Type the following formula in B5 and press Enter. After that also, drag the Fill Handle down to copy this formula:

=COUNTA($C$5:C5) 

Using COUNTA Function to Number Rows Automatically in Excel

Step 2: As a result, the outcome depicts the image below.

Counta Function Result


Similar Readings


Method 5: Adding Number

Adding a number to the previous row number can display the row number as you want.

Step 1: First Enter 1 in the first row(B5).

Step 2: In the 2nd-row type the following formula and press Enter. Then also drag the Fill Handle down to copy this formula:

=B5+1

adding number method

Step 3: Consequently, this should generate all the proper row numbers.

adding number result

You can use any number to serialize the rows.

Read More: How to AutoFill Numbers in Excel (12 Ways)


Method 6: Implementing Excel Table

Excel Table organizes data in a manner that users find convenient to automatically number any rows via typing a table formula.

Step 1: Go to Insert Tab >> Table.

adding table

Step 2: Select the Range (you want to number the row). Click OK.

Selecting Table Data

Step 3: In cell B5 type in the below formula:

=ROW(Table1)-4

Step 4: Press Enter. The whole Table’s rows get numbered similar to the picture below.

Table Formula

Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)


Method 7: Using SUBTOTAL Function

The SUBTOTAL function only numbers rows with entries in it.

Step 1: Type the following formula in cell B5. Also, press Enter and drag the Fill handle:

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

Subtotal Formula

Step 2:  The rest of the rows get numbered.

Subtotal Result


Method 8: Utilizing OFFSET Function

We can use the OFFSET function to serial rows having entries. In that case, you have to have the column name omitted or blank in the dataset.

Step 1: Type this formula in cell B5 and press Enter. Make sure that cell B4 is empty. Then, drag the Fill Handle:

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

Offset Formula

Step 2:  This should be enough to number the rest of the rows.

Offset Result


Conclusion

In the article, we use a dataset without blank entries. In the case of blank entries that exist in a dataset, not all methods work similarly to serial them. We provide tricks to number/ ignore blank entries as notes. Thus, there are no other scenarios left where users find themselves stranded to number the rows in a dataset. Hope you find these described methods super easy & steps convenient to follow. Comment, if further clarifications are needed or want to add something.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo