Decode and Explain Formulas in Inherited Excel Financial Models

IFERROR wrapping a cross-sheet VLOOKUP

The single most common 'inherited model' shape: a lookup against an assumptions tab, wrapped so a missing key shows blank rather than #N/A. The cross-sheet absolute range parses cleanly.

Paste:
=IFERROR(VLOOKUP(A1,Assumptions!$A$1:$D$50,3,0),"N/A")

Breakdown:
IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
  expression: VLOOKUP() — Searches for a value in the first column of a range and returns a value from another column
    lookup value: cell reference A1
    table range: cell range Assumptions!$A$1:$D$50
    column index: number 3
    exact-match flag: number 0
  fallback if error: text "N/A"

A multi-condition SUMIFS roll-up

Multi-criteria aggregation is everywhere in models. Note the labelling quirk: only the first range/criteria pair gets named labels; further pairs appear as arg 4, arg 5. The structure is still fully shown.

Paste:
=SUMIFS(Detail!D:D,Detail!A:A,">100",Detail!B:B,"East")

Breakdown:
SUMIFS() — Adds values in a range that meet multiple conditions
  sum range: Detail!D:D
  criteria range 1: Detail!A:A
  criterion 1: text ">100"
  arg 4: Detail!B:B
  arg 5: text "East"

(Sheet-qualified WHOLE-COLUMN ranges like Detail!D:D show without
the 'cell range' prefix — the parser tokenises them as raw text.
Use a row-bounded range like Detail!$D$2:$D$999 to get 'cell range'.)

A SUMPRODUCT conditional sum

SUMPRODUCT is the analyst's swiss-army knife and the most-inherited 'array' pattern. It is not on the description list, so it shows as a generic function — but the conditional factors inside are parsed in full so you can read the logic.

Paste:
=SUMPRODUCT((A2:A100=D2)*(B2:B100>0)*C2:C100)

Breakdown:
SUMPRODUCT() — Excel function
  arg 1: multiplication (*)
    left:  multiplication (*)
      left:  equality test (=)
        left:  cell range A2:A100
        right: cell reference D2
      right: greater-than test (>)
        left:  cell range B2:B100
        right: number 0
    right: cell range C2:C100

A LET-based calculation

Modern models use LET to name intermediate results. The tool describes LET, but it has no per-argument labels, so the name/value pairs show as sequential arg N and the names render as raw identifiers — read them in pairs.

Paste:
=LET(rev,B2,cost,C2,rev-cost)

Breakdown:
LET() — Assigns names to calculation results for use within a formula
  arg 1: rev
  arg 2: cell reference B2
  arg 3: cost
  arg 4: cell reference C2
  arg 5: subtraction (-)
    left:  rev
    right: cost

A quoted sheet name that breaks parsing

Finance tabs are often named with spaces and ampersands ('P&L 2026'). The apostrophe-quoted reference is not tokenised, so the formula dumps as a raw line. The fix is to rename the tab or strip the qualifier before pasting.

Paste:
='P&L 2026'!B12*1.05

Breakdown:
'P&L 2026'!B12*1.05

(The apostrophe is unrecognised, so no tree is built. Rename the
sheet to 'PL2026' — then 'PL2026!B12*1.05' parses normally.)

The Explainer is gated to Pro and above. On Free it returns Formula Explainer requires Pro tier. with no partial output. There is no smaller free preview — the whole tool is behind the gate. Upgrade to Pro (or higher) to use it.

SUMIFS and COUNTIFS only carry named labels for the first range/criteria pair. A model formula with three or four conditions shows the first pair as criteria range 1: / criterion 1: and the rest as arg 4:, arg 5:, arg 6:. The arguments are all present and correctly nested — just count the positions: in SUMIFS the pattern is sum range, then range/criterion pairs.

LET and LAMBDA are described but have no per-argument labels, so their name→value pairs render as sequential arg N and the name tokens appear as raw identifiers rather than being tied to their values. Read LET arguments in pairs (name, value, name, value, …, final expression) to reconstruct the bindings.

Any function that is not one of the 65 named ones — a valid-but-unlisted Excel function like SUMPRODUCT, OFFSET, or INDIRECT, a Google Sheets-only function like QUERY or ARRAYFORMULA, or a custom workbook UDF like MyMacro — renders as NAME() — Excel function (uppercased) with generic arg N labels. It is never mislabelled User-defined function and never throws; you still see the full nesting structure and every argument, just without a prose description of that one function.

A reference like 'Sales 2026'!A1 uses an apostrophe, which the tokeniser does not recognise. From that apostrophe onward the rest of the formula is absorbed as one raw chunk: if the quoted ref is at the very start, the whole formula dumps as a single raw line; if it sits inside an argument, the surrounding functions parse but that argument swallows everything after the apostrophe — so any later arguments are dropped from the tree. Workaround: rename the sheet so it has no spaces (then Sales2026!A1 parses), or strip the '…'! qualifier before pasting if you only need the function structure.

Excel table syntax is not fully tokenised. Table1[Amount] keeps Table1 as a plain cell reference but drops the [Amount] column part. A reference that starts with a bracket, like [@Price], starts with an unrecognised character, so it and the rest of the formula dump as one raw line. Replace structured references with the equivalent A1 ranges before pasting if you need them in the tree.

The Explainer is static analysis: it never evaluates the formula and never reads cell values, so it cannot tell you what =VLOOKUP(...) returns for your data — only what each part does and how the parts nest. To see a value, run the formula in Excel; to understand it, use this. For tracing which cells feed a result with real values, use the dependency map.

The breakdown comes from a deterministic recursive-descent parser, not a language model, so an identical formula always produces an identical tree. There is no temperature, no sampling, and no chance of an AI confidently inventing a function or mis-describing an argument — the descriptions are fixed text keyed to each function name.

If the formula box is empty or only whitespace, the tool returns No formula provided. rather than an empty tree. Paste the real formula text. A leading = is optional — it is added automatically if you leave it off.