Checking someone else's spreadsheet? Seeing every formula at once is the fastest way to spot a hard-typed number or a broken reference, and it's how the people who catch errors first actually do it.
Ctrl+`
Step by step
- Open the worksheet you want to check.
- Press Ctrl+` to display formulas in every cell.
- Review the formulas; columns widen so you can read them.
- Press Ctrl+` again to return to showing results.
Tips & troubleshooting
- On a UK keyboard the ` key is usually at the top left, below Esc and left of 1.
- To edit a single formula, select the cell and press F2.
What to look for when auditing
With formulas showing, a column that should be consistent becomes easy to scan. Look for:
- A plain number in the middle of a column of formulas. Someone has typed over a calculation.
- A formula that breaks the pattern, such as a SUM that stops a row short of its neighbours.
- References to other workbooks, shown as a file name in square brackets.
- Numbers typed inside formulas, such as *1.2, that ought to live in a labelled input cell.
Other ways to inspect formulas
- Formulas tab: the Show Formulas button does the same as the shortcut.
- F5, Special, Formulas: selects every formula cell on the sheet so you can shade them and see where the calculations are.
- Ctrl+[ jumps to the cells a formula refers to. Press F5 then Enter to come back.
- Trace Precedents on the Formulas tab (Alt, M, P) draws arrows from the cells feeding a formula. Alt, M, A, A removes them.
- =FORMULATEXT(A1) displays another cell's formula as text, useful for documenting a model beside the results.
Things that surprise people
- The setting applies to one worksheet at a time, and it is saved with the workbook. If a file opens showing formulas, someone left it on.
- Dates appear as serial numbers and columns double in width while formulas are showing. Both return to normal when you switch back.
- Printing while it's on prints the formulas, which is a handy way to produce an audit copy.
On a Mac
In Excel for Mac the shortcut is Control+` (grave accent).