How to Add Numbers Automatically in Excel

Automatic line numbering

Click on the first cell where the number sequence will begin. This method instructs how to have each cell in the column display the corresponding row number. You should use this method if rows are frequently added or deleted from the spreadsheet.

To create a basic row of consecutive numbers (or other data like day of the week or month of the year), see How to Fill a Column with Consecutive Numbers .

Type =ROW(A1)into cell (if this is cell A1). If it is another cell, you need to replace it accordingly.

For example, if you are entering cell B5, type =ROW(B5).

Press ↵ Enter. The cell line number will appear. If you entered =ROW(A1), cell A1 displays the number 1. If you enter =ROW(B5), cell B5 will display the number 5.

To start from 1 at any line where you want to start the number range, count the number of lines above the current cell, then subtract the result in the function.

For example, if you enter =ROW(B5)and want to display the number 1 in this cell, change the function to =ROW(B5)-4, because B4 counts back 4 lines as B1.

Select the cell containing the first number in the number range.

Move the mouse pointer to the frame located in the lower right corner of the selected cell. This quarter note is called the Fill Handle. The mouse pointer will turn into a scissors icon when located directly above the Fill Handle.

If you don't find the Fill Handle, navigate to the File tab > Options > Advanced and check the 'Enable fill handle and cell drag-and-drop' box. drag-drop cell feature)

Drag the Fill Handle down to the last cell in the range. Each cell in the column will display the corresponding row number.

If you delete a line in this range, the cell number will automatically be corrected based on the new line number.

Fill in consecutive numbers in the column

Click on the first cell where the number sequence will begin. This method will show you how to add consecutive numbers to cells in a column.

If you use this method but then delete a row, you will need to go through the steps again to renumber the entire column. If you think you will change data lines frequently, you should apply the Automatic Line Numbering method above.

Enter the first number in the sequence into a cell. For example, if you number from top to bottom in a column, type it 1in the first cell.

You don't have to start at 1. Your sequence can start at any number, and can even be formatted differently (such as even numbers, multiples of 5, etc.). .

Excel also supports other types of 'numbering', including dates, seasons and days of the week. For example, when you fill in the days of the week in a column, the first cell will display 'Monday'.

Click the next cell in the template. This is the cell directly below the current cell.

Enter the second number of the sequence to create the pattern. To number consecutively (like 1, 2, 3, etc.), enter 2 here.

If you want the next numbers to be 10, 20, 30, 40, etc., then the next two numbers in the sequence you need to enter are 10and 20.

If you are entering by day of the week, type the next day of the week in the box.

Click and drag to select all cells. When you release the mouse, the cells you just entered will be highlighted.

Move the mouse pointer to the small frame located in the lower right corner of the selected area. This frame is called the Fill Handle. The mouse pointer will turn into a scissors icon when located directly above the Fill Handle.

If you don't find the Fill Handle, go to the File > Options > Advanced tab and check the box 'Enable fill handle and cell drag-and-drop.'

Click and drag the Fill Handle down to the last cell in the number range. After you release the mouse, the cells in the column will be numbered according to the pattern you established in the first two cells.