Microsoft Excel provides row headers starting with the number 1. But if you have data that you want to number starting at the first row, these row headers won’t help much. We’ll show you how to add your own row numbers.
You may want to number the rows for an easy way to collaborate and make sure you’re talking about the same data. Or perhaps you would like to use a specific concatenation of reference numbers in your paper. You can add numbers like this in Excel using the fill or function handle. Let’s browse the options.
Number of rows using fill handle
With the fill handle, you can create a series of numbers or letters by dragging across cells. The key to using this method is the pattern you start with and copying.
For example, if you simply want the numbering to start with 1 and go in order, you can enter “1” in the first cell and “2” in the cell below it. This establishes a pattern.
Next, select both cells and drag the fill handle to the next rows. You will see a preview as you swipe showing the numbers to be published.
If you have a separator in your data and you want to continue the numbering after the separator, you can do that with the next pattern in the string.
For example, you might have rows numbered from 1 to 6, a fraction of three rows, and then you want to capture 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 again. Excel is smart enough to know your pattern and compliance.
Number of rows using the ROW . function
Another way to number rows is to use the ROW function. With it, you can also use the fill handle to drag the formula you’re entering into the remaining rows.
Select the cell where you want to start the numbering. If you want to start with number 1, you can use cell reference A1 and enter:
=ROW(A1)
You can then drag the formula into the cells in the rows below. Now if you have a separator in your data, as in the example above, you can continue the numbering after the separator using the same function.
Go to the cell where you want to capture the numbering and enter the function with the cell reference corresponding to the number you need. So, if you want to start with 5, you’ll use A5 or if you want to start with 10, you’ll use A10.
For our example, we want to continue with the number 7, so we will enter:
=ROW(A7)
And again, you can then drag the formula into the remaining cells using the fill handle.
Use a custom number string
As mentioned, you can use these methods for row numbering, but also for reference numbers. Maybe you have product orders, customers, or something similar that you want to assign numbers to. For example, you might want to start the numbering as 0001, 0002, 0003 or 00-001, 00-002, and 00-003.
With the ROW function, you can include the TEXT function to set the numbering format for the string you want.
As an example, we want to start the numbering with 0001. Here is the formula to enter:
=TEXT(ROW(A1),"0000")
With this formula in the cell, the result will be 0001. Reference A1 assigns a number 1 and 0000 is the format.
And like the other methods listed here, you can use this convenient fill handle to copy the formula into the cells in the following rows.
To continue the numbering after the break, simply use the corresponding cell reference as described earlier.
If you’re not fond of the way Excel uses letters as column headers, did you know that you can change the cell reference style? take a look!
