Weekly Computer Tip # 238
18 July 2008

Switch between displaying formulas and their values Excel 2000-2007)

As somebody who uses Microsoft Office all day long I sometimes find myself hunting for answers to my (and my course participants') burning questions. For a while my top 10 of tips was listed on my website only, but as course participants often told me that they came away with new tricks, tips and shortcuts that they never would have found on your own I decided to start writing weekly tips, offering free subscription. That was in April 2003. (No, this is not a typo!)

One of these old top 10 tips was how to quickly alternate between viewing the values and formulas in an Excel worksheet, which is particularly useful if it's somebody else's document or if I cannot remember what formulas I used. (These days that doesn't take much. ;-)

Until Microsoft released version 2007 you had to use a rather unintuitive keyboard shortcut (CTRL + ` the grave accent; on most keyboards this key is directly to the left of the "1" key). That shortcut still works (hence the fact that I put Excel 2000-2007 in today's title) but Microsoft has also turned it into a button, which will hopefully help you remember how to display the formula instead of the resulting value in each cell.

Here's how:

  1. Click on the Formulas tab.
  2. In the Formula Auditing group, click Show Formulas.

(Or press CTRL + ` if you are still using an older version of Excel. Or like the keyboard shortcut better!)

Once you've checked the formulas for errors and/or printed the worksheet showing all the formulas, simply click on the button once more to switch back to displaying the values of the formulas.

By the way, if you think you're going to use this button a lot, why not stick it on your Quick Access Toolbar. Simply right-click the button and select Add to Quick Access Toolbar from the shortcut menu. (See tip_218.php for more information.)