There are two different ways to automatically add numbers to worksheet columns in Excel. One of the best ways to number rows in Excel is to use the ROW function. This function ensures that cells return the correct number of rows, even if later rows are entered or deleted. Another way (which doesn't require typing a formula) is to use the Fill feature. This method is easier, but if a row is deleted later, your numbering sequence will be truncated. Learn how to number the lines of your worksheet using these two methods.
Step
Method 1 of 2: Dynamically Numbering Worksheet Lines
Step 1. Click the first cell where the series of numbers will start
This method describes how to make each cell in a column display its corresponding row number. This method is useful if you will frequently add or delete lines on a worksheet.
To create a sequence of consecutive numbers in a column of a worksheet (or other data, such as a date or month series), see this section
Step 2. Type =ROW(A1) into the cell (if the starting cell of the number sequence is A1)
If not, enter the appropriate cell number.
For example, if you are typing in cell B5, type =ROW(B5)
Step 3. Press Enter
Your cell will display the row number. If you type =ROW(A1), the cell will contain the number 1. If you type =ROW(B5), the cell will contain the number 5.
- To start with the number 1, regardless of where the first row of the number is, count the number of rows above your current cell, then subtract that number from your formula.
- For example, if you enter =ROW(B5) and want the cell to contain the number 1, edit your formula to =ROW(B5)-4 because B1 is four lines above B5.
Step 4. Select the cell that contains the first number from the series of numbers
Step 5. Place the cursor over the box in the lower right corner of the selected box
This box is called the Fill Handle. When the cursor is exactly on the Fill Handle, the cursor changes to a plus symbol.
If you don't see Fill Handle, go to File > Options > Advanced and check the box next to “Enable fill handle and cell drag-and-drop.”
Step 6. Slide the Fill Handle down to the last cell of your number series
The cells in the column will contain the numbers according to the row.
If you delete a row that contains a row of numbers, the cell numbers will adjust automatically based on the new row numbers
Method 2 of 2: Filling Columns with Rows of Numbers
Step 1. Click the first cell where the series of numbers will start
This method will show you how to add a series of numbers to cells in a column.
If you use this method and then need to delete a row, this step will need to be repeated to repeat the numbering of the entire column. If you feel that you will frequently change rows of data in a worksheet, take a look at this section
Step 2. Type the first number of your series into the cell
For example, if you are going to number all cells in a column, type 1 into this cell.
- You don't have to start at 1. Your sequence can start at any number, and it can also follow other patterns (e.g. even numbers, multiples of 5, etc.).
- Excel also supports other types of "numbering," such as dates, seasons, and days of the week. For example, fill the first cell with “Monday” to fill the column with the row of days of the week.
Step 3. Click the next cell in the row pattern
This cell should be directly below the current active cell.
Step 4. Type in the second number of the row to create the pattern
To create a series of numbers (1, 2, 3, etc.), type 2 here.
- If the desired series of numbers is 10, 20, 30, 40, etc. or similar, the first two cells in the series must contain 10 and 20.
- If you entered a row of days of the week, type the next day into the cell.
Step 5. Click and drag to select both cells
When the mouse button is released, both cells will be highlighted.
Step 6. Place the cursor on the small square in the lower right corner of the highlighted area
This box is named Fill Handle. The cursor will change to a plus symbol when on the Fill Handle.
If you can't see the Fill Handle, go to File > Options > Advanced and check the box next to “Enable fill handle and cell drag-and-drop.”
Step 7. Click and drag the Fill Handle down to the last cell of your row
If you release the mouse button, the cells in the column will be numbered according to the pattern of the first two cells.
Tips
- Microsoft provides a free version of Excel as part of Microsoft Office Online.
- You can also open and edit worksheets in Google Sheets.
