How to Use Autofill Formula in Excel (6 Effective Ways)

This tutorial will demonstrate how to use the Autofill formula in Excel. When we are dealing with lots of data or cells in Excel, we often have to use the same formula in many cells. We can easily do this by using the Autofill formula in Excel. Moreover, in Excel, there are many useful shortcuts and attributes available to get the proper result. This not only saves our time but also helps us reduce the number of mistakes in the overall work. So, it is very important to learn how to use the Autofill formula in Excel.

How to Use Autofill Formula in Excel: 6 Effective Ways

In this section, you will find six ways to complete your task. We’ll use a sample dataset overview as an example in Excel to make it easy to understand. For instance, in the given worksheet, some “Items” are given in column B, and their “Unit Price” is available in column C, “Qty” in column D, and “Discount” in column E. If you follow the steps correctly, you should learn how to use the Autofill formula in Excel on your own. The steps are

1. Using the FILL Command for Excel Autofill Formula

In this first case, we want to use the FILL command for the Excel autofill formula. But for this at first, you have to determine a proper result and then use it further to all cells by using the FILL command in Excel. We will describe the steps of this method below.

First, in cell F5, insert the following formula.

=C5*D5-C5*D5*E5

So, we multiplied the Unit Price with the Qty and then subtracted the Discount from the value, and pressed “Enter” to get the result.

Next, we got the result of one cell. Now select the entire column “Total Price” and hit on the “Fill” option to click on the “Down” option similar to the below image.

After clicking on the Down option, the formula will automatically show the result for all the cells like the below image.

Thus, we have auto-filled the cells by using the Fill command in Excel.

Read More: Fix: Excel Autofill Not Working

2. Using the FILL HANDLE Option for Excel Autofill Formula

The FILL HANDLE method is one of the easiest ways to autofill formulas. You can do it by applying two approaches.

i. By Double-Clicking the Autofill Handle Icon

First, we want a similar result by using the same formula in cell F5 just like the first method. Then, try to move your mouse cursor to the bottom right corner of the formula cell.
After that, the cursor will show this plus icon .

Lastly, when you see this icon, double-click on icon and you will get the result you want.

So, we have auto-filled the cells by using double-clicking in Excel.

ii. By Dragging the Autofill Handle Icon

In this case, we have to get a similar result by using the same formula in cell F5 just like the first method.
Next, move your mouse to get the plus icon.
After that, press the icon and drag it all the way to the end cell of your column.

Thus, Excel will auto-fill the cells of the column with the result accordingly.

Therefore, we have auto-filled the cells by using the drag command in Excel.

3. Using the Keyboard Shortcut for Excel Autofill Formula

To apply this method, you have to follow the below steps.

Initially, we have to get a similar result by using the same formula in cell F5 just like the first method.
After that, select the formula cell and press the “SHIFT+Down Arrow Key (🔽)” key.

Furthermore, press “CTRL+D” to apply the formula to all the selected cells.

Moreover, you can also press “CTRL+ENTER” to get a similar result.

As a result, we have auto-filled the cells by using the keyboard shortcut command in Excel.

4. Using Array Formula

In Excel 365 there is one extra available for this case. We know it as an Array formula. This formula can work with all the cells at the same time. So, we can learn this method by following the below steps.

Initially, in cell F5 insert the following formula.

=C5:C11*D5:D11-C5:C11*D5:D11*E5:E11

After this, press the Enter button to apply this formula.
Finally, you will get your proper result similar to the below image.

Resultantly, we have auto-filled the cells by using the Array formula in Excel. In short, it can repeat any formula pattern in Excel.

5. By Creating a Table

You can also autofill formulas by creating a table by following the below steps.

First, select the data range>go to “Insert”>press on the “Table” option.

Then, a new window appears, named Create Table. Select the data range in the window and press OK.

Now, insert the same formula in cell F5 just like in the first method.

Next, press Enter to apply the condition, and you will get a proper result similar to the below image.

Hence, we have auto-filled the cells by using the Table command in Excel.

6. By Copy-Pasting the Cell

The copy-paste method is very basic. To use this method, follow the below steps.

First, we can use the same formula in cell F5, just like in the first method.

After that, copy the formula cell by pressing the “CTRL+C” options, and then select the cells where you want to paste it.

Lastly, just press “CTRL+V” to apply or use the mouse to paste the formula.

At the end of this article, we auto-fill the numbers in the cells by using the copy-paste command in Excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Using Autofill Formula.xlsx

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you learn how to use the Autofill formula in Excel. We will be glad to know if you can execute the task in any other way.

Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.

<< Go Back to Excel Autofill | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!