While working with Excel worksheets you may have to use formulas for your data and you may wish to repeat the results in other cells. This can be done without inserting your formula in every cell manually. Excel includes some handy time-saving features to auto-fill your formula. Today in this article, we will discuss some easy ways to autofill your formula in Excel.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Autofill Formula in Excel (6 Ways)
In this section, you will find six ways to complete your task.
1. Using the FILL Command
Consider a situation where you are working in a supermarket. A datasheet has been assigned to you where you need to use the autofill formula feature to get the result. The FILL command can help you out in this situation.
In the given worksheet, some “Items” are given along with their “Unit Price”, “Qty”, and “Discount”. You need to find the “Total Price” of all the items using the FILL command.
In cell F4, apply this simple formula to get the total price of one item.
We have multiplied the Unit Price with the Qty and then subtracted the Discount from the value.
Press “Enter” to get the result.
We got the result of one cell. Now select the entire column “Total Price” and click on “Fill” then click on “Down” as we want to apply the formula in the Downward direction.
After clicking on the Down option, Excel automatically filled the rest of the cells with the same formula. So we have got our result.
2. Using the FILL HANDLE Option
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
We will use the same datasheet. Now find out the first result by using the same formula in cell F4. Move your mouse cursor to the bottom right corner of the formula cell until the cursor show this plus icon
Now when you saw this icon, double click on the icon and Excel will autofill the results in the cells to the end of the data row.
ii. By Dragging the Autofill Handle
In this case, find out the result of the first cell of your column. Move your mouse to get the plus icon.
Now click the icon and drag it all the way to the end cell of your column.
And then release the mouse key. The cells are auto-filled with the result value.
3. Using the Keyboard Shortcut
To apply this method, first, apply the formula in cell F4. Select the formula cell and press the “SHIFT+Down Arrow Key (🔽)” key to select the rest of the cells.
When all the required cells are selected, Press “CTRL+D” to apply the formula in those selected cells.
You can also apply “CTRL+ENTER” to get the same result.
4. Using Array Formula
If you are a user of Excel 365 you can easily use the array formula to get the job done. Now in cell F4 apply the array formula. The final formula is
This Array formula would return 12 values in the cell (one cell each). But since we are using dynamic arrays, the result would not be confined to the single-cell and would fill up the entire column.
5. By Creating a Table
You can also autofill formulas by creating a table. Follow these steps.
Select the data range that you want to convert into a table. Then go to “Insert” and click on “Table”
A new window appears. Check on “My table has headers” and click OK.
Now that the table is created, in cell F4, apply the same formula to get the first result.
Click on this icon of the “Total Price” column and it will show the auto-filled cell values. Click on Select All to select them.
Click Ok to apply the condition. Our cells are auto-filled.
6. By Copy-Pasting the Cell
The copy-paste method is very basic. To use this method, get the first result in cell F4.
Now copy the formula cell by pressing “CTRL+C” or by using the mouse. Then select the cells where you want to paste it.
After the selection is done, just press “CTRL+V” to apply or use the mouse to paste the formula.
Things to Remember
👉 There may be a situation when you need to activate the FILL HANDLE feature. To do this, Go to “Files” and then select “Options”
A new window appears. Click on Advanced and in the Editing Options, Check on “Enable Fill Handle and Cell Drag-and-Drop”. Click Ok to apply.
👉While using the autofill features, remember to activate automatic calculation. To do this, Go to File then click on Option. When the new window arrives. Select Formulas and in the Calculation Option, choose Automatic. Click Ok to continue.
👉 The Array Formula is only available for Excel 365 version. If you are not using this version you cannot use this formula
Today we discussed six easy ways to autofill your cells with formulas in Excel. We tried to cover most of the solutions but of course, there are other solutions than these six ways. If you have any confusion or suggestion regarding this article you are always welcome to comment and share.