Risk Heatmap

Color the live grid to see where risk and inconsistency concentrate. The heatmap is a lens you toggle on and off across one sheet or many — Risk for complexity, Structure for the full cell taxonomy, Audit for problems only — backed by a legend panel that counts every category, scores the model, and walks you through the flagged cells.

What it is

Risk Heatmap toggles an in-grid color lens over the active sheet (click Risk Heatmap again, or press Ctrl+Shift+R, to remove it). It classifies every cell into a detailed taxonomy — complexity-scored formulas, constants, errors, copied vs. distinct formulas, external and cross-sheet references, array and volatile formulas — and fills each cell with a color for its category. A side legend panel turns that into something you can work with: per-category counts and percentages, a 0–100 health score, and, in Audit mode, buttons to jump through the flagged cells.

It is built for fast triage: open a model you don’t know, color it, and let the concentration of red and orange tell you which sheet and which corner to investigate first.

Where to find it

ModelXcel ProMapRisk HeatmapKeytipRHShortcutCtrl+Shift+R

It is a split button: the main face toggles the heatmap on the active sheet, and the dropdown arrow opens a menu of view modes, apply scope, colors, and the legend panel. One thing to know up front: the ribbon button does not show a pressed/on state, so it won’t visually tell you the heatmap is currently applied — the colored grid and the legend panel are your cues.

View modes

The dropdown offers three modes. Every cell is classified the same way each time; the mode decides which categories actually get colored.

Risk ViewFormula cells only, by complexity: blue low → yellow moderate → orange high, plus red for errors. Constants, blanks, and references stay uncolored. Use it to see where the heavy, error-prone calculations live.
Structure ViewThe full taxonomy — every cell type: constants, the three risk levels, errors, the consistency family, external-workbook and cross-sheet references, and array / dynamic-array / volatile formulas. Use it to understand how the whole sheet is built.
Audit ViewProblems only: errors, distinct formulas, high-risk formulas, external and cross-sheet references, volatile functions, and hardcoded numbers. Everything “normal” stays uncolored so the issues pop. Use it to build a punch list.

Switching mode re-colors immediately — you don’t need to toggle off first, and choosing a mode while the heatmap is off turns it on in that mode. (Under the hood, Audit is analyzed like Structure so consistency is detected, then narrowed to just the problem categories.)

The cell taxonomy

The legend and colors are driven by a 16-way classification. These are the categories and the colors the Professional preset paints them (the default; other presets recolor the same categories):

