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

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

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

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

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

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.

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

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

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.

**Similar Readings:**

**How to AutoFill Cell Based on Another Cell in Excel (5 Methods)****Fill Down to Last Row with Data in Excel (3 Quick Methods)**

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

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

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.

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

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

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

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

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

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