One of the fundamental features of Microsoft Excel is we can build formulas and functions and then see the results in our worksheet. But sometimes it would be helpful to see what’s behind these results. And we don’t want to have to move to every single cell in a worksheet to look at the calculations especially when the worksheet might be complex with hundreds or thousands of rows. And this can be especially important when we receive data from a colleague, a vendor, a client, and we want to be able to double-check or audit the logic to make sure that we’re getting the correct calculations for the results that we’re looking for in the worksheet. To make sure you have accurate calculations or logic in place, use the Excel Show Formulas feature (see video).
You’ll find Show Formulas under the Formulas tab in the Formula Auditing group. Now, we can turn this on or off by simply using Show Formulas.
Show Formulas Keyboard Shortcut
You’ll see there’s also a keyboard shortcut for Show Formulas, which is [Ctrl] + the grave accent (`). This is the key that is just to the left of the number 1 at the top of your keyboard. It’s on the same key as the tilde (~) which is the horizontal squiggle line. So, we could use the keyboard shortcut as well as a toggle to turn Show Formulas on or off.
The Show Formulas feature is also one way for us to make sure that we don’t have missing calculations, perhaps where someone put in the direct entry of a value, rather than having a calculation that will update as data changes. We can print out these results, the display of the actual formulas, and then toggle it back at any time. Show Formulas should be one of the tools in your auditing tool chest for Microsoft Excel.
By Dawn Bjork, MCT, MOSM, CVP, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Virtual Presenter