How to Make Excel to Auto Calculate Formulas (4 Simple Methods)

In this article, we describe 4 simple methods to auto-calculate formulas in a workbook.

Suppose we have a dataset of some Products, Sales of January, and Sales in February.

Let’s calculate the Total Sales for January and February.

If we now drag down the Fill Handle to fill the cells below with the same formula, instead of seeing different results for each row as expected, the same value appears. This is due to the Automatic Calculation setting not being enabled.

There are various ways we can make Excel auto-calculate formulas in situations like these.

Method 1 – Using Excel Options to Enable Automatic Calculation

The surest way is to enable the option in the Excel Options.

Steps:

Go to the File tab.

Choose Options from the left pane.

In the new window that opens, select Formulas and then Automatic.
Click OK to continue.

Now, if we use the Fill Handle to copy the same formula again, the formulas will auto-calculate as expected.

Method 2 – Enabling Auto Calculation of Formulas from the Formulas Tab

We can alternatively enable Automatic Calculation from the Formulas tab.

Steps:

Go to the Formulas tab.
Select Automatic from the Calculation Options.

Automatic calculation of formulas is re-enabled.

Method 3 – Using the Calculate Now Option to Auto Calculate Formulas

The Calculate Now option allows for auto-calculation of formulas on demand, without the need to switch from Manual Calculation mode.

Steps:

Select the output range (F5:F12).
Click the Calculate Now icon on the Formulas tab to get an immediate result.

Here is the output.

Method 4 – Using a Keyboard Shortcut to Auto Calculate Formulas

Step 1:

Select the output range (F5:F12).
Press the F9 key on the keyboard.

The calculation mode is changed to Automatic, and the formulas in the selected range are recalculated.

In some cases, you might need to change the cell value, but the output in the final output column doesn’t show the changed value. In such cases, press F9 to refresh the dataset.