Excel 2020: See All Formulas at Once
July 09, 2020 - by Bill Jelen
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.
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 .
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.
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.
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!~?.
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
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.