Text Constant
A literal text value, no formula.
Numeric Constant
A hardcoded number — in a formula area, the classic “hardcoded input” audit flag.
Low Risk Formula
A formula with a low complexity score.
Medium Risk Formula
A formula with a moderate complexity score.
High Risk Formula
A formula with a high complexity score.
Formula Error
Evaluates to an Excel error (#DIV/0!, #REF!, #N/A, #VALUE!, #SPILL!…).
Consistent (Left)
R1C1 matches the left neighbor — copied/filled.
Consistent (Above)
R1C1 matches the cell above — copied/filled.
Consistent (Both)
Matches both neighbors — the healthy filled case.
Distinct Formula
Inconsistent with its neighbors — the hand-edited override in a filled block.
External Workbook
References another file ([Book.xlsx]).
Cross-Sheet Ref
References another worksheet (Sheet1!A1).
Array Formula
A legacy CSE array formula ({=…}).
Dynamic Array
A modern spill formula (FILTER, SORT, UNIQUE…).
Volatile Function
NOW, TODAY, RAND, OFFSET, INDIRECT — recalculates on every change.

Reading the heatmap

What you seeA dense patch of orange and red in Risk View.
What it meansComplex, error-prone formulas cluster there.
What to do nextDrill into that area with Worksheet Map, or open TrustLens on the cells.
What you seeA high count on the External Workbook or Cross-Sheet Ref legend card.
What it meansThe model leans on other files or sheets — a fragility and update risk.
What to do nextClick the card to select those cells and confirm every link is intended.
What you seeNumeric Constant cells flagged inside a formula area (Audit View).
What it meansHardcoded inputs are sitting among calculations.
What to do nextClick the Numeric Constant card to select them, then decide which should be driver cells.

Apply scope

From the dropdown, choose how wide the lens reaches:

  • Risk Heatmap (main button) — the active sheet only.
  • Apply to All Sheets — every visible worksheet (hidden and very-hidden sheets are skipped).
  • Select Sheets… — opens a picker so you can choose exactly which sheets to color.

For multi-sheet runs (or a single sheet with more than ~50,000 used cells) a progress dialog appears with per-sheet status and a Cancel button that stops safely after the current step.

Customize colors

Customize Colors… opens a settings dialog with a Color Preset chooser and a per-category color table:

  • Presets: Professional (default), HighContrast (accessible primaries), and PrintFriendly (desaturated, ink-light hues that survive monochrome printing).
  • Per-category colors: every category has its own picker and hex readout; categories not used by the current view mode are shown greyed for context.

There is no “reset” button — to return to a preset’s defaults, simply re-select that preset, which repopulates every color. Your custom colors are stored as overrides on top of the chosen preset and reapplied when you re-color.

The legend panel

Show Legend Panel toggles a side task pane that is the real cockpit for the heatmap. It has four parts:

  • Header — the current mode name (Risk / Structure / Audit View) and a health score: a number drawn inside a color-banded ring (green at 80+, amber at 60+, orange at 40+, red below). It shows — until you apply the heatmap.
  • Legend — one card per category that the active mode colors, showing the swatch, the category name, and a count line like 1,234 cells, 12.3% (the percentage is of all cells). Each card has a visibility checkbox to show or hide that category, and clicking a card selects the cells of that category on the grid.
  • Summary — workbook-wide totals: Total, Formulas, Constants, Empty.
  • Review issues (Audit mode only) — ◀ Prev / Next ▶ buttons that step through the flagged cells, with an Issue n of N counter. The buttons select and scroll to each cell so you can walk a model end to end.

The health score is a heuristic

The legend pane scores the model on a 0–100 scale and draws it inside a color-banded ring. The bands:

80–100 · Good (relative)
Few formulas flagged relative to the formula count.
60–79 · Watch
Some flagged formulas; worth a scan.
40–59 · Concern
Flags are concentrated here.
0–39 · Heavily flagged
Many flags relative to the formula count.

A penalty indicator, not a grade

The 0–100 score is a hand-tuned penalty heuristic: each sheet starts at 100 and loses points per flagged formula — errors penalized most, then distinct formulas, then high-risk formulas, external-workbook and cross-sheet references, and volatile functions — normalized by the formula count, and the workbook score is the formula-weighted average. It is a quick relative signal of “how much is flagged here,” not a measure of correctness. A model can score 100 and still be wrong, or score low and be perfectly intentional. For actual verification, use Proof & Trust.

How the colors are applied, saved, and cleaned up

The heatmap colors are real cell interior fills — not conditional formatting and not a non-destructive overlay. Before it paints, the add-in snapshots each affected cell’s original fill so it can restore it exactly. Because the colors are genuine formatting writes, two things follow:

  • Applying the heatmap clears Excel’s native undo. You remove the lens by toggling it off (which restores your snapshotted fills), not with Ctrl+Z.
  • The colors must be kept out of the saved file — which the add-in handles for you.

On save: the add-in strips the heatmap (restores your original fills) before the file is written, then reapplies it after the save completes — so the on-disk workbook never contains the heatmap colors, but your screen does. On close: it strips the colors so they don’t persist.

If it can’t neutralize the lens, it cancels the save or close

The add-in will not risk baking heatmap colors into your file. If stripping the lens fails for any reason, it cancels the save (or close) and warns you rather than writing colored cells to disk. If you see that warning, toggle the heatmap off manually (which restores your fills) and try again, or reopen Excel as the message suggests.

Lens state — the view mode, color preset, selected sheets, category visibility, and a last-applied timestamp — is stored as workbook custom document properties (chunked across names beginning MxlRiskHeatmap_) so the lens configuration can be restored. Toggling the heatmap off restores your fills, clears that state, and verifies the cleanup; if cleanup can’t be verified it keeps the state and warns you rather than silently leaving colors behind. (The emptied property names may remain in the file as blank metadata.)

One lens at a time

Risk Heatmap is one of three mutually-exclusive in-grid highlight lenses, alongside the Audit group’s issue and audit-status highlights. A single coordinator enforces that only one is active at a time. If you turn on the heatmap while another lens is painted, it asks you to confirm the switch — on yes, it cleanly removes the other lens (restoring those fills) before applying the heatmap; on no, nothing changes. Rapid double-clicks during a switch are ignored rather than stacking up.

Worked examples

Finding hardcoded values in a formula area

Switch to Audit View and apply. Numeric constants are flagged, so a typed number sitting among formulas stands out. Open the legend panel, click the Numeric Constant card to select them all, and review whether each should be a driver cell or a formula.

Finding inconsistent formulas

In Audit View, use the Review issues Next ▶ button to step through the distinct formulas — the cells that break a copied run. Each stop selects the cell so you can judge whether the override is intentional, and open TrustLens to inspect it.

Scanning for external links and volatile formulas

Use Structure or Audit View and read the External Workbook, Cross-Sheet Ref, and Volatile Function legend cards. The counts tell you how exposed the model is to other files and how much recalculates on every edit; click a card to find and review those cells.

Preparing a model review across the workbook

Apply to All Sheets (or Select Sheets…) in Audit View, then use the legend’s health score and category counts to rank where to focus. Walk the flagged cells with Prev/Next. When you’re done, toggle the heatmap off. Saving strips the lens from the file write and reapplies it on screen, so the saved workbook stays clean but the live view remains active until you turn it off or close.

Caveats & limitations

  • Heuristic, not proof. The Risk view and the health score flag where to look; they are not a correctness check. Verify with Proof & Trust.
  • The colors are real fills. They clear native undo and must be neutralized before save/close — which the add-in does, but if neutralization fails the save/close is cancelled. Don’t deliberately keep the lens on and save a copy expecting colored output; the heatmap is a view, not a formatting tool.
  • Issue navigation visits distinct formulas only. Prev/Next steps through inconsistent-formula cells — not every category the Audit view flags (errors, externals, volatiles, hardcoded numbers are shown but not part of the walk). Use the legend cards to select those.
  • Mode and scope live on the ribbon. There is no view-mode switcher inside the legend panel; change modes and scope from the split-button dropdown.
  • No on/off indicator on the ribbon. The button doesn’t show a pressed state; rely on the grid and legend pane to know whether the lens is applied.

Troubleshooting

  • “Could not safely neutralize the active highlight layer” on save/close. The lens couldn’t be stripped, so the operation was cancelled to protect your file. Toggle the heatmap off and retry, or reopen Excel as the message suggests.
  • Switching to the heatmap prompts about another highlight. Only one in-grid lens runs at a time. Confirm to replace the Audit/Issue highlight, or cancel to keep it.
  • “No cells matched the current Risk Heatmap view.” Nothing on the sheet falls into the active mode’s categories (e.g. Risk view on a sheet with no formulas). Try Structure view or another sheet.
  • The button doesn’t look toggled even though colors are showing. Expected — the split button has no pressed state. Click it (or Ctrl+Shift+R) to toggle off.

Related

Worksheet MapRender a sheet’s structure as a map and export it, instead of coloring the live grid.TrustLensInspect and prove a flagged formula cell.Proof & TrustTurn “this looks risky” into actual verification.Map & visualizationHow Risk Heatmap and Worksheet Map compare.