Colour-Code Formula Cells and Hardcoded Values in Excel

A pricing tab: formulas blue, the markup assumption amber

A simple pricing sheet where unit cost is typed and the sale price is calculated. The highlighter paints the typed cost amber and the calculation blue — so the one assumption in the column stands out instantly.

Input cells:
  A2  Cost          (text label)   -> amber
  B2  12.50         (typed number)  -> amber
  C2  =B2*1.4       (formula)       -> blue
  C3  =B3*1.4       (formula)       -> blue

Downloaded formula-highlighted.xlsx:
  the 1.4 markup is buried INSIDE the formula, not in a cell,
  so it does not show as amber. See the next example for why
  that matters.

Spotting a hardcode hiding inside a formula column

The classic audit failure: someone overwrote one cell of a formula column with a typed number to 'make the total work'. Every other cell in the column is blue; the overwritten one is amber. That single amber cell in a sea of blue is the bug.

Column C should be all formulas:
  C10 =SUM(C2:C9)   -> blue
  C11 =SUM(D2:D9)   -> blue
  C12 9999          -> AMBER  <- typed override, the bug
  C13 =SUM(F2:F9)   -> blue

Result: one amber cell stands out in the blue column.
This is exactly what manual auditing misses.

Why a copied markup constant stays invisible

The highlighter colours cells, not the literals inside formulas. A magic number baked into a formula (here 1.4) is not flagged, because the cell is a formula and gets the blue treatment. To surface in-formula constants, break them out into their own input cell first — then they become amber.

Before (constant hidden in formula):
  C2 =B2*1.4   -> blue (the 1.4 is not visible as a hardcode)

Better modelling, then re-run highlighter:
  E1 1.4          -> amber (now an explicit assumption)
  C2 =B2*$E$1     -> blue

The assumption is now an amber input cell you can audit.

Reading the counts to judge a model

After running, the tool reports formula and hardcoded cell counts. A healthy calculation-heavy model has far more blue than amber outside the input block. A model that is mostly amber is mostly typed numbers — fragile and hard to maintain.

Findings after run:
  formulaCells:  842
  hardcodedCells: 5,310

Read: 5,310 typed cells vs 842 formulas. If most of those
amber cells are calculations that SHOULD be formulas, the
model is a manual spreadsheet pretending to be a model.

Auditing every sheet of a multi-tab workbook at once

The highlighter loops all worksheets. A workbook with Inputs, P&L, Balance Sheet, and Cash Flow tabs is fully coloured in one run — you do not toggle anything per sheet the way Ctrl+~ forces you to.

Workbook: 4 sheets
  Inputs        -> mostly amber (it is the assumptions tab)
  P&L           -> mostly blue with amber drivers
  Balance Sheet -> mostly blue
  Cash Flow     -> mostly blue

One run colours all four. Open each tab and the picture is
immediate: amber should cluster on Inputs, not scatter
across the calculation tabs.

When a workbook uses Excel's shared-formula optimisation, only the master cell carries a formula string; the follower cells carry a sharedFormula reference to the master. The classifier tests for a formula key, which followers do not expose via cell.value, so those cells fall through to amber. Modern Excel often writes each formula in full (so this rarely bites), but files saved by LibreOffice or older Excel may show a block of formulas where the first cell is blue and the rest are amber. Treat a blue-then-amber run in a calculation block as a shared-formula artefact, not a hardcode.

A cell holding a hyperlink is stored as { text, hyperlink } — an object without a formula key — so it is treated as a hardcoded value and filled amber. This is correct: a hyperlink is not a calculation. If your sheet uses =HYPERLINK(...) instead of a clicked-in link, that IS a formula and colours blue.

A typed 0 is a non-empty value, so it is filled amber. A cell containing only a space character is also a non-empty string and gets amber. Only cells that are genuinely empty (skipped by ExcelJS includeEmpty: false) are left uncoloured. If you see stray amber in apparently blank areas, those cells contain spaces or zeros.

A cell like =A1/A2 that evaluates to #DIV/0! still has a formula key in cell.value (the error is in result), so it is classified as a formula and filled blue. To find error cells specifically rather than colour them, use the Error Locator, which reports #REF!, #N/A, #DIV/0! and the rest with cell addresses.

The highlighter reads each cell's stored formula/value, not its conditional-formatting rules. A cell that looks coloured in Excel because of a conditional-formatting rule is still classified purely on whether it contains a formula. The new amber/blue fills are applied as direct cell fills and will sit underneath any conditional-formatting rules Excel evaluates on open.

Every non-empty cell receives either the blue or amber fill, replacing whatever fill it had. If your model already uses colour conventions (e.g. grey section headers), those fills are lost in the highlighted copy. Run the highlighter on a throwaway copy when you only need it for a one-off audit, and keep your styled master separate.

The processor throws "Formula Highlighter requires Pro tier." for non-Pro accounts. This is enforced before any processing happens. Upgrade to Pro (50 MB / 100,000 rows) or higher to use it.

Only the .xlsx family is accepted. A CSV is plain text with no formula layer — there are no formula cells to colour, so the tool does not accept CSV. If your data lives in CSV, there is nothing for this tool to do; the formula/value distinction only exists inside a spreadsheet.

Because ExcelJS loads the whole workbook into memory and rewrites it, very large files near the tier ceiling (up to 500 MB on Developer) can be slow and memory-heavy in the browser. The reported durationMs will reflect this. If a huge workbook stalls, split the analysis to the sheets you actually need to audit.