Editor choice

Automatically Number Rows in Excel (10 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to automatically number rows in Excel? Do not worry, we are here for you. In this article, we will describe 10 easy ways to do the task smoothly.

Excel does not offer any features to number data while we enter/import them. Thus, 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 the following video, you can see that we have automatically numbered rows in Excel. So let’s dive into the following article to do the task.


What Is Excel Auto-Number Feature?

Although Excel does not have built-in Auto-Number features or functions,  it provides various features and functions that can be used to generate auto-incremented numbers or sequential numbering in a column.

Users can utilize these functions to create a more orderly spreadsheet by assigning numbers to important cells in the spreadsheet. An auto-number feature is a convenient tool that accountants, legal professionals, tech experts, and students can use for any type of spreadsheet, saving them time.


Why Do You Need to Automatically Number Rows in Excel?

Here are some reasons why automatically creating numbered rows in Excel can be beneficial:

  • Organization: Automatically numbering rows helps keep your spreadsheet structured and organized. It creates a clear order for the data, making it simpler to find and refer to specific rows.
  • Sorting and Filtering (Data Analysis): Numbered rows make it easy to sort and filter data effectively. When you use automatic numbering, you can quickly organize the rows in either increasing or decreasing order based on their numbers. This is particularly useful when dealing with big sets of data.
  • Data Entry: Using auto-numbering in Excel helps you save time and energy when entering data. Instead of typing row numbers yourself, Excel can generate and increase the numbers automatically as you add data. This removes the need for counting manually and lowers the risk of making mistakes.
  • Collaboration: In teamwork settings, when rows are automatically given numbers, it helps everyone have a clear and consistent way to refer to them. Each team member can use their assigned numbers to easily identify and talk about specific rows, making communication easier and clearer.
  • Document Control: When you make forms or templates in Excel, auto-numbering rows helps with managing documents better. It makes sure that every new entry gets a special code that is different from the others. This can be helpful for keeping track of things and checking them later.

Automatically Number Rows in Excel: 10 Easy Ways

The following dataset has several rows, however, there is no serial number of the rows. Now, using this dataset, we will go through 10 easy methods to automatically number rows in Excel.

Here, we used Microsoft 365. You can use any available Excel version.

Dataset to automatically number rows in Excel


1. Using SEQUENCE and COUNTA Functions to Automatically Number Rows in Excel

In this method, we will use the combination of SEQUENCE and COUNTA functions to automatically number rows in Excel. This is a handy and effective way to do the task.

  • First of all, we will type the following formula in cell B5.
=SEQUENCE(COUNTA(C:C)-1)
  • After that, as soon as we press ENTER, the rows get automatically numbered according to their serial.

 

Formula Breakdown

 SEQUENCE(COUNTA(C:C)-1)

  • COUNTA(C:C): This part of the formula counts the number of non-empty cells in column C. The range C:C represents the entire column C.
  • COUNTA(C:C)-1: Here, we subtract 1 from the count obtained in step 1. This is done to exclude the header cell from the count.
  • SEQUENCE(COUNTA(C:C)-1): The SEQUENCE function generates a sequence of numbers based on the specified parameters. In this case, it will create a sequence starting from 1 and ending at the value obtained in step 2 (the count minus 1). Each number in the sequence will increment by 1.

Read More: How to Add Automatic Serial Number with Formula in Excel


2. Use of VBA Code to Number Rows Automatically in Excel

Here, we will use VBA code to automatically number rows in Excel. This is a quick way to do the task. Also, this is very much effective when you are dealing with a larger dataset. Let’s follow the instructions below to generate an auto serial number using VBA macros.

  • First of all, to bring out the VBA Editor window, we will go to the Developer tab >> select Visual Basic.

Here, you can also press the ALT+F11 keys to bring out the VBA Editor window.

Going to Developer tab and selecting Visual Basic

After that, a VBA Editor window will appear.

Then, from the Insert tab >> select Module.Selecting Module from Insert Tab

  • Next, we will type the following VBA code in the Module.
Sub AUTOMATIC_ROW_NUMBER()
Dim row_range As Range, data_range As Range, c As Long
B = 1
Set data_range = Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row)
       For Each row_range In data_range.SpecialCells(xlCellTypeVisible)
                row_range(1, 0).Value = B
                B = B + 1
       Next row_range
