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:
Figure 1. The Formulas options of the Excel Options dialog box.
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:
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.
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!
When you are working in a worksheet, you may want to freeze the rows at the top or left of the worksheet. Excel provides ...
Discover MoreExcel has a wide variety of keyboard shortcuts that can help make it easier to use the program. When one of those ...
Discover MoreExcel helpfully lets you know when the data or formulas you've entered in a cell don't make sense. It does this by ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments