Getting Notification a Recalculation is Necessary

by Allen Wyatt
(last updated January 8, 2022)

8

Deanna has some very large workbooks that she uses at her office. She often turns off automatic recalculation because, well, she gets tired of waiting for the recalculation to finish every time she changes a value in a cell. The problem is that Deanna often forgets that she's turned off automatic recalculation, and that can come back to bite her when she erroneously believes that a worksheet has been recalculated. Deanna wonders if there is a way to visually indicate that a worksheet needs recalculation or is waiting to be manually recalculated.

This may be simpler than Deanna may realize. First of all, to adjust how Excel performs its calculations, follow these steps:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and later versions display the File tab of the ribbon and then click Options.)
  2. At the left side of the dialog box, click Formulas. (See Figure 1.)
  3. Figure 1. The Formulas options of the Excel Options dialog box.

  4. Using the options under Workbook Calculation, choose how you want your workbook to be calculated.
  5. Click OK.

If you click Manual (step 3), make sure you leave the Recalculate Workbook Before Saving option selected, as that will allow Excel to update the formulas and cells at least once each session.

If you choose to do manual recalculations (as Deanna has), there is a subtle change in Excel's user interface, and it is easy to miss. Whenever you make a change to the workbook, and that change would otherwise require a recalculation, Excel places a message near the left side of the status bar. That message? "Calculate." Yep, Excel visually lets you know that a calculation is necessary. In fact, you can click on that message, and that will trigger a recalculation.

If a more obvious notification is necessary, you will need to rely on a couple of event handlers to do the task. Right-click on a worksheet tab, choose View Code, and place these in the resulting Code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A1").Interior.Color = vbRed
End Sub
Private Sub Worksheet_Calculate()
    Range("A1").Interior.Color = vbGreen
End Sub

These two event handlers update the color of cell A1 as an indicator of whether the worksheet needs to be recalculated. The first sets the color to red anytime there is a change anywhere in the worksheet. The second sets the color to green whenever a recalculation occurs.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12807) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 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

Displaying the Print Dialog Box in a Macro

Want to print a document by using a macro? One way is to display the Print dialog box and allow the user to interact with ...

Discover More

Problems with Custom Views

If you get an error when you try to use one of your custom views, it could be due to the protection you have applied to ...

Discover More

Shortcut for Show/Hide

Hate to take your hands off the keyboard? Here's a handy keyboard shortcut you can use to display (or not display) the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Controlling Automatic Backups

Excel can make backups whenever you save your workbook. If you want to turn the feature on or off, this tip explains how ...

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

Seeing Excel's Program Window

Have you ever opened Excel to find that the window you saw yesterday is not the same as it is today? Sometimes, for ...

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 8 + 2?

2022-01-11 15:49:26

J. Woolley

My Excel Toolbox now includes a stable CalcMode([Visible]) function that uses a cell's comment/flag and color to indicate the current calculation mode. (An earlier version was described below.)
See https://sites.google.com/view/MyExcelToolbox/


2022-01-10 10:29:14

J. Woolley

@Mike D.
If a cell in your worksheet (perhaps A1) has a volatile formula like =NOW(), Excel will recognize that cell needs to be updated and manual recalculation will not be ignored; therefore, the Worksheet_Calculate event will fire and make the marker green.


2022-01-10 08:06:31

Mike D.

Love this, however:
This only seems to work if I change something that needs to be calculated.
When I change anything it turns red but only turns green if I have changed something in a formula or data that is referenced.
If I add anything other than a formula and then recalculate A1 stays red.


2022-01-09 11:57:44

J. Woolley

Mea culpa. The CalcMode() function described below has been removed from My Excel Toolbox because it was unstable.
Function CalcModeToggleLink([Friendly_Name],[Screen_Tip]) and macro ToggleCalcMode both remain in My Excel Toolbox.
See https://sites.google.com/view/MyExcelToolbox/


2022-01-09 10:50:47

J. Woolley

By the way, there is a drawback to use of the CalcMode() function described below. When mode is Manual, Application.CalculationState will always be xlPending and the StatusBar will always indicate "Calculate" even after pressing F9 to recalculate. This is the result of a delayed action trick that allows CalcMode() to return the correct calculation mode; it reconfirms its cell's formula after each calculation (like F2+Esc), which Excel interprets as a change to that cell.


2022-01-08 10:31:01

J. Woolley

My Excel Toolbox includes the CalcMode() function that returns the current calculation mode (Automatic, Semiautomatic, or Manual). If the mode is changed to Manual, recalculation (F9) is necessary to update the CalcMode() function; update is immediate when mode is changed to Automatic or Semiautomatic.
My Excel Toolbox also includes the ToggleCalcMode macro (Ctrl+Shift+T) to switch from Automatic (or Semiautomatic) to Manual and from Manual to Automatic. The macro always updates CalcMode().
Finally, the CalcModeToggleLink([Friendly_Name],[Screen_Tip]) function uses SuperLink to create a hyperlink that runs ToggleCalcMode when activated. Optional Friendly_Name will be displayed; default is "Toggle Calculation Mode". The CalcMode() function can be part of Friendly_Name or Screen_Tip, BUT NOT BOTH. Here are cell formula examples:
=CalcModeToggleLink("Toggle "&CalcMode()&" Calculation")
=CalcModeToggleLink(CalcMode(),"Toggle Calculation Mode")
=CalcModeToggleLink(,CalcMode())
See https://sites.google.com/view/MyExcelToolbox/


2022-01-08 10:27:11

J. Woolley

If you decide to add VBA event handlers, you might include this in your ThisWorkbook module to insure the workbook is recalculated each time it is saved (when calculation mode is manual):

Private Sub Workbook_Open()
    Application.CalculateBeforeSave = True
End Sub


2022-01-08 07:30:46

Rene

One additional tip: shift+F9 recalculates a single worksheet, F9 recalculates the whole spreadsheet. Handy in case a full recalculate takes too long.


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.