Worksheet Map — ModelxcelPro docs

What it is

Worksheet Map opens (toggles) the Worksheet Map task pane for the active window. The ribbon button only shows or hides the pane — it runs no analysis on its own. Inside the pane you Analyze the active sheet, and the add-in produces two complementary views from a single pass:

A diagnostics tree — a navigable outline of everything the analysis found on that sheet: formula errors, row- and column-wise inconsistencies, partial aggregate ranges, distinct vs. copied formulas, and a complexity breakdown.
A rendered <source>_Map sheet — a same-workbook copy of the grid’s shape where each cell is colored by type and stamped with a small glyph (← ↑ ↖ for copied formulas, × for distinct ones).

Use it when you inherit a model and need to understand how a sheet is wired before you trust it — to find the cells someone hand-edited inside a copied row, the hardcoded numbers buried among formulas, and the genuinely complex calculations that deserve a closer read.

Where to find it

ModelXcel ProMapWorksheet MapKeytipWM

The button toggles the pane; it has no global keyboard shortcut of its own. Once the pane is open, three global shortcuts drive it: CtrlShiftM runs Analyze, CtrlShiftE runs Export Map, and CtrlShiftG performs the current navigate action (Go to Source / Back to Map / Open Map).

The task pane

A small toolbar runs across the top of the pane:

Button	What it does
Analyze	Full analysis of the active sheet, then (re)builds the diagnostics tree and the `<source>_Map` sheet.
Refresh	Identical to Analyze — both run the same full re-analysis. Use it after you edit the source sheet.
Export Map	Opens a sheet picker, then writes a standalone map workbook to disk (see Export Map below).
Settings	Opens Map Settings (analysis depth, colors, what to show). Applying re-analyzes.
Legend	Shows/hides the in-pane legend (hidden by default).

The diagnostics tree

The tree is a per-sheet diagnostics view, not a workbook browser — there is one root per analyzed sheet (🔬 Diagnostics for: <sheet>) and you do not pick other sheets from inside it. Under the root the analysis groups its findings into fixed sections:

Formula Errors: Cells evaluating to #DIV/0!, #REF!, #N/A, etc., grouped by error type.
Horizontal Inconsistencies: Cells that break a left-to-right run of copied formulas.
Vertical Inconsistencies: Cells that break a top-to-bottom run of copied formulas.
Partial Ranges: An aggregate (SUM, AVERAGE, COUNT…) that appears to stop short of the data block it should cover — split into Vertical and Horizontal.
Formulas: × Distinct Formulas (cells inconsistent with their neighbors) and ✓ Consistent Patterns (copied with the cell to the left, above, or both).
Complexity Analysis: High Complexity (score ≥ 7) and Medium Complexity (score 4–6).

Inconsistency and error sections expand by default; the Formulas and Complexity sections start collapsed. Node text carries live counts and color (errors red, horizontal orange, vertical blue, distinct/high-complexity red), and leaf nodes name the cell address, the formula, and a [Complexity: n/10] score. Very large sheets are capped per section (e.g. the first 1,000 leaves, top 20 consistent patterns) with an “… and N more” node so the tree stays responsive.

What “consistent” and “complexity” mean here

Consistency is decided by exact R1C1 comparison against the immediate left and above neighbors only. A formula filled across a row or down a column normalizes to the same R1C1 string as its neighbor and is reported as consistent; a cell someone hand-edited in the middle of that run no longer matches and is reported as distinct — the classic place a model breaks. Complexity is a 0–10 score from formula length, function and operator counts, and parenthesis nesting (a deeper score is used when Deep Analysis is on). The displayed bands are 1–3 Low, 4–6 Medium, 7–10 High.

Reading the findings

The point of the tree is to send you to the right cells in the right order. Three of the most common reads:

What you seeA cell marked × Distinct in the middle of an otherwise-uniform filled row.

What it meansSomeone hand-edited one cell in a copied run — the classic place a model breaks.

What to do nextSelect it and open TrustLens to judge whether the override is intentional.

What you seeA red node under Formula Errors.

What it meansCells evaluating to #DIV/0!, #REF!, #N/A and friends.

What to do nextGo to Source and fix the inputs or the formula at the root of the error.

What you seeA High Complexity (score ≥ 7) group.

What it meansLong, deeply-nested formulas that are hard to verify by eye.

What to do nextOpen each in Pretty Print (see CalcScope) and read it line by line.

Complexity filter

Right-click the tree → Filter by Complexity → Show All / High Complexity / Medium Complexity / Low Complexity. This rebuilds the tree in memory with no re-analysis and no workbook write. Note it only narrows the formula-derived sections (Formulas and Complexity Analysis); the error and inconsistency sections always show in full.