End Sub

VBA Code to automatically number rows in Excel

Code Breakdown

 Sub AUTOMATIC_ROW_NUMBER()

  • This line defines the start of a subroutine named AUTOMATIC_ROW_NUMBER.

Dim row_range As Range, data_range As Range, c As Long

  • This line declares three variables: row_range, data_range, and c.
  • row_range and data_range are declared as Range objects, which can hold a range of cells in Excel.
  • c is declared as a Long data type, which can store large integer values.

B = 1

  • This line assigns the value 1 to a variable named B.
  • It initializes B as the starting value for numbering the rows.

Set data_range = Range(“C5:C” & Range(“C” & Rows.Count).End(xlUp).Row)

  • This line sets the data_range variable to a range of cells from C5 to the last non-empty cell in column C.

 For Each row_range In data_range.SpecialCells(xlCellTypeVisible)

  • This line starts a For…Next.. loop that iterates through each cell in the data_range variable, which represents the visible cells in column C.
  • SpecialCells(xlCellTypeVisible) filters the range to only include visible cells, excluding any hidden or filtered cells.

row_range(1, 0).Value = B

  • This line assigns the value of B to the cell in the current row of the row_range
  • row_range represents a single cell within the data_range

B = B + 1

  • This line increments the value of B by 1, preparing it for the next row.

Next row_range

  • This line marks the end of the loop.

End Sub

  • Ends the Sub

After that, when we Run the code, you can see the rows get serial numbers automatically.


3. Inserting Table and Using ROW Function

Here, we will first insert a Table with our dataset. After that, we will use the ROW function to automatically number rows in Excel.

  • First, to insert a Table, we will select the range of cells C4:F16.

Here, we will insert the Table excluding the Serial column.

  • Then, we will go to the Insert tab >> from the Tables group >> select Table.

Inserting Table

At this moment, a Create Table dialog box will appear.

  • Then, make sure My table has headers is marked >> click OK.

Clicking OK on Create Table dialog Box

Therefore, you can see the created table.

  • Now, we will type the following formula in cell B5.
=ROW(Table2)-4
  • After that, when you press ENTER, you will see the rows get serial numbers automatically.

Read More: How to Auto Generate Number Sequence in Excel


4. Using Fill Handle Feature

Here, we will use the Fill Handle tool to automatically number rows in Excel.

  • First, enter 1 & 2 in cells B5, and B6
  • 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).
  • Double-click on the PLUS

Using Fill Handle Tool to automatically number rows in Excel

As a result, all the rows now have serial numbers.

Row Number using Fill Handle Tool

Note: If you have data with adjacent blank entries, double-clicking 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.

5. Applying Fill Series Feature

In this method, we will use the Fill Series feature to automatically number rows in Excel. Unlike Fill Handle, the Fill Series option in Excel offers various autofill ways such as Linear, Growth, Date, and Autofill. Go through the process below to create a number sequence in Excel without dragging using the Fill Series feature.

  • To begin with, enter 1 in B5.
  • Next, go to Home Tab>> Click Fill (in the Editing Section)>> Select Series (from the drop-down menu).

Selecting Fill Series Option to automatically number rows in Excel

At this point, a Series dialog box will appear.

  • Select Columns (under Series in) & Linear (under Type).
  • Thereafter, choose Step Value & Stop Value in the dialog box (in our case, Step Value 1, Stop Value 12).
  • Click OK.

Selecting Item from Series Dialog Box

Therefore, you can see all the row numbers in the following image.

Result After Using Fill Series Feature


6. Using ROW Function to Automatically Number Rows in Excel

Here, we will use the ROW function to automatically number rows in Excel. Let’s follow the instructions below to do automatic numbering rows in Excel!

  • First of all, type the following formula in cell B5.
