How to Number Rows in Excel – Add Serial Numbers [2024]

Excel is the top spreadsheet application for productivity. You can easily organize complex datasets and compile beautiful-looking reports within a couple of hours. Numbering rows is also easy with built-in functions like the ROW and COUNTA.

Imagine a part of your job is to keep track of hundreds of entries every day, and you are looking to assign numbers in increasing order to each new entry automatically.

It may take several hours to assign serial numbers to them manually.

In this article, let us discuss various methods to number rows or columns in Excel. We will discuss multiple functions and built-in tools, so make sure to read the article till the end.

This auto-number (how to number rows in Excel) guide is divided into the following sections. Click on the links to jump to that particular section:

• Numbering rows using the Fill Handle
• Numbering rows using the Fill Series 
• Automatically numbering rows using the ROW function
• Automatically numbering rows using the COUNTA function

Download the Example Excel Sheets

Consider downloading the following Excel spreadsheet, which contains sheets used to demonstrate adding serial numbers to rows. Make sure you follow me along as I will take you through the article, it will strengthen your knowledge of the functions and tools.

Click Here To Download!

If you have your own Excel ready with the required dataset, skip downloading the above file.

How to Number Rows in Excel

Numbering rows in Excel is a common task that helps us keep track of data in an organized way.

There are different methods to do so. Let’s discuss each one of them in detail.

METHOD #1 – Using the Fill Handle 

This is one of the quickest methods to number the rows sequentially. It automatically assigns numbers in sequential order to selected rows.

Let’s see how:

• Open the desired Excel spreadsheet
• Choose a cell from where you want the number sequence to begin
• Type “1” in the chosen cell
• In the cell below, enter “2”
• Determine your final number in the sequence
• Select both cells
• Hover to the lower-rightmost corner of the second cell
• You will notice a “+” icon
• Click on it and drag it to the end of the table, as shown in the following GIF
  

Note that Excel will only populate sequential numbers across the selected cells, as shown in the above GIF.

METHOD #2 – Using the Fill Series Method

The Fill Series is a feature of Excel that allows users to create a sequence of numbers across rows or columns quickly. Such a feature is handy when you need to generate a long list of serial numbers.

To understand it better, let us consider the following table including employee details such as their names and job titles.

Our task is to enter the serial numbers for each employee in the first column.

Let’s begin,

• Open the desired Excel spreadsheet
• Enter “1” in the first cell of the column
• Navigate to the “Home” tab in the main menu
• Click on the “Fill” option in the “Editing” group
  
• From the Fill dropdown menu, select the “Series” option
  
• This will open a Series dialog box, in which you will need to set up the series
  
• Make sure to select the “Columns” option in the “Series in” field
  
• Enter the stop value. If you have 20 records, then enter the Stop Value as “20”
  
• Click the “OK” button once done
• This will instantly number the rows from 1 to 20, as shown below
  

This method is similar to the first one. It is used to assign serial numbers to existing entries.

METHOD #3 – Using the ROW Function 

Let us learn to use the built-in Excel function to number the rows. It allows you to number rows within a few seconds quickly.

Explained: The ROW function in Excel

The ROW is a lookup function that returns the row number of the given cell reference. It has the following general syntax,

=ROW([reference])

The “reference”  argument needs to be replaced with a range of cells or arrays whose row numbers need to be displayed.

It can be combined with other functions in Excel to create powerful formulas.

Now, let us use this function to add serial numbers to rows.

We will consider the following table, which contains 20 rows, excluding the header row.

Let’s begin,

• Open the desired Excel sheet
• Double-click on the cell “A2”
• Type “=ROW”
• Select the first option from the popup or press “Tab” on your keyboard
• Close the brackets of the formula “)”
• Now use the mathematical operator “–”
• Type “1”
  (The ROW function gives the row number of the current row. If we subtract 1 from it, we will get the row number offset by “1”)
• Press the “Enter” button to see the result
  

The final formula would be as follows, 

=ROW()-1

Since our data starts from the second row, we will subtract 1 from the current row number. If your data starts from row 4, then the formula would be,

=ROW()- 3

The main advantage of using this function is that it creates a dynamic sequence of numbers. 

If you delete a row in between your dataset, the sequence will not get disturbed. Since it is not referencing any cell, it will automatically adjust to give you the correct row number.

As you can see, we have deleted a row, and the row numbers have been automatically updated.

METHOD #4 – Using the COUNTA Function 

This method is suitable for situations where you want the serial numbers for the rows or columns that are filled. 

Note that we need to combine the COUNTA with the other functions, such as the IF and ISBLANK.

Explained: The IF Function in Excel

The IF is a conditional function that returns values based on a logical expression defined by the users.

The general syntax for the IF function is as follows,

=IF(logical_expression, value_if_true, value_if_false)

Each argument of the function needs to be replaced with the proper information,

• “logical_expression” – It represents a criterion or condition determining the output. It returns TRUE or FALSE.
• “value_if_true” – Here, you can specify the desired output if the logical expression argument returns the TRUE.
• “value_if_false” – Similar to the second argument, it is used to display the second desired output if the logical expression argument returns the FALSE as an output.

It is quite a simple formula with self-explanatory arguments. Often, comparison operators are used to create logical expressions for the function.

Explained: The ISBLANK Function in Excel

It is a simple function in Excel that returns TRUE or FALSE based on the cell’s content.

Here’s the general syntax for the ISBLANK function,

=ISBLANK(value)

The “value” argument needs to be replaced with the cell reference value, which you wish to check is empty. The function then returns TRUE if the cell contains numbers or letters and FALSE if it doesn’t.

Explained: COUNTA function in Excel

The COUNTA function in Excel counts all cells in a range that has values. The values can be numbers, alphabets, dates, time, and even error values.

The General Syntax for the COUNTA function is as follows,

=COUNTA(value1, [value2]..)

Where the “value” argument needs to be replaced with the cell references or cell ranges you wish to count the blank cells.

That’s all about the functions. Let us use them to number rows.

We will consider the following table to understand how the combination of the above functions helps us number rows.

Here are the steps,

• Open the desired Excel spreadsheet
• Select the cell “A2”
• Type “=IF”
• Select the first option from the popup or press “Tab” on your keyboard
• Type in “ISBLANK”
• Select the first option from the popup or press “Tab” on your keyboard
  
• In place of the “value” argument, enter the cell reference as “B2”
• Complete the bracket for the ISBLANK function using “)”
• Press “,” on your keyboard to move to the next argument of the IF function
• Put double quotations without any space as ““””
• Press “,”
• Now, type “COUNTA”
• Select the first option from the popup or press “Tab” on your keyboard
  
• In place of the “value1” argument, enter “$B$2:B2”
• Close the brackets for the COUNTA formula using “)”
• Complete the entire formula with a closing parenthesis “)”
• Press the “Enter” to see the result
  

The final formula would be as follows,

=IF(ISBLANK(B2),””,COUNTA($B$2:B2)

You can directly copy and paste the above formula, but make sure to replace the cell references wherever necessary.

To apply this formula for the rest of the cells, you need to hover over the Fill Handle that appears in the lower-rightmost corner of cell A2. Next, click on it and drag it to the end of the table, as shown at the end of the above GIF.

Conclusion

Numbering rows is pretty straightforward in Excel.

Make sure to use the COUNTA function method when you have blank rows throughout your table.

I hope this article taught all the bells and whistles of assigning serial numbers to rows. You can use the same methods to number columns as well.

Feel free to comment below if you are stuck somewhere or are having any particular issue. Also, don’t forget to check our blog for more tips and tricks to use Microsoft Excel like a pro.