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

Get FREE Advanced Excel Exercises with Solutions!

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 EIf 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: 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.

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

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

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.

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

## 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 Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  