MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: See All Formulas at Once


July 08, 2019 - by Bill Jelen

Excel See All Formulas at Once. Photo Credit: Roberto Nickson at Unsplash.com

You inherit a spreadsheet from a former co-worker and you need to figure out how the calculations work. You could visit each cell, one at a time, and look at the formula in the formula bar. Or you could quickly toggle between pressing F2 and Esc to see the formula right in the cell.

A spreadsheet has a mix of text and numbers.

But there is a faster way. On most U.S. keyboards, just below the Esc key is a key with two accent characters: the tilde from Spanish and the grave accent from French. It is an odd key. I don’t know how I would ever use this key to actually type piñata or frère .


Just under the Escape key, there is a key with a Tilde and a Grave Accent. Hold down Ctrl and press this key to toggle into Show Formulas Mode.

If you hold down Ctrl and this key, you toggle into something called Show Formulas mode. Each column gets wider, and you see all of the formulas.

In show formulas mode, the columns become slightly wider and you see the formulas in each cell instead of the values.

This gives you a view of all the formulas at once. It is great for spotting “plug” numbers (B9) or when someone added the totals with a calculator and typed the number instead of using =SUM(). You can see that the co-worker left RANDBETWEEN functions in this model.

Note

Here is another use for the Tilde key. Say you need to use the Find dialog to search for a wildcard character (such as the * in "Wal*Mart" or the ? in "Hey!?" Precede the wildcard with a tilde. Search for Wal~*Mart or Hey!~?.

Tip

To type a lowercase n with a tilde above, hold down Alt while pressing 164 on the number keypad. Then release Alt.

Title Photo: Roberto Nickson at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.