How to Add Numbers Automatically in Excel

There are two different ways to add numbers to a column automatically. The surest way to add automatic number ranges to Excel is to use the ROW function. This method will ensure that cells display the correct numbering even when rows are added or deleted. The second way (without typing the formula) is to use the Fill feature, this method is easier but when you delete the line the number range will be missing. Let's learn how to automatically number rows, or enter consecutive numbers in columns.

Automatic line numbering

How to Add Numbers Automatically in Excel Picture 1

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 .

How to Add Numbers Automatically in Excel Picture 2

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).

How to Add Numbers Automatically in Excel Picture 3

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.

How to Add Numbers Automatically in Excel Picture 4

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

How to Add Numbers Automatically in Excel Picture 5

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)

How to Add Numbers Automatically in Excel Picture 6

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

How to Add Numbers Automatically in Excel Picture 7

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.

How to Add Numbers Automatically in Excel Picture 8

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'.

How to Add Numbers Automatically in Excel Picture 9

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

How to Add Numbers Automatically in Excel Picture 10

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.

How to Add Numbers Automatically in Excel Picture 11

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

How to Add Numbers Automatically in Excel Picture 12

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.'

How to Add Numbers Automatically in Excel Picture 13

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.