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, often we 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 to reduce the number of mistakes in the overall work. So, it is very important to learn how to use the autofill formula in Excel.

Download Practice Workbook

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

6 Effective Methods to Use Excel Autofill Formula

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 understand easily. 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.

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 “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: How to AutoFill Cell Based on Another Cell in Excel

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 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 saw 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 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 with the result accordingly.

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

Read More: Drag Number Increase Not Working in Excel (A Solution with Easy Steps)

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.

Read More: How to Apply AutoFill Shortcut in Excel (7 Methods)

Similar Readings

• How to AutoFill Ascending Numbers in Excel (5 Quick Ways)
• How to Fill Down Blanks in Excel (4 Quick Methods)
• Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)
• AutoFill Not Incrementing in Excel? (3 Solutions)
• [Fix] Excel Fill Series Not Working (8 Causes with Solutions)

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.

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

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 the first method.

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

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

Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)

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 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-filled the cells by using the copy-paste command in Excel.

Read More: How to Auto-Populate Cells Based On Another Cell in Excel

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to 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. Follow the ExcelDemy website for more articles like this. 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.

Related Articles

• How to Use Custom Autofilter in Excel for More Than 2 Criteria
• How to Hide Filter Arrows from Pivot Table in Excel (3 Easy Ways)
• Filling a Certain Number of Rows in Excel Automatically (6 Methods)
• How to AutoFill Sequential Letters in Excel (5 Quick Ways)
• Excel VBA: Autofill Method of Range Class Failed (3 Solutions)
• Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows