# How to make Excel Auto Calculate (6 Easy Methods)

The dataset, Sales of Products, has four columns: Product Name, Unit Price, Quantity, and Total Price.

• To calculate the total price for Mobile, enter the following formula in cell E5:
`=C5*D5`

• Go to cell E5 and use Fill Handle to AutoFill data in range E6:E14. The same value will be displayed in all those cells.

A change in the cell value does not change the value in the final formula output.

### Method 1 â€“ Using the Excel Options Feature

Steps:

• Go to the FileÂ tab.

• Select Options.

• The 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 the total price for Mobile in cell E5.
• Select cell E5 and use Fill Handle to AutoFill data in range E6:E14.

### Method 2 â€“ Enabling Calculations Options to Automatic

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

• Calculate the 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.

### Method 3 â€“ Using the Calculate Now OptionÂ

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

Steps:

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

The values in the range E5:E14 have been updated.

### Method 4 â€“ Applying the Keyboard Shortcut for Auto Calculation

Steps:

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

It will update the results in the 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 dependent on other cells. Then, it recalculates all formulas in all open workbooks, even the ones that have not been changed.

### Method 5 â€“ Using an Excel Table

Steps:

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

• Calculate the 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 update.

### Method 6 â€“ Applying VBA CodeÂ

Steps:

• Go to the Developer tab >> Visual Basic.

• Select Insert >> Module.

• Paste the following code in your VBA Macro Editor:

``````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``````
• Press the Run button or F5 key to run the code.

You will see the output in the 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 the Excel Options dialog box.
• Go 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 the Excel Options dialog box.
• Go 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-calculation.

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

## Excel Auto Calculate: Knowledge Hub

<< Go Back to How to Calculate in Excel

Get FREE Advanced Excel Exercises with Solutions!
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