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.

**Table of Contents**hide

**How to AutoFill Numbers in Excel: 12 Ways **

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

**📌**** 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.

**📌**** 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.

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

**Read More: **How to Auto Number Cells 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’**.

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`

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**

**The 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`

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.

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.

**Read More: **How to Autofill Numbers in Excel Without Dragging

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

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.

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

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

We can use the **Fill Handle** option to autofill a column while skipping rows at regular intervals. To be precise, you can also autofill if there are blank cells in the dataset. 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.

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.

**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 represent 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%`

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.

**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. If your double click feature not working you can enable it from the **option** feature.

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.

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

**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 the **Series **dialogue box again from the Fill options in the **Editing** group of commands.

➤ Select the **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**.

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.

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

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

**10. AutoFill Row Numbers with COUNTA Function to Ignore Blank Cells**

**The 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.

**📌**** 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.

**📌**** 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.

**Read More:** [Fix] Excel Fill Series Not Working

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

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.

**📌**** Step 1:**

➤ Select **Cell B5** and type:

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

➤ Use **Fill Handle** to autofill the entire column.

**📌**** 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.

**Read More:** How to AutoFill Numbers in Excel with Filter

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

**📌**** Step 2:**

➤ Select **Cell B5** and type:

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

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

**📌**** Step 3:**

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

➤ Select the **Insert **option.

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.

**Download Practice Workbook**

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

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