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:

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

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.

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:

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.

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

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.

Download the Practice Workbook

Download the workbook to practice.

Excel Auto Calculate: Knowledge Hub

• How to Turn on Auto Calculate in Excel
• How to Make Excel Auto Calculate Formulas
• Excel Spreadsheet Formulas Not Updating Automatically
• Excel Turn off Auto Calculate VBA

<< Go Back to How to Calculate in Excel | Learn Excel