How to Find All Cells With Formulas in Excel

The "which cells are calculated and which are typed" problem

You inherited a model. Some cells are formulas. Some are hardcoded numbers a previous owner typed in. The model "ties out" but you don't know which is which. Trusting a hardcoded cell to recalc when assumptions change is the kind of thing that breaks audits.

TL;DR — Key takeaways

• F5 > Special > Formulas selects every formula cell on the active sheet.
• Ctrl + ` (backtick) toggles Show Formulas mode, displaying formulas instead of values.
• A VBA macro paints every formula cell light yellow, leaving hardcoded values uncolored.

Method 1: F5 > Special > Formulas

Excel's built-in.

1. F5 > Special > Formulas (check all 4 sub-types).
2. Click OK. Every formula cell on the active sheet is selected.

The selection is the answer. You can see at a glance which cells are formulas.

The catch: the selection disappears as soon as you click somewhere else. And it's only the active sheet, not the whole workbook.

Method 2: Show Formulas mode

Press Ctrl + ` (backtick) to toggle.

When on, every cell displays its formula instead of its value. Hardcoded values stay unchanged. Formulas show their formula text.

Pros: Every formula is visible at once.

Cons: You can't easily count or filter. The display is text, which makes the sheet look strange. And you can't run other operations while in Show Formulas mode.

Method 3: The free VBA macro

Download Highlight All Formula Cells. Free .xlsm with one macro.

1. Select the range to scan.
2. Alt + F8, pick the macro, click Run.
3. Every formula cell gets a light yellow fill with dark gold text. Hardcoded cells stay uncolored.
4. Hit Ctrl/Cmd + Z to revert when you're done auditing.

The colors are the audit signal. Walk the sheet, scan for yellow, then scan for the white cells in the middle of calc chains (those are the suspicious ones).

A common scenario: inherited model audit

You took over a financial model from a departing analyst. The model has 14 tabs. You don't know which cells are inputs, which are formulas, which are overrides.

1. On each tab, Ctrl+A twice, run Highlight All Formula Cells.
2. Walk each tab. Yellow cells are formulas. White cells in the input section are intentional inputs. White cells in the calc section are suspicious.
3. Investigate the suspicious whites. Convert intentional overrides to formulas, document the rest.
4. Hit Ctrl+Z to revert colors.

For a 14-tab inherited model, this audit pass takes about 15 minutes versus an hour of click-each-cell-and-look.

Frequently asked questions

How does it handle array formulas?

Both single-cell array formulas and multi-cell array formulas have HasFormula = True. Every cell of the array gets highlighted.

What about cells with the literal text "=A1+B1" (typed as text, not as formula)?

Treated as plain text, not highlighted. The macro checks HasFormula which is True only for real formulas.

Will it work on sheets with millions of cells?

Yes, but it'll be slower. For a sheet with 1M+ cells, expect a few seconds. The macro toggles ScreenUpdating = False to avoid flicker.

Will it overwrite existing colors?

Yes, on the highlighted cells. Ctrl+Z reverts. To preserve existing colors, work on a copy.

Can I customize the highlight color?

Edit the .bas source. The fill is RGB(255, 235, 156) and font is RGB(120, 80, 0).

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

After auditing formulas, common next step is finding errors with List Cells With Formula Errors. Or freezing the working state with Convert Formulas to Values.