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.