Microsoft Excel Logo

Microsoft Excel provides row headers starting with number 1. But if you have data that you want to number beginning below the first row, these row headers aren’t much help. We’ll show you how to add your own row numbers.

You may want to number rows for an easy way to collaborate and make sure you’re talking about the same data. Or, maybe you want to use a specific sequential series for reference numbers throughout your sheet. You can add numbers like this in Excel using the fill handle or a function. Let’s walk through the options.

Number Rows Using the Fill Handle

By using the fill handle, you can create a series of numbers or letters by dragging through cells. The key to using this method is the pattern you begin with and copy.

For example, if you simply want to start numbering with 1 and go in order, you would enter “1” in the first cell and “2” in the cell below it. This establishes a pattern.

Start a fill pattern

Then, select both cells and drag the fill handle to the subsequent rows. You’ll see a preview as you drag showing the numbers that will populate.

Drag the fill handle to number the rows

Advertisement

If you have a break in your data and want to continue the numbering after the break, you can do so with the next pattern in the series.

For instance, you may have rows numbered 1 through 6, a break of three rows, and then want to pick up the numbering with 7 for the rest.

Enter “7” in the cell you want to number and “8” in the cell below. Then select both cells and use the fill handle to drag once again. Excel is smart enough to know your pattern and comply.

Continue numbering rows

Number Rows Using the ROW Function

Another way to number rows is using the ROW function. With it, you can also use the fill handle to drag the formula you enter to the remaining rows.

Select the cell where you want to start numbering. If you want to start with the number 1, you would use the A1 cell reference and enter:

=ROW(A1)

Enter the ROW function in Excel

You can then drag the formula to the cells in the rows below. Now if you have a break in your data, like in the example above, you can continue your numbering after the break with this same function.

Advertisement

Go to the cell where you want to pick up the numbering and enter the function with the corresponding cell reference for the number you need. So, if you want to start with number 5, you’d use A5 or if you want to start with number 10, you’d use A10.

For our example, we want to continue with the number 7, so we would enter:

=ROW(A7)

Continue number with the ROW function

And again, you can then drag the formula to the remaining cells using the fill handle.

Use a Custom Number Series

As mentioned, you can use these methods for numbering rows, but also for reference numbers. Maybe you have product orders, customers, or something similar that you want assign numbers to. For instance, you might want to start numbering as 0001, 0002, and 0003 or 00-001, 00-002, and 00-003.

With the ROW function, you can include the TEXT function to designate the numbering format for the series you want.

As an example, we want to start numbering with 0001. Here is the formula to enter:

=TEXT(ROW(A1),"0000")
Advertisement

With this formula in the cell, the result would be 0001. The A1 reference assigns a number 1 and the 0000 is the format.

Enter the TEXT and ROW functions

And like the other methods listed here, you can use that convenient fill handle to copy the formula to the cells in the subsequent rows.

Continue numbering with the TEXT and ROW functions

To continue numbering after a break, just use the corresponding cell reference as described earlier.

If you’re not fond of the way that Excel uses letters as column headers, did you know that you can change the cell reference style? Have a look!

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio ยป

The above article may contain affiliate links, which help support How-To Geek.