Spotty Recalculation

by Allen Wyatt
(last updated September 20, 2017)

3

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. Worksheets are recalculated from front to back and from left to right and top to bottom within each worksheet.

Thus, if you have large worksheets with lots of dependent calculations, and the calculations on which everything is dependent are recalculated after the dependent calculations, you might get erroneous results. For instance, if your aggregate information is near the top of a worksheet or near the beginning of a workbook and that information is dependent on cells that are calculated further down or on later worksheets, then the aggregate information may not get recalculated properly on the first go-around.

You can find out if this is the case by simply reorganizing your workbooks so that the aggregate information is physically after the cells on which it depends. That way it will be recalculated after the cells on which it depends are calculated.

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.

Finally, you'll want to check if you have inadvertently created any circular referenes 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 descrived 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, and 2013.

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

Excel Won't Display Different Windows in the Taskbar

If you want individual Taskbar buttons for each open Excel workbook but cannot seem to get those buttons, the culprit could ...

Discover More

Referencing a Worksheet Name

Excel provides ways to reference the column or row number of a cell, but it doesn't provide a built-in way to reference a ...

Discover More

Word Won't Maximize

Sometimes, for whatever reason, your computer might not display Word or other programs properly. There are a few things to ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Excluding a Specific Add-In at Startup

Got an add-in that you don't want loaded each time that Excel starts up? Here are a few ways that you can exclude it.

Discover More

Selecting Multiple Cells by Mistake

Click on a cell and you expect the single cell to be selected. If you instead get a group of cells, it can be frustrating to ...

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

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}] 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 two less than 2?

2017-09-20 06:43:54

Thierry

Hi Allen,

We have had a similar experience like Guillermo some years ago on some spreadsheets. The main culprit turned out to be a third party add-in for Excel. If there were lots of cells using UDF's from the add-ins, workload on the machine became exorbitant. At best, recalculation the monster workbook took 5-10 minutes, often it simply got stuck.
F9 (and patience) often helped to get further.
So, maybe taking a look at eventual add-ins could be a good advice?


2017-09-20 05:53:42

Mark

Allen,

Your description of how Excel calculates workbook is overly simplistic and I think outdated. Excel doesn't use the front-to-back, L-R ,top-bottom calculation model and if it did it hasn't since before Excel 2000.

Excel builds a dependency tree which then is used to construct a calculation chain and finally uses this to drive the order of recalculation.

Here's a link to Microsoft's article about this (2007 version): https://msdn.microsoft.com/en-us/library/bb687891(v=office.12).aspx

That article mentions that Excel provides good tools to identify and correct circular references and unless Guillermo has ignored them and turned them off that doesn't seem like to be the cause.

Based on that article I think your suggestions that the issue could be from a macro turning off recalculation seems like the most likely cause of the issues.


2014-03-19 16:01:31

mike

Couldn't F9 be used?


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.