12 Ways to AutoFill Numbers in Excel

Method 1: AutoFill a Column with a Series of Numbers

Example Model:

Use the Fill Handle option to autofill the series of numbers starting from 1.

Steps:

Select Cell B5.
Select the cell and find the Plus (+)

Drag the Plus (+) icon downward.
Click on the options menu and select the Fill Series

Note: A series of numbers will appear starting from 1 to 9.

Read More: How to Auto Number Cells in Excel

Method 2: AutoFill Numbers by Using the ROW Function in Excel

In the following picture, Cell B5 is situated in Row 5. So if you apply the ROW function in that cell, the function will return ‘5’.

Steps:

Enter the following formula into cell

=ROW()-4

To start the number with ‘1’, input the following formula in Cell B5:

=ROW()-4

Note: Since the data was in the 5th row, the ROW function returned the number ‘5’. To replace it with number ‘1’, we have to subtract ‘4’ from the ROW function.

Method 3. Insert the OFFSET Function to AutoFill Numbers in a Column

Steps:

Enter the following formula into cell

=OFFSET(B4,-1,0)+1

After pressing Enter, the number ‘1’ will appear.

Note: Keep in mind that while using this formula immediate upper cell blank must be blank.

Use the Fill Handle to autofill the column.

Note: You don’t have to choose the Fill Series option anymore as shown in the first method.

Method 4: AutoFill Numbers by Using the Fill Series Command

Steps:

From the Home , go to the Editing group of commands.
Select the Series command from the Fill drop-down under the Editing group of commands.

Note: A dialogue box named ‘Series’ will open up.

Let’s assume we want to create a series of numbers with a common difference of ‘2’ and the series will end with the last value of not more than 20.

Select the Columns radio button from the Series in options.
Input ‘2’ and ‘20’ in the Step value and the Stop Value.
Press OK.

Note: You’ll find the series of numbers with the mentioned criteria right away.

Method 5. AutoFill Numbers While Skipping Rows (Blank Cells)

Example Model:

Select two successive cells starting from the first input data as shown in the following screenshot.

Steps:

After auto-filling the column with the Fill Handle, you’ll find the series of numbers starting with ‘1’ while skipping a row at regular intervals.

Method 6: AutoFill Formulas in a Column in Excel

Steps:

Enter the following formula into cell D5.

=C5*5%

Use the Fill Handle from Cell D5 and drag it down to Cell D11.

Method 7: Double-Click the Fill Handle to AutoFill Numbers

Steps:

Click the Fill Handle icon in the right-bottom corner of Cell D5 twice.

Method 8: AutoFill Numbers using a Geometric Pattern

Steps:

Open the Series dialogue box from the Fill options in the Editing group of commands.
Select the Columns button as the Series in option.
Choose Growth as the Type of the series.
Input ‘2’ and ‘200’ as the Step value and the Stop value
Press OK.

Note: You’ll find the geometric series starting from 2 with a growth rate of 2, which means each value will be multiplied by 2 until the final output exceeds 200.

Method 9: AutoFill a Date Series in a Column

Select the Columns radio button from the Series in options.

Note: After pressing OK the dates are as shown in the following picture.

Method 10: AutoFill Row Numbers with COUNTA Function to Ignore Blank Cells

In the following picture, Column B will represent the serial numbers. We have to assign a formula in Cell B5, drag it down to the bottom, and define the serial numbers for all non-blank rows.

Steps:

Enter the following formula into cell B5.

=IF(ISBLANK(C5),””,COUNTA($C$5:C5))

Press Enter for the first serial number ‘1’ as the first row in the table is not empty.

Use the Fill Handle to autofill the entire Column B.

Note: The serial numbers for all non-empty rows appear at once.

Method 11: Use the SUBTOTAL Function to AutoFill Numbers for Filtered Data

In the following table, we’ve filtered the data for Sam only. We’ve extracted the sales values of greater than $1500 for Sam here. However, after filtering the table, the serial numbers have also been modified. Let’s assume, we want to maintain the serial numbers as they were shown initially.