Peek actions & navigation

Select a node (or click a cell on a _Map sheet) to preview its source formula, then act on it from the buttons below the tree or the right-click menu:

TrustLens — opens TrustLens on the source cell to inspect, prove, and (optionally) edit the formula.
Pretty Print — opens the source formula in the Pretty Print viewer (see CalcScope).
Go to Source — selects the matching cell on the source sheet. The same button relabels itself Back to Map or Open Map depending on where your selection currently is, and Toggle Map / Source jumps between a cell and its counterpart on the _Map sheet.
Copy Cell Address / Copy Full Formula — clipboard helpers in the right-click menu.

All navigation only activates sheets and selects cells — it never writes to the workbook.

Analyze / Refresh: what it writes

Browsing the pane is read-only. Analyze and Refresh are not: they render the map, and in the pane’s default mode that means creating or rebuilding a <source>_Map worksheet in the current workbook.

The map sheet is named after the source sheet (truncated to fit Excel’s 31-character limit) with a _Map suffix. If a sheet of that name already exists it is deleted and recreated — so never hand-edit a _Map sheet; your changes are discarded on the next analyze.
The _Map sheet reproduces the source grid’s column widths and shape, then colors each cell by category (formula / text constant / numeric constant) and writes directional glyphs into formula cells (← copied-from-left, ↑ copied-from-above, ↖ both, × distinct). Your source sheets are not recolored; the map is a separate generated sheet.

Reading the map sheet

The map sheet uses the same key as the add-in’s Worksheet Map Legend: a fill color for each cell category, plus a glyph stamped into formula cells that shows how each one relates to its neighbors (the same R1C1 test the tree uses). The colors shown are the default Professional scheme.

Formula Cells: Cells containing formulas.
Consistent with the left: Formula copied from the cell to the left.
Consistent with left & above: Formula copied from the left and above.
Consistent with the above: Formula copied from the cell above.
Distinct Formula: Formula distinct from its neighbors — the hand-edited override in a filled block.
Text Constants: Cells with text values.
Numeric Constants: Cells with numeric values — the classic “hardcoded input” to check.
Errors: Cells with errors.
Arrays: Cells with legacy CSE array formulas.
Dynamic Arrays: Cells with spilling dynamic array formulas.

The legend lists every category for completeness. The in-place _Map render paints the formula, text-constant, and numeric-constant fills and writes the consistency glyphs; the error, array, and dynamic-array rows appear in the legend but aren’t separately colored on the in-workbook map.

The map sheet is a generated artifact — and it clears native undo

A <source>_Map sheet is rendered output, not a hand-maintained sheet. Because the render runs with Excel events and calculation suspended, the write clears Excel’s native undo stack — Ctrl+Z will not remove the map sheet. To remove it, just delete the sheet; it has no effect on your model and is regenerated the next time you analyze that sheet.

Export Map: a portable audit pack

Export Map builds a separate workbook and saves it to disk — it does not modify the source. After you pick which sheets to include, the export creates a new file named Worksheet Maps - <timestamp>.xlsx in the same folder as the source workbook, then offers Open file / Show in folder links. The workbook contains:

Map SummaryDashboardA “Worksheet Analysis Dashboard” with KPI cards, analytics, and an issue table aggregated across the mapped sheets.
Map LegendKeyThe symbol/color key and a complexity scale.
<source>One per source sheetA map sheet named after the source sheet, rendered the same way as the in-workbook map.
<source> DetailsOne per source sheetA flat table of every distinct formula cell (Cell, Formula, Complexity, Level, Category, Financial Functions, Pattern, Has Error), with formulas stored as text so they don’t recalculate in the export.

The exported workbook is tagged with an MxlSourceWorkbook custom document property recording the source file’s name, which lets the add-in resolve map cells back to the original workbook when the export is reopened (so TrustLens / Pretty Print / Go to Source still point at the real model).

Export Map is the right tool for an audit package, a model handoff, or a review binder: one self-contained file a reviewer can read without the live model, with the distinct-formula tables doing the work of a manual formula inventory.

Navigating the pack back to the live model

Because the pack remembers its source, a reviewer can jump from a map cell straight to the real cell — as long as the source workbook is also open and the Worksheet Map pane is visible in the pack’s window. With both in place, the add-in links the two workbooks and these shortcuts cross from the pack into the source:

From a mapped cell in the pack	Keyboard	Result
Go to the source cell	`Ctrl+Shift+G`	Activates the matching cell in the source workbook (the button reads “Go to Source”).
Inspect it in TrustLens	`Ctrl+Shift+I`	Opens TrustLens on the underlying source cell, not the map cell.
Read it in Classic Pretty Print	`Ctrl+Shift+Y`	Opens Pretty Print on the underlying source cell.

