Maruf Islam
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,
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).
Step 3: Double-click on the PLUS icon. All other rows in the column will now have row numbers similar to the picture below.
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.
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.
Step 4: Consequently, this should give you all the row numbers as in the image below.
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
Step 2: The outcome of the formula is shown below
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) 
Step 2: As a result, the outcome depicts the image below.
Similar Readings
- How to Autofill Dates in Excel (3 Suitable Methods)
- How to Autocomplete from List in Excel (4 Easy Ways)
- 12 Automatic Ways of Numbering in Excel
- How to Repeat Rows for a Specified Number of Times in Excel
- How to Add Sequence Number by Group in Excel (2 Ways)
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
Step 3: Consequently, this should generate all the proper row numbers.
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.
Step 2: Select the Range (you want to number the row). Click OK.
Step 3: In cell B5 type in the below formula:
=ROW(Table1)-4Step 4: Press Enter. The whole Table’s rows get numbered similar to the picture below.
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)
Step 2: The rest of the rows get numbered.
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
Step 2: This should be enough to number the rest of the rows.
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
- How to Use Custom Autofilter in Excel for More Than 2 Criteria
- How to Hide Filter Arrows from Pivot Table in Excel (3 Easy Ways)
- Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
- How to Use Flash Fill in Excel to Split Data (4 Quick Ways)
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)
