Automatically number rows in Excel

Whether it’s for preparing revenues or customer lists, continuous numbering improves the clarity of Excel tables. Although Excel does not provide a button for automatic numbering, it’s easy to automatically number rows once you know how to. The following guide explains how to use Excel’s simple options to add numbering.

Auto numbering in Excel – what are the options?

Excel provides two options for automatic numbering to help make tables easier to read. On the one hand, you have the option to create continuous or user-defined number sequences with the fill box. On the other, the ROW function lets you define numbering as part of a formula.

HiDrive Cloud Storage
Store and share your data on the go
  • Store, share, and edit data easily
  • Backed up and highly secure
  • Sync with all devices

Method 1: Auto numbering with fill boxes

The fill box or auto-fill box can be found at the bottom right edge of a cell when you move your mouse cursor over it. To select multiple cells, click on the area inside a cell and drag the box down – without clicking on the little fill box at the bottom right edge.

Fill box in an Excel cell
The fill box with a green edge and green square in the bottom right.

The fill box is the easiest way to add auto numbering to rows in your Excel table. Here, you have the option to set the sequence the numbering should adopt.

Step 1: If you prefer ascending numbering (1, 2, 3, 4, ...), simply enter “1” and “2” in the first two cells of the column. Excel always needs 2 or 3 numbers to automatically detect and continue a desired sequence. Select the range with the numbers. When you select the range, make sure you don’t click on the little green square at the bottom right of the cell. Instead, click in the cell and drag the frame to the end of the corresponding cell.

Selecting the range with numbering
Select the range with the set numbering.

Step 2: Now, move the cursor to the little green box in the bottom right edge of the cell. Drag this down to the end of the area in which you’d like numbering to appear.

Select multiple cells with the fill box
Use the fill box to select the area that should be numbered.

Step 3: Excel will now complete the continuous number sequence.

Excel will now automatically number the cells
Excel will now automatically add the numbering to the cells.

You can also insert numbering for odd numbers or in steps of 10, 20, or 100, for example. To do this, simply enter the number sequence you wish to use. Excel will take care of the rest.

Numbering in different steps
Excel automatically recognizes the numbering sequence.
Tip

You can also use auto numbering to auto-complete date entries or times, for example. But you always need to provide the first 3 to 3 numbers for Excel to recognize a number sequence or pattern. Only then does the program automatically detect the sequence.

Method 2: Add numbering with the ROW function

As an alternative to the fill box, you can use the ROW function to add automatic numbering in Excel. The ROW function syntax is as follows:

=ROW([CELL REFERENCE])

The ROW function returns the row number of the reference, which is entered in brackets “()”. Add “B5” into the formula, for example.

=ROW(B5)

Now press [Enter]. The function will return “5,” since it refers to row 5 in column B.

Using the ROW function
The ROW function tells you the row number you search for.
Note

Remember that you define the cell reference. If you leave the reference empty, the ROW function will automatically output the number of the cell that contains the formula.

Now, you can use the ROW function to insert automatic numbering.

Step 1: In the first cell, enter the ROW function with the starting cell reference. If you begin automatic numbering in cell A1, enter “=ROW(A1)”.

ROW function numbering
Start numbering with the ROW function.

Press [Enter]. A “1” will now appear in cell A1.

The ROW function outputs the first number
The ROW function returns the first number.

Step 2: Now drag the fill box to the end of the desired area. The ROW function will then insert continuous numbering.

ROW function adds automatic numbering
The ROW function completes your numbering sequence.
Note

If you change the data or move or delete rows after adding the ROW function, the numbering may be interrupted. To update it, select two of the existing numbers of the numbering sequence and drag the fill box across the interrupted area.

Step 3: Would you like the ROW function with number 1 to begin in a different row? This is easy to do: Click on the cell where numbering should start, e.g. B3. Enter the ROW function and then subtract the rows that remain empty above the start of the numbering sequence. The syntax is as follows:

=ROW([CELL REFERENCE])-[NUMBER OF CELLS]

The ROW function will now start your numbering sequence with “1” in row B3, since B1 and B2 are subtracted from the numbering:

Using a specific numbering sequence with the ROW function
The ROW function can start the numbering sequence in any desired cell.

Drag the fill box down again. The ROW function will insert the numbering automatically.

Automatic numbering with the ROW function
The ROW function completes your numbering sequence.
Tip

For more professional spreadsheets, a whole range of tricks is available to format a table in Excel.

Create a Business Email Address
Build your brand with professional email
  • 2 GB+ storage
  • Sync across all your devices
  • Spam filter and ad-free
Was this article helpful?
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.