With the mouse, selecting a mapped cell previews its source target and enables the pane’s Go to Source button — it does not jump on its own. Clicking Go to Source, pressing Ctrl+Shift+G, or double‑clicking a node in the diagnostics tree performs the actual navigation. If the source workbook isn’t open, Go to Source tells you so rather than failing silently. The same navigation works the other way too — from a source cell, Ctrl+Shift+G reads Back to Map / Open Map. See Keyboard shortcuts → Global shortcuts for the full rules.

Export needs a saved, local source workbook

The export saves next to the source file, so the source must be saved somewhere on disk. A workbook opened from a OneDrive / SharePoint cloud URL must be synced locally first; a workbook opened from a generic http(s) URL, or never saved, is rejected with a clear message. Save a local copy and try again.

Settings

Settings opens a three-tab dialog (Analysis, Visualization, Performance). Highlights:

Analysis — which cell kinds to analyze (Formulas, Constants, Arrays, Named Ranges, Dynamic Arrays) and a Deep Analysis toggle that switches to the more detailed complexity scorer.
Visualization — Color Scheme (Professional, HighContrast, Monochrome), per-category color pickers, Show Legend, Group Identical Formulas, and a Show Formulas mode (Distinct only / Hide all / Show all) plus Show Values.
Performance — Use In-Memory Analysis and Include Data Validation.

Settings are held in memory for the session only — they are not saved to disk or the document, and reset to defaults when the add-in reloads. Click Save (or Apply) to keep them for the session; the pane then re-analyzes so the new options take effect.

Worked examples

Reviewing a large inherited financial model

Open the pane (WM) on the first calculation sheet and Analyze. Start at Formula Errors and Distinct Formulas — the cells most likely to be wrong. For each distinct cell, click TrustLens to read the formula and check whether the break is intentional. Move sheet by sheet (the analysis is per-sheet), re-running Analyze on each.

Finding dense formula areas to slow down on

After analyzing, open Complexity Analysis and read the High Complexity (score ≥ 7) group, or right-click → Filter by Complexity → High to strip the tree down to just those cells. These are the formulas worth a line-by-line read — open each in Pretty Print to see the structure.

Hunting the cell that broke a copied row

Expand Horizontal Inconsistencies (or Vertical). Each entry is a cell that breaks an otherwise-uniform run — the single hand-typed override inside a filled row. Go to Source jumps to it on the grid; Toggle Map / Source lets you see it in context on the colored map.

Exporting a map pack for auditors

Click Export Map, select the sheets a reviewer needs, and let it write Worksheet Maps - <timestamp>.xlsx. Hand over that one file: the auditor gets the Map Summary dashboard, the colored map of every sheet, and the per-sheet Details tables listing every distinct formula — without needing the live model.

Caveats & limitations

Single-sheet, not a dependency map. Worksheet Map analyzes one worksheet at a time from its used range. Despite the ribbon supertip’s wording, the analyze pipeline does not trace cross-sheet dependencies or follow external [file.xlsx] references, and it does not build a precedent graph. To flag cells that reference other sheets or workbooks, use the Risk Heatmap (its Structure and Audit modes mark cross-sheet and external references).
Consistency is neighbor-based. A cell is “distinct” only relative to its left/above neighbors’ R1C1; an intentional one-off in an otherwise uniform block is still flagged, and two unrelated identical formulas far apart are not compared.
Very wide sheets are bounded. The used-range column scan is capped (roughly 500 scanned / 1,000 columns), so columns far to the right of your data may be ignored.
The map legend lists categories the in-workbook map may not paint. The legend includes Errors/Arrays/Dynamic Arrays for completeness, but the in-place _Map render colors formula, text-constant, and numeric-constant cells and writes consistency glyphs — it does not separately color error or array cells.
Settings don’t persist. Map Settings live for the session only and reset when the add-in reloads.

Troubleshooting

“No Worksheet Selected.” Analyze needs a worksheet active (not a chart sheet). Select a sheet and retry.
Export says it needs a saved/local workbook. Save the source locally, or sync the OneDrive/SharePoint library to a local folder and reopen from there.
The _Map sheet keeps coming back / lost my edits. That sheet is regenerated on every analyze; treat it as disposable output and don’t edit it. Delete it any time.
The Map shortcuts do nothing. Ctrl+Shift+M / E / G act on an open Map pane. Open the pane (WM) first.

Risk HeatmapColor the live grid by risk/structure/audit across many sheets, including cross-sheet and external references.TrustLensInspect, prove, and edit a flagged formula — the main peek action from the tree.CalcScope & Pretty PrintRead a dense formula’s structure line by line.Map & visualizationHow Worksheet Map and Risk Heatmap compare.