=ROW()-4

The ROW function finds and returns the row number of a cell reference.

  • After that, press ENTER.

Therefore, you can see the outcome in cell B5.

  • Moreover, we will drag down the formula with the Fill Handle tool.

Thus, you can see the complete Serial column.

Using ROW Function to Automatically Number Rows in Excel

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

7. Utilizing COUNTA Function

Here, we will use the COUNTA function to automatically number rows in Excel.

  • First of all, type the following formula in cell B5.
=COUNTA($C$5:C5)

The COUNTA function calculates the number of nonempty cells in a range.

  • Then, press ENTER.

As a result, you can see the outcome in cell B5.

  • Moreover, we will drag down the formula with the Fill Handle tool.

You can see the complete Serial column.

Using COUNTA Function to automatically number rows in Excel


8. Adding Number

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

  • First Enter 1 in the first row (B5).

Here, you can use any number to serialize the rows.

  • In the 2nd row type the following formula and press ENTER.
=B5+1
  • Then drag the Fill Handle down to copy this formula.

adding number to automatically number rows in Excel

Therefore, you can see the complete Series column.

Result after Adding Number

Read More: How to Increment Row Number in Excel Formula


9. Using SUBTOTAL Function

We can use the SUBTOTAL function to automatically number rows in Excel.

  • First of all, type the following formula in cell B5.
=SUBTOTAL(3,$C$5:C5)

The SUBTOTAL function finds and returns the subtotal for a range of cells.

  • After that, press ENTER.

Therefore, you can see the outcome in cell B5.

  • Moreover, we will drag down the formula with the Fill Handle tool.

Thus, you can see the complete Serial column.

Applying SUBTOTAL Function to automatically number rows in Excel


10. Utilizing OFFSET Function to Automatically Number Rows in Excel

Here, we will use the OFFSET function to automatically number rows in Excel.

  • First, type the following formula in cell B5.
=OFFSET(B5,-1,0)+1
  • Next, press ENTER.

Therefore, you can see the outcome in cell B5.

  • After that, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Serial column.

Using OFFSET Function to automatically number rows in Excel


How to Hide or Display Fill Handle Tool in Excel

The Fill Handle tool is displayed by default. However, in many cases, you may need to hide this. Also, if your Fill Handle tool is not displayed, you may need to unhide it. Here, we will show both.

  • First, go to the File

Going to File Tab

  • After that, select Options.

Selecting Options

At this point, an Excel Options dialog box will appear.

  • Select Advanced.
  • Now, to display the Fill Handle tool, mark the Enable fill handle and cell drag and drop.
  • Finally, click OK.

Displaying Fill Handle Tool

  • If you want to hide the Fill Handle tool, then unmark the Enable fill handle and cell drag and drop >> click OK.

Hiding Fill Handle Tool


Things to Remember While Using Auto-Numbered Methods

  • Identify Values Before Using Functions: Make sure to choose the numbers you want to begin and end with before using any function. Also, consider which method is suitable depending on how big your spreadsheet is, as larger spreadsheets can be more complicated.
  • Verify Your Work: Although auto-number functions can be very helpful, they are not always flawless. Therefore, it is crucial to verify your functions to make sure you obtain the desired numerical order for each row.
  • Learn from Online Resources: You can also watch videos on the internet that teach you how to do things through a screen. This might make it easier for you to understand what the finished function looks like and follow along step by step with someone explaining and showing you how to do it.

Download Practice Workbook

You can download the Excel file and practice the explained methods.


Conclusion

In this article, we describe 10 easy ways to automatically number rows in Excel. We extensively describe each method, so we hope you find each method effective.

In addition to this, we describe how you can hide or display the Fill Handle tool in Excel. Also, we describe some tips that might come in handy for you while using automatically-numbered methods. Moreover, we describe the reasons behind using automatically numbering rows in Excel.

We hope this article is helpful for you. Thank you for going through the article. If you have any queries or suggestions, please let us know in the comment section.


Related Articles


<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo