Highlight All Formula Cells

What it does

When you inherit a workbook, you don't know which cells are formulas and which are hardcoded numbers a previous owner typed in. That distinction is the difference between "this model recalculates" and "someone overrode the formula and now the model is lying". The macro walks your selection and paints every cell containing a formula light yellow with dark gold text, leaving hardcoded values untouched. After a single pass you can see at a glance the calculated cells and the cells that need scrutiny. Then revert the colors with Ctrl+Z when you're done auditing.

Real-world example

Picture a finance director at a private equity portfolio company two weeks after she joined. Her predecessor handed off a 14-tab quarterly forecast model and resigned in the same email. The model "ties out" to the historical numbers, meaning when she punches in actuals it returns answers that match the audit. Great. The hard part: she has no idea whether each cell is a formula she should trust to recalculate when assumptions change, or a hardcoded number her predecessor typed in to make Q3 line up with the budget after a midnight emergency.

Her current method: click each cell one at a time and watch the formula bar. The forecast tab alone has about 600 cells. At 5 seconds per cell, that's an hour of clicking, and her eyes glaze over after the first 200 cells. So she checks the "important" cells (the ones near the totals) and trusts the rest. Two weeks later her CEO finds a hardcoded number in the cost-of-capital row and asks her why the cost of capital didn't update when interest rates moved.

With this macro she selects the entire forecast block, runs the tool, and 540 formula cells turn light yellow in 2 seconds. The 60 cells that remain white are the ones that should be inputs (assumptions, scenario toggles) plus, suspiciously, eight cells in the middle of the calculation chain that are clearly-not-inputs but were typed in. She investigates those eight, finds two genuine overrides her predecessor made and forgot to undo, and fixes them. Total elapsed: about 12 minutes versus the hour-plus of cell-by-cell clicking.

On a 600-cell model audit pass, expect about 50 minutes saved. The bigger win is finding the cells you'd have missed scrolling past them visually because they look identical to formula cells.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range you want to audit. This can be a single column, a block, or a whole tab via Ctrl+A.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick HighlightFormulaCells, click Run.
3. The macro paints every formula cell light yellow with dark gold text. The popup reports how many cells got highlighted.
4. When you're done auditing, hit Ctrl/Cmd + Z to revert all the colors at once.

FAQ

Will it overwrite existing cell colors?

Yes, in the cells it highlights. If you have existing color-coding (red flags, green confirmations) and you highlight a region that includes those cells, the formula cells in that region get painted yellow regardless of their prior color. Ctrl+Z reverts everything in one step. If you need to preserve existing colors, work on a copy of the file or revert immediately after auditing.

How does it handle array formulas and dynamic arrays?

Array formulas (entered with Ctrl+Shift+Enter) and dynamic array formulas (the modern XLOOKUP/FILTER variety) both have HasFormula = True for every cell in the array. So every cell in the array gets highlighted, which is the right behavior for an audit: each cell is computed, not hardcoded.

What about cells that just look like formulas because they start with =?

A real formula cell has HasFormula = True. A cell containing the literal text =A1+B1 (not as a formula, just as text) has HasFormula = False and is skipped. So the highlight is accurate even on weird sheets where someone has typed formula-looking text into cells.

Can I customize the highlight color?

Not from the macro UI, but the source code is plain VBA in the repo. The colors are set as RGB(255, 235, 156) for fill and RGB(120, 80, 0) for text. Edit those numbers in the .bas file, re-import, and you're done. The macro is also short enough that you could clone it as HighlightHardcodedCells and invert the test (If Not cell.HasFormula) to highlight inputs instead.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running reverts every painted cell back to its prior color. That's actually how most people use this macro: paint, audit, revert, fix, repeat. The macro runs entirely offline. Your data never leaves your machine.