Use Manual Calculations in Excel? This New Feature Helps You Find Stale Values

If you’ve got a big, complex spreadsheet with lots of formulas, it can be slow to run. In those situations, turning off calculations can be a life saver. But the downside of doing so, is that you might forget that those calculations haven’t been updated. Relying on stale values can be risky and lead to poor decision making and analysis.

Thankfully, there’s a new feature in Excel that now helps you find and identify those values easily.

Finding stale values

For this example, I’m going to use a simple table. It shows product IDs, prices, quantities, and total sales.

Excel table with price, quantity, and total sales.

The only calculation that happens here is in the total sales column, where price is multiplied by quantity. If the calculations are on, changing either the price or quantity fields will change the value in the total sales field automatically. But if I turn on Manual Calculations, then the calculation won’t happen until I either set the calculations to Automatic, or to manually force calculations (e.g. by pressing F9).

To turn off calculations in Excel, go to the Formulas tab and select Calculations Options, where you’ll see the following options:

Setting the calculation options in Excel.

The one danger is that if you set your calculations to Manual, it will change the setting for all the workbooks you currently have open. This change isn’t just set to one sheet or workbook.

In the above screenshot, the calculations are set to Manual. And if you’ve updated to the latest version of Excel, you’ll see the option at the bottom: Format Stale Values. If you check this off, you will now see different formatting for calculations that Excel hasn’t updated.

After checking that off and making changes to some of the quantities in my table, some of the values in the total sales column haven’t updated. And it’s easy to see which ones those are:

There are now strikethroughs showing for the values which aren’t updated. This tells you that those values are no longer accurate. As you can see from the value of $172.50 where the corresponding quantity is 50 and the price is $5.75, the total sales based on that calculation should be $287.50. Without applying the formatting for stale values, it would be difficult to notice that the value of $172.50 is incorrect.

Once the values are recalculated, either by manually triggering them (F9) or by changing them back to automatic, then the strikethrough goes away. And that’s because the value has also been updated:

Excel table after updating calculations.

If you never turn your calculations off and set them to manual, you’ll never need to use this feature of stale formatting. But if you do occasionally turn off calculations, then it can be valuable to you as it can help you avoid errors and making incorrect decisions based on outdated information.

If you don’t see this option available yet then it may not be available on your version of Excel. You need Microsoft/Office 365 and for the latest beta updates to be installed. Eventually, however, it will be rolled out to all 365 users.. But if you want new features as soon as they are available, be sure to sign up for the (free) Office Insiders Program to ensure that you get them earlier than the general rollout.

If you liked this post on How to Find Stale Values in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Comments are closed.