Getting Notification a Recalculation is Necessary

Written by Allen Wyatt (last updated January 8, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


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.


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


Correcting Student Papers

If you are a teacher, you may be looking for ways you can use Word's features to correct papers your students send to you ...

Discover More

Limiting Entries to Numeric Values

When creating a worksheet, you may need to limit what can be entered into a particular cell. Using data validation you ...

Discover More

Two Page Numbers per Physical Page

Want to save paper when printing your document? Just print two pages per sheet of paper and you'll get rid of only half ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Forcing Stubborn Recalculation

Have you ever recalculated a worksheet, only to notice that not everything calculated as it should? Here's a way you can ...

Discover More

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

Generating Random Testing Data

Need to test your formulas? Then you need some testing data that you can use to see if the formulas function as you ...

Discover More

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

View most recent newsletter.


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

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

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.

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")

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


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

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.