Excel Workbook Formulas Not Calculating: How to Fix Them

Your Excel file worked perfectly yesterday, but when you entered new data today, the totals didn’t update. Why did the formulas suddenly stop calculating, and how can you fix them? Here’s the most common reason for that problem, and a quick way to fix it. Also, see how to avoid that problem in the future.

For example, here’s a simple SUM formula to add up 7 numbers. Number 3 was changed from 10 to 5, but the total stayed at 70.

What happened to the formula? Why did it stop working?

formula stopped working
formula stopped working

Why Formulas Stop Calculating

In most cases, formulas stop calculating because the Excel calculation mode has switched to Manual, instead of Automatic.

To see which calculation option is active:

  • At the top of Excel, click the Formulas tab
  • At the far right, click on Calculation Options
  • The active setting has a check mark – Manual, in the screen shot below

To change the calculation mode, click on one of the calculation options

  • Automatic
  • Automatic Except for Data Tables
  • Manual
calculation options
calculation options

Why Did Calculation Switch to Manual?

How did the Excel calculation mode get switched to Manual in the first place? You always us the Automatic setting, and nobody else shares this workbook with you.

As my favourite television detective, Monk, would say, “Here’s what happened.”

  • You started Excel
  • The first workbook you opened had a Manual calculation setting.
    • Maybe a co-worker sent that file to you, and they like to work in Manual mode.
  • A few minutes later, you opened your other workbook, which you always use in Automatic mode
  • Excel ignored its Automatic setting, so you were still in Manual mode, without realizing it

And here’s the reason that Excel ignored your workbook’s calculation setting:

  • The Excel calculation mode is an application-level setting
  • All open workbooks have the same calculation mode
  • When you start a new Excel session, Excel takes its calculation setting from the first workbook you open
  • If you open more workbooks later, during the same session, Excel ignores their calculation settings

Video: Manual or Automatic Calculation

To see how a workbook can change to a different calculation mode, without you noticing, watch this short video.

Avoid the Manual Calculation Problem

To avoid creating calculation mode problems:

  • If you switch to Manual calculations during an Excel session, switch back to Automatic before closing and saving a workbook

To avoid inheriting calculation mode problems:

  • After you open the first workbook in an Excel session, check the calculation mode
  • If it’s not the calculation mode that you need, change it, before opening any other files

Quick Way to Check Calculation Mode

If you need to check calculation mode frequently, add the Automatic and Manual settings to your Quick Access Toolbar, or to a custom tab in the Excel Ribbon. There are details on the Excel Calculation Options page, on my Contextures site.

Here are 2 benefits from adding the Automatic and Manual commands to the Ribbon or QAT:

  • you can quickly see the current calculation mode
  • you can easily change the current calculation mode
calculation options on QAT and Ribbon
calculation options on QAT and Ribbon


Excel Workbook Formulas Not Calculating: How to Fix Them

Excel Workbook Formulas Not Calculating: How to Fix Them

Excel Workbook Formulas Not Calculating: How to Fix Them


Original source: https://contexturesblog.com/archives/2021/06/10/excel-workbook-formulas-not-calculating-how-to-fix-them/?utm_source=rss&utm_medium=rss&utm_campaign=excel-workbook-formulas-not-calculating-how-to-fix-them

Leave a Reply

Close Menu