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.
How to Use Autofill Formula in Excel: 6 Effective Methods
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.
=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: How to Apply AutoFill Shortcut 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 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.
Similar Readings:
- How to Repeat Formula Pattern in Excel
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Fill Down Blanks 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 with the result accordingly.
Therefore, we have auto-filled the cells by using the drag command in Excel.
Read more: How to AutoFill Sequential Letters 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.
Similar Readings
- How to Auto Number Cells in Excel
- How to AutoFill Numbers in Excel
- How to Autofill Numbers in Excel without Dragging
- How to AutoFill Ascending Numbers in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Auto Number or Renumber after Filter 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.
Read More: How to Auto Generate Number Sequence 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.
Similar Readings
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- How to Autofill Dates in Excel
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Autofill Dates in Excel Without Dragging
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 Autofill Days of Week Based on Date in Excel
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
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.
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 AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
- How to Increment Month by 1 in Excel
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
- How to Auto Populate from Another Worksheet in Excel
- How to Perform Predictive Autofill in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
- [Fixed!] AutoFill Formula is Not Working in Excel Table
- How to Turn Off AutoFill in Excel
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- AutoFill Formula to Last Row with Excel VBA
- Drag Number Increase Not Working in Excel