How to Autofill Numbers in Excel (12 Ways)

There are several short and quick methods to autofill numbers in Microsoft Excel. In this article, you’ll learn how you can easily apply those useful techniques to autofill numbers under different criteria with appropriate illustrations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


12 Suitable Approaches to Autofill Numbers in Excel

1. Autofill a Column with a Series of Numbers

In our first example, we’ll see the basic use of Fill Handle to autofill a series of numbers. In the picture below, a number ‘1’ has been inputted in Cell C5. Now, we’ll use the Fill Handle option to autofill the series of numbers starting from 1.

Autofill a Column with a Series of Numbers

📌 Step 1:

➤ Select Cell B5.

➤ Put your mouse cursor onto the right-bottom corner of the selected cell, you’ll find a Plus (+) icon there.

Autofill a Column with a Series of Numbers

📌 Step 2:

➤ Drag the Plus (+) icon downward as long as you want.

➤ Click on the options menu as shown in the following picture and select the Fill Series command.

Autofill a Column with a Series of Numbers

And you’ll be shown the series of numbers starting from 1 to 9.

Autofill a Column with a Series of Numbers

Read more: How to Autocomplete Cells or Columns From List in Excel


2. Autofill Numbers by Using ROW Function in Excel

The ROW function returns the row number of a cell reference. By inserting this ROW function in a cell and dragging it downward, we can find a series of numbers in a column.

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

Autofill Numbers by Using ROW Function in Excel

Now we can use the Fill Handle option to autofill the column up to a specific cell. If I want to start the number with ‘1’, then I have to input the following formula in Cell B5:

=ROW()-4

Autofill Numbers by Using ROW Function in Excel

If I wanted to start the number with ‘1’, then I had to input the following formula in Cell B5:

=ROW()-4

As my first input data was in the 5th row, the ROW function returned the number ‘5’. So, to get the number ‘1’ there, we have to subtract ‘4’ from the ROW function.


3. Insert OFFSET Function to Autofill Numbers in a Column

OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference. By using the OFFSET function, we can create a series of numbers without using the Fill Series option after copying down.

In the following picture, the formula applied in Cell B4 is:

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

Insert OFFSET Function to Autofill Numbers in a Column

After pressing Enter, you’ll find the number ‘1’. You must keep in mind that while using this formula in a cell, you have to keep the immediate upper cell blank.

Insert OFFSET Function to Autofill Numbers in a Column

Now use Fill Handle to autofill the column and you’ll get the series of numbers at once. You don’t have to choose the Fill Series option here anymore as shown in the first method.

Insert OFFSET Function to Autofill Numbers in a Column

Read more: How to Use Autofill Formula in Excel


4. Autofill Numbers by Using Fill Series Command in Excel

We can use the Fill Series option more precisely by activating the dialogue box from the Series command. Let’s go through the following steps to see how it works.

📌 Step 1:

➤ From the Home ribbon, go to the Editing group of commands.

➤ Select the Series command from the Fill drop-down under the Editing group of commands.

A dialogue box named ‘Series’ will open up.

Autofill Numbers by Using Fill Series Command in Excel

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.

📌 Step 2:

➤ Select the Columns radio button from the Series in options.

➤ Input ‘2’ and ‘20’ in the Step value and the Stop value respectively.

➤ Press OK and you’re done.

Autofill Numbers by Using Fill Series Command in Excel

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

Autofill Numbers by Using Fill Series Command in Excel

Read more: Automatic Numbering in Excel


5. Autofill Numbers While Skipping Rows (Blank Cells)

We can use the Fill Handle option to autofill a column while skipping rows at a regular interval. Let’s assume, we want to fill a series of numbers in a column where each number will skip a row to surpass the preceding number.

What we have to do is select two successive cells starting from the first input data as shown in the following screenshot.

Autofill Numbers While Skipping Rows (Blank Cells)

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.

Autofill Numbers While Skipping Rows (Blank Cells)


6. Autofill Formulas in a Column in Excel

We can use the Fill Handle option to autofill formulas too along a column or a row. In the following dataset, the first two columns are representing the amounts of sales of some salesmen. In Column D, a 5% bonus will be added to each salesman based on their sales values. In Cell D5, the first bonus amount has been done manually by using the following formula:

=C5*5%

Autofill Formulas in a Column in Excel

Now if we use the Fill Handle from Cell D5 and drag it down to Cell D11, we’ll get the resultant outputs as displayed in the picture below.

Autofill Formulas in a Column in Excel


7. Double-Click Fill Handle to Autofill Numbers

There’s another way of using the Fill Handle option and that is double-clicking the icon. In this process, the entire column will be updated automatically and you won’t have to drag down the icon anymore to autofill.

In the picture below, you’re seeing the Fill Handle icon in the right-bottom corner of Cell D5. Let’s click the icon twice and you’ll see the instant output.

Double-Click Fill Handle to Autofill Numbers

Like in the picture below, you’ll get the return values immediately.

Double-Click Fill Handle to Autofill Numbers


8. Autofill Numbers with a Geometric Pattern in Excel

We can autofill numbers in a series by applying a geometric pattern too. What we have to do is set a multiplier for an initial value and set the series type as Growth. Let’s go through the following steps now:

📌 Steps:

➤ Open Series dialogue box again from the Fill options in the Editing group of commands.

➤ Select Columns radio 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 respectively.

➤ Press OK.

Autofill Numbers with a Geometric Pattern in Excel

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

Autofill Numbers with a Geometric Pattern in Excel


9. Autofill a Date Series in a Column

We can use the Fill Series command to autofill a series of dates too. What we have to do is select the Series type as Date and then input the Step value and Stop value. If we want to show the dates in a column then we have to select the Columns radio button from the Series in options.

Autofill a Date Series in a Column

After pressing OK, we’ll find the series of dates as shown in the following picture.

Autofill a Date Series in a Column

Read more: How to Autofill Dates in Excel


10. Autofill Row Numbers with COUNTA Function to Ignore Blank Cells

COUNTA function counts the number of cells in a range that are not empty. By using the COUNTA function, we can define the serial numbers of the non-blank rows in a table or a dataset.

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.

Autofill Row Numbers with COUNTA Function to Ignore Blank Cells

📌 Step 1:

➤ Select Cell B5 and type the following formula:

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

➤ Press Enter and you’ll get the first serial number ‘1’ as the first row in the table is not empty.

Autofill Row Numbers with COUNTA Function to Ignore Blank Cells

📌 Step 2:

➤ Now use the Fill Handle to autofill the entire Column B.

And you’ll find the serial numbers for all non-empty rows at once.

Autofill Row Numbers with COUNTA Function to Ignore Blank Cells


11. Use SUBTOTAL Function to Autofill Numbers for Filtered Data

In the following dataset, the number of sales of the three salesmen has been recorded for successive 15 days. Column B here represents the serial numbers of the rows. Since this is a filtered data table, we’ll find out how the serial numbers react after filtering the amounts of sales of a specific salesperson.

Use 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. But 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.

Use SUBTOTAL Function to Autofill Numbers for Filtered Data

📌 Step 1:

➤ Select Cell B5 and type:

=SUBTOTAL(3,$C$5:C5)

➤ Use Fill Handle to autofill the entire column.

Use SUBTOTAL Function to Autofill Numbers for Filtered Data

📌 Step 2:

➤ Now filter the sales values of Sam to show the amounts that are greater than $1500 only.

And you’ll now see that the serial numbers have not been modified here and they’re maintaining the sequence of the numbers.

Use SUBTOTAL Function to Autofill Numbers for Filtered Data


12. Create an Excel Table to Autofill Row Numbers (ROW Function)

In our last example, we’ll show how to insert a row inside a data table while the serial numbers will be updated simultaneously.

📌 Step 1:

➤ Select the entire table data (B5:F19) and name it with SalesData by editing in the Name Box.

Create an Excel Table to Autofill Row Numbers (ROW Function)

📌 Step 2:

➤ Select Cell B5 and type:

=ROW()-ROW(SalesData[#Headers])

The entire Column B in the data table will display the serial numbers.

Create an Excel Table to Autofill Row Numbers (ROW Function)

📌 Step 3:

➤ Now right-click any of the row numbers on the left of the spreadsheet with your mouse cursor.

➤ Select the Insert option.

Create an Excel Table to Autofill Row Numbers (ROW Function)

Like in the picture below, a new row will be added in the selected region and the serial numbers of the entire data table will be updated simultaneously.

Create an Excel Table to Autofill Row Numbers (ROW Function)


Concluding Words

I hope all the methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo