# Excel Auto Calculate (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we have talked about how to make Excel to auto calculate formulas. We have discussed 6 easy methods to do so. We have also explored ways to enable iterative calculation. Lastly, we have talked about how to set calculation precision.

The importance of the Excel auto calculate feature is to enable Excel to update formulas and recalculate values in cells whenever changes are made to the dataset and corresponding formulas. It simplifies the process of updating and modifying large datasets.

## What is Auto Calculation in Excel?

Auto calculation in Excel is the ability of Excel to populate cells with formulas and update results automatically when a component or data connected to the formula changes. Excel comes with built-in functionalities that enable auto calculation. Auto calculation saves a lot of time and eliminates the need for manual calculation. It ensures accuracy in calculations for large datasets.

## How to Make Excel to Auto Calculate Formulas

We have a dataset of some products, their unit prices and quantities.

You can calculate the total price for mobile by using the following formula in cell E5.

`=C5*D5`

Now, if you go to cell E5 and use Fill Handle to AutoFill data in range E6:E14, you will see the same value in all those cells.

Also, you may see that, a change in the cell value does not change the value in the final formula output.

These scenarios can happen when the Excel auto calculate feature is not enabled or not working properly.

### 1. Use of Excel Options Feature

• Go to the FileÂ tab.

• Select Options.

• Excel Options dialog box will appear.
• Go to the FormulasÂ tab.
• Look for the Calculation optionsÂ section.
• Click on Automatic under the Workbook Calculation header and press OK.

• Calculate total price for mobile in cell E5.
• Select cell E5 and use Fill Handle to AutoFill data in range E6:E14.

### 2. Enabling Calculations Options to Automatic

• Go to the FormulasÂ tab.
• Click on Calculation Options.
• Select Automatic.

• Calculate total price for mobile in cell E5.
• Select cell E5 and use Fill Handle to AutoFill data in range E6:E14.

Note:

You can also use the keyboard buttons Alt + M + X + A to apply this method.

### 3. Using Calculate Now Option of Excel

Suppose, you have calculated the total price in range E5:E14 by using the previous method but the values are the same in all of the cells.

• In this situation, select range E5:E14.
• Go to the FormulasÂ tab.
• Click on Calculate Now from the CalculationÂ group.

• You will see the accurately updated values in range E5:E14.

### 4. Apply Keyboard Shortcut in Excel for Auto Calculation

• If the values are the same in all the cells of range E5:E14 after calculation, press F9 button from the keyboard.

• It will update the results in range E5:E14.

Note:

F9 â€“ It recalculates formulas in all open workbooks if the formulas or cell values have been changed since the last calculation.

Shift + F9 â€“ Updates changed formulas in the active worksheet only.

Ctrl + Alt + F9 â€“ Recalculates all formulas in all open workbooks, even the ones that have not been changed.

Ctrl + Shift + Alt + F9 â€“ Initially, it checks formulas that are dependent on other cells. Then it recalculates all formulas in all open workbooks, even the ones that have not been changed.

### 5. Auto Calculate Using Excel Table

• Select any cell of the data set.
• Press Ctrl + T.
• Create Table dialog box will open.
• Check the My table has headersÂ option.
• Press OK.

• Calculate total price for mobile in cell E5 by using this formula.
`=[@[Unit Price]]*[@Quantity]`
• The rest of the cells in range E5:E14 will automatically populate.

### 6. Apply VBA Code to Auto Calculate

• Go to the Developer tab >> Visual Basic.

• Select Insert >> Module.

• Paste the following code in your VBA Macro Editor.
• Press the Run button or F5 key to run the code.

``````Sub Auto_Calculate()
Â Â Â  'variable declaration
Â Â Â  Dim WS As Worksheet
Â Â Â  Dim myRng As Range
Â Â Â  'set variables
Â Â Â  Set WS = ActiveSheet
Â Â Â  Set myRng = WS.Range("B5:E14")
Â Â Â  'auto calculate formula in a range of cells
Â Â Â  For i = 1 To myRng.Rows.Count
Â Â Â Â Â Â Â  myRng.Cells(i, 4) = myRng.Cells(i, 2) * myRng.Cells(i, 3)
Â Â Â  Next i
End Sub``````
• You will see the output in range E5:E14.

Note:

``````For i = 1 To myRng.Rows.Count
Â Â Â Â Â Â Â  myRng.Cells(i, 4) = myRng.Cells(i, 2) * myRng.Cells(i, 3)
Next i``````

This portion of the VBA code loops through each row in a range called myRng and multiplies the values in the second column (Unit Price) and third column (Quantity) of each row. The product is then assigned to the corresponding cell in the fourth column (Total Price) of the same row.

## How to Enable Iterative Calculation in Excel

• Follow Method 1 and open Excel Options dialog box.
• Navigate to the FormulasÂ tab.
• Under the Calculation Options section, check the Enable Iterative calculationÂ option.
• Press OK to save changes.

## How to Set Calculation Precision in Excel

• Follow Method 1 and open Excel Options dialog box.
• Navigate to the Advanced tab.
• Look for the When calculating this workbookÂ section.
• Check the Set precision as displayedÂ option.
• Click OK to save changes.

## Things to Remember

There are a few things to remember while performing Excel auto calculate.

• Press the F9 key to refresh the worksheet.
• Be careful with circular cell references.
• Ensure that formulas are correctly inserted in cells.

1. Can I disable the auto calculate feature in Excel?

Yes, you can disable the auto calculate feature in Excel following the same procedure of enabling it.

2. How can I force Excel to recalculate my formulas?

You can force Excel to recalculate formulas either by pressing the F9 key or using the Calculate Now option under the Formulas tab.

3. Why are my formulas not updating automatically in Excel?

First, check if the Automatic calculation mode is selected. Additionally, ensure that the formulas have correct cell references. Circular references or errors in formulas can also resist automatic updates.

## Conclusion

In this article, we have discussed how to make Excel auto calculate in detail. We have demonstrated 6 easy methods. If you have any questions regarding this essay, donâ€™t hesitate to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.

## Excel Auto Calculate: Knowledge Hub

<< Go Back to How to Calculate in Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF