Spotty Recalculation

Written by Allen Wyatt (last updated June 6, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


Guillermo has a large worksheet for pricing and costing products. The worksheet was calculating fine, then all of a sudden, some cells in the aggregate pages stop calculating. If he clicks in the cell and presses Enter the cell recalculates. Guillermo has checked all formats that the cells reference, and they are all set to accounting or number, plus auto-calculate is selected. He's at a loss as to what is causing the problem.

There are a couple of things that may be contributing to this issue. First, the culprit could be the layout of your worksheet. If the worksheets in your workbook are very large, it is important to remember the order in which Excel performs its calculations. You mention "aggregate pages," which implies that there are multiple worksheets at play here—even though you mention only a "large worksheet" at the beginning. Excel recalculates worksheets by trying to sequence all the calculations (building a calculation chain) so that cells which are dependent on other cells the least are stacked at the beginning of the sequence and cells that are dependent the most on other cells are positioned towards the end of the sequence. If Excel finds a cell is dependent on another cell lower down in the calculation chain, the sequence is rearranged to move that cell further down the chain.

Thus, if you have large worksheets with lots of calculations—particularly calculations that are dependent on values or results in other worksheets or workbooks—then it is possible that Excel is getting confused and is unable to come to a final, correct result for all calculations and the aggregate information may not get recalculated properly on the first go-around.

All of this being said, it should be pointed out that it is unlikely, in modern versions of Excel, that this is the case. Excel is much better in newer versions than it was a doing calculating in older versions of the program. So, the culprit may lie elsewhere.

Keeping that in mind, another thing you can try is to see if your errant formula cells really contain formulas. If they, instead, are parsed by Excel as text, then you'll obviously get wrong results. You can select the cell, press F2 (to enter editing mode), and then press Enter. If you have lots of such cells, try this:

  1. Press Ctrl+H. Excel displays the Replace tab of the Find and Replace dialog box.
  2. In the Find What box enter an equal sign (=).
  3. In the Replace With box enter an equal sign (=).
  4. Click Replace All.

A third thing you can check is whether your worksheets employ user-defined functions (UDFs). If the aggregate totals are dependent in some way on values returned by UDFs, then you'll want to make sure that the UDFs are returning the correct values. Sometimes they may not recalculate when you expect them to, so they might give (under some circumstances) incorrect results. If you believe this is the case, then modify the macro coding so that there is an Application.Volatile statement at the beginning of it.

And, speaking of macros, you might want to check to make sure that none of the macros are affecting the calculation process or turning off auto-calculate.

Another thing to check is whether a third-party add-in for Excel may be the culprit. One ExcelTips reader suggested that if numerous cells were using UDFs from add-ins, then Excel took much longer to recalculate. Pressing F9 may help speed up the recalculation process, but you may also want to start disabling some add-ins to see if the problem goes away.

Finally, you'll want to check if you have inadvertently created any circular references in your worksheet. These occur when a formula in one cell references, in some way, the cell in which the formula exists. Thus, if cell C1 contains a formula dependent on cells D7 and E7 and one of those cells contain a formula dependent on cell C1, then a circular reference exists. Unless circular references are handled properly (as described in other issues of ExcelTips), they can generate unexpected or faulty results. If there is a circular reference in the worksheet, there should be an indicator of such at the left side of the status bar.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13014) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Directories in a Macro

When a macro works with files, it often has to change between different directories on your disk drive. This is done ...

Discover More

Automatically Opening Macro Workbooks when Using a Shortcut Key

Click a button on a toolbar and Excel will go so far as to open a another workbook in order to run a macro associated ...

Discover More

Word Counts for a Group of Documents

Getting a word count for a single document is easy. Getting an aggregate word count for a large number of documents can ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Changing the Default Font

It makes sense that when Excel creates a blank workbook, it must figure out which font to use for that workbook. ...

Discover More

Creating New Windows

If you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your ...

Discover More

Saving Movement on Enter with a Workbook

Press Enter when working in a workbook and Excel moves to a cell adjacent to the one in which you were working. If you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 2?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.