How to Show All Formulas in Excel

Show Formulas is one of the fastest auditing moves in Excel. In one keystroke you switch from a sheet of numbers to a sheet of formula text, making hardcoded values, broken references, and inconsistent patterns visible without clicking on individual cells. It is the first thing most auditors do when opening an unfamiliar model.

The Ctrl + ` shortcut

The fastest method: press Ctrl + \`` (the grave accent key, to the left of 1on most keyboards, sharing the key with the tilde~`).

• First press: every cell on the active sheet displays its formula instead of its result.
• Second press: returns to normal value view.

This is a per-sheet toggle. Enabling it on Sheet1 does not affect Sheet2.

Show Formulas via the ribbon

For those who prefer the ribbon:

1. Click the Formulas tab.
2. In the Formula Auditing group, click Show Formulas.
3. The button is highlighted when the mode is active. Click it again to deactivate.

Both methods are identical in effect. Use whichever is faster for your workflow.

What to look for in formula view

Hardcoded values. In formula view, a cell containing a bare number like 2500000 is immediately obvious in a column where every other cell shows a formula. Hardcodes embedded inside formulas, like =A1*1.05 where 1.05 is an undocumented assumption, are harder to spot but still visible once you read the formula text.

Inconsistent formulas. Read down each column. In a well-built financial model, formulas in the same column typically follow the same pattern, with the row reference incrementing: =B5*C5, =B6*C6, =B7*C7. A cell showing =B5*C6 or =B7*D7 breaks the pattern and is worth investigating.

Row and column breaks. A formula that sums C5:C10 in a row where all other formulas sum C5:C11 has either a missing or extra cell in its range, a common source of off-by-one errors.

Formula vs value columns. Some columns should be entirely formulas; others should be entirely hardcoded values (input assumptions). In formula view, you can quickly verify that no formula has crept into an input column and no hardcode has replaced a formula in a calculation column.

Printing formulas

To produce a printed record of the formulas in a model:

1. Toggle Show Formulas on (`Ctrl + “).
2. Press Ctrl + P to open the Print dialog.
3. In Page Setup, consider Landscape orientation and Fit Sheet on One Page (or Fit All Columns on One Page) to manage the wider column display.
4. Print or export to PDF.

A printed formula audit trail is sometimes required for regulatory review or peer review in investment banking and audit contexts.

Why raw formula view doesn’t scale on large models

Show Formulas works well for a single sheet with a manageable number of columns. For a model with 20+ sheets and hundreds of formulas per sheet, reading formula text cell by cell becomes impractical:

• Columns expand significantly, forcing horizontal scrolling.
• Formula text varies in length, making visual patterns harder to detect.
• Cross-sheet references (=Sheet3!B14) require navigating to that sheet to evaluate the source.
• There is no at-a-glance summary of which cells are formulas vs hardcodes across the whole workbook.

Seeing structure at a glance with Formula Audit XL

Formula Audit XL‘s Cell Map (Ctrl+Shift+M) takes the Show Formulas idea further. Instead of displaying raw formula text, it colours every cell by type: unique formula, copied-across/down, input, hardcoded number, reference to another sheet or workbook, text, or empty. You see the structure of the whole sheet at once, and odd cells stand out by colour. To pinpoint a single overtyped cell that breaks a row’s pattern, the Inconsistent Formulas check (Ctrl+Shift+I) lists them directly. The colours are temporary and cleared in one click.

Common pitfalls

• Forgetting to turn it off before sharing. If you save and send a file while Show Formulas is active, the recipient will open it in formula view. Always toggle it off before distributing.
• Confusing with a frozen/broken view. Occasionally a user accidentally triggers Show Formulas and cannot find the toggle. `Ctrl + “ is the fastest fix.
• Not covering all sheets. Because Show Formulas is per-sheet, a quick audit of a multi-sheet model requires visiting every sheet. It is easy to miss sheets in the middle of a large workbook.

Related guides

• Inconsistent Formulas in Excel: Find and Fix
• Excel Formula Auditing: The Complete Guide
• How to Find Errors in Excel Spreadsheets
• How to Audit a Financial Model in Excel