How to Use Autofill Formula in Excel (6 Ways)

featured

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.

Step-1:

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.

creating table

Step-2:

In cell F4, apply this simple formula to get the total price of one item.

=C4*D4-C4*D4*E4

We have multiplied the Unit Price with the Qty and then subtracted the Discount from the value.

Press “Enter” to get the result.

fill command

Step-3:

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.

using fill command

After clicking on the Down option, Excel automatically filled the rest of the cells with the same formula. So we have got our result.

final 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

Step-1:

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 icon 1

applying formula

Step-2:

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.

getting result

ii. By Dragging the Autofill Handle

Step-1:

In this case, find out the result of the first cell of your column. Move your mouse to get the plus icon.

getting result

Step-2:

Now click the icon and drag it all the way to the end cell of your column.

 

final result

And then release the mouse key. The cells are auto-filled with the result value.

getting result

3. Using the Keyboard Shortcut

Step-1:

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.

final result

Step-2:

When all the required cells are selected, Press “CTRL+D” to apply the formula in those selected cells.

final result

You can also apply “CTRL+ENTER” to get the same result.

4. Using Array Formula

Step-1:

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

=C4:C15*D4:D15-C4:C15*D4:D15*E4:E15

applying formula

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.

getting result

5. By Creating a Table

You can also autofill formulas by creating a table. Follow these steps.

Step-1:

Select the data range that you want to convert into a table. Then go to “Insert” and click on “Table”

creating table

A new window appears. Check on “My table has headers” and click OK.

creating table

Step-2:

Now that the table is created, in cell F4, apply the same formula to get the first result.

creating table

Step-3:

Click on this icon icon 1 of the “Total Price” column and it will show the auto-filled cell values. Click on Select All to select them.

getting result

Click Ok to apply the condition. Our cells are auto-filled.

final result

6. By Copy-Pasting the Cell

Step-1:

The copy-paste method is very basic. To use this method, get the first result in cell F4.

getting result

Step-2:

Now copy the formula cell by pressing “CTRL+C” or by using the mouse. Then select the cells where you want to paste it.

final result

After the selection is done, just press “CTRL+V” to apply or use the mouse to paste the formula.

getting result

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”

option

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.

options

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

final selection

👉 The Array Formula is only available for Excel 365 version. If you are not using this version you cannot use this formula

Conclusion

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo