There are two ways to use Microsoft Excel's Auto Numbering to automatically insert a series of numbers within a column. The most reliable way to number rows in a worksheet is to use Excel's ROW function; by doing so you will be sure that the cells will display the correct numbering even if new rows are added or some of the existing ones are deleted. Alternatively, you can use the "cell fill and drag handle" (which doesn't require you to use any formulas). The latter method is very simple to implement, with the only disadvantage that deleting an existing row will create a break in the number series. Read on to find out how to number the rows of an Excel sheet dynamically or manually (starting from a predefined scheme).
Steps
Method 1 of 2: Dynamic Numbering
Step 1. Select the first cell where you want the row numbering to start
This procedure shows how to make each cell in a column display the corresponding row number. This is a great solution when you need to frequently add or remove rows from your worksheet.
To create a simple numbering using a series of consecutive numbers or data (such as days of the week or months of the year), see this section of this article
Step 2. Type the formula = ROW (A1) into the chosen cell (assuming it is cell A1)
If the selected cell is not A1, use the references of that choice.
For example, if you are entering data in cell B5, you will need to use the formula = ROW (B5)
Step 3. Press the Enter key
At this point the selected cell should show the reference numbering, that is the number corresponding to the row. For example, if you have used the formula = ROW (A1), the number 1 will be displayed in the selected cell. If instead you have chosen to use the formula = ROW (B5), the number 5 will be displayed.
- To make the numbering start from 1 regardless of the selected cell, you simply have to subtract the number of cells preceding the chosen cell from the result of the formula.
- For example, if you used the formula = ROW (B5) and you want the automatic numbering to start from 1, you will need to change it like this = ROW (B5) -4 precisely because the cells preceding cell B5 are 4.
Step 4. Select the cell which contains the first number of the number series
Step 5. Place the mouse cursor on the "cell fill and drag handle" located in the lower right corner of the selected cell
When the mouse cursor is superimposed directly on the fill handle it automatically changes to a small "+".
If the fill handle is not displayed, access the "File" menu, choose the "Options" item, then display the "Advanced" tab and select the check button "Enable fill handle and cell dragging"
Step 6. Drag the fill handle to the final cell of the group to be numbered
All the chosen cells will show the corresponding row number.
If you were to add or delete a row within the group that belongs to the numbered set, the cells will be automatically updated with the new row reference number
Method 2 of 2: Manual Numbering Using an Existing Set of Numbers
Step 1. Select the first cell where you want the row numbering to start
This procedure shows how to insert a series of consecutive numbers within a group of cells in a column.
Using this method, in order for the numbering to remain correct when deleting an existing row, you will have to manually repeat the procedure to renumber all the selected cells entirely. If you need to often reorganize the data in individual rows, use this method because it is more suitable for the purpose
Step 2. Type the first number of the number series to be used as a reference pattern within the chosen cell
For example, if you want to number a group of cells in a column, enter the number 1.
- The data series to be used do not necessarily have to start from 1. You can use any starting number and any logical reference scheme. For example, you can use a series of numbers that are multiples of 5.
- Excel also allows you to use non-numeric data series, such as dates, seasons, days of the week or months of the year. For example, if you want to fill a column with the days of the week you will have to type the string "Monday" in the first cell.
Step 3. Select the next cell in the series
In this case it should be the cell directly below the currently selected one.
Step 4. Enter the second element to create the numbering scheme to use as a reference for the data series
To get consecutive numbering (1, 2, 3, etc.), you will need to enter the number 2.
- If you want to use a series of numbers that repeat with a different pattern, for example 10, 20, 30, 40, etc., in the first cell you will have to enter the number 10 and in the second the number 20.
- If you want to use the days of the week instead, type "Monday" in the first cell and "Tuesday" in the second.
Step 5. Drag the mouse cursor to select both cells that contain the data series to use as a reference
When you release the mouse button, the chosen cells should appear highlighted.
Step 6. Place the mouse cursor on the "cell fill and drag handle" located in the lower right corner of the group of highlighted cells
When the mouse cursor is superimposed directly on the fill handle it automatically changes to a small "+".
If the fill handle is not displayed, access the "File" menu, choose the "Options" item, then display the "Advanced" tab and select the check button "Enable fill handle and cell dragging"
Step 7. Drag the fill handle to the final cell of the group to be numbered
When you release the mouse button, all the cells in the chosen set will be numbered using the numbering scheme described by the first two cells.
Advice
- A free application is available on the Microsoft website that replicates a simplified version of Excel that can be used by anyone with a Microsoft services account.
- Alternatively, you can browse and edit your spreadsheets using the Google Sheets app.
