Getting Notification a Recalculation is Necessary

by Allen Wyatt
(last updated January 8, 2022)

9

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, Excel in Microsoft 365, and 2021.

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

Using Leaders with Tab Stops

Tab stops allow you to modify the horizontal position at which text is positioned on a line. Word allows you to preface ...

Discover More

Understanding Paragraph Alignment

One of the most basic ways to align paragraphs is to set the alignment used for the text in the paragraph. Word provides ...

Discover More

Understanding Functions

Do some macro programming in VBA and you'll quickly find out that you can use functions to extend the power and ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Inserting the User's Name in a Cell

Need to understand who is using a particular workbook? There are a number of ways you can find out, as discussed in this tip.

Discover More

Clearing Large Clipboard Entries

Need to clear out a large amount of information saved on the Clipboard? All you need to do is to replace it with a small ...

Discover More

Finding the Number of Significant Digits

When looking at a number, you may wonder how many significant digits it contains. The answer is not always an easy one, ...

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 9 - 3?

2022-04-02 15:08:19

J. Woolley

Mea culpa (again). I should have realized the standard Excel function =INFO("recalc") will also return the current calculation mode.


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.