Flatten Names — ModelxcelPro docs

The scenario

You’re about to send a model to a reviewer, a counterparty, or a system that doesn’t handle defined names well. The formulas read in names — =Revenue-Costs, =Income*TaxRate — which is great for you but opaque to anyone without your Name Manager, and risky for tools that resolve names differently. Flatten Names rewrites those formulas into explicit coordinates so each one stands on its own.

What it does

For every formula in scope, Flatten Names finds the tokens that are defined names and replaces each one with the reference it resolves to. The rewrite is precise about form:

References are absolute. A name is always written back dollar-signed — $A$1 for a single cell, $A$1:$B$10 for a range. There is no relative-reference option; the output is anchored so it survives being moved or pasted.
Cross-sheet names become sheet-qualified. If the name lives on a different sheet than the formula, the reference is qualified with the sheet name — Sheet2!$A$1. Sheet names that need it are quoted: a sheet called Forecast 2026 becomes 'Forecast 2026'!$A$1 (and an apostrophe inside a sheet name is doubled).
Constant names become literals. A name that holds a value rather than a range — for example TaxRate defined as =0.21 — is replaced by its literal value. It is wrapped in parentheses only when it needs them for precedence (see below).
Everything else is left exactly as written. Ordinary cell references, operators, function names, numbers, and text in the formula are preserved byte-for-byte; only the name tokens are spliced out.

The names are not deleted

Flatten Names rewrites the formulas that use a name — it never removes the name from the workbook. After flattening, your defined names still exist in the Name Manager; they’re just typically left unreferenced. That is exactly what makes the round trip possible: you can Restore the snapshot, or later Apply Names to put them back.

Before & after

Assume Sales is $E$10 on the current sheet, Income is $B$2, Revenue is defined as Sheet2!$A$1:$B$5, and TaxRate is the constant =0.21:

Case	Before	After
Same-sheet name	`=Sales*12`	`=$E$10*12`
Same-sheet range name	`=SUM(RevenueRange)`	`=SUM($A$1:$A$10)`
Cross-sheet name	`=Revenue`	`=Sheet2!$A$1:$B$5`
Named constant	`=Income*TaxRate`	`=$B$2*0.21`
Mixed names and a normal reference	`=Revenue-Costs+$D$5`	`=Sheet2!$A$1:$B$5-$E$11+$D$5`

Notice the normal reference $D$5 is untouched, and the constant 0.21 is inlined without parentheses because it parses as a plain number.

When a constant gets parentheses

A constant name is parenthesized only when its value is not a plain number and it contains an arithmetic or text operator (+ - * / & ^ %). This keeps operator precedence intact when the literal is spliced into a larger formula:

TaxRate = =0.21 → inlined as 0.21 (a number; no parentheses).
A name defined as =1+2 → inlined as (1+2) (not a number, contains an operator).
A name whose value text is empty → inlined as 0.

Two constant edge cases worth knowing

A negative number such as -5 still parses as a number, so it is inlined bare (-5, no parentheses) — in a few precedence-sensitive positions you may want to re-check the formula. And a name whose definition is itself a formula (for example =EOMONTH(TODAY(),0)) is treated as a value name and inlined as that text. Both are rare; both are worth a glance after a flatten if a name held something other than a simple range or number.

Choosing a scope

Flatten Names opens the Choose Scope dialog first. Pick how much of the workbook to convert:

Entire workbook — every visible sheet’s used range. Hidden and very-hidden sheets are skipped automatically and reported.
Active sheet — just the active sheet’s used range (processed even if that sheet is hidden).
Current selection — only the cells you’ve selected, across every sheet the selection spans.

The dialog remembers your last choice and defaults to Entire workbook the first time. Full scope rules are on the Rules & troubleshooting page.

Progress & cancellation

A flatten over a large model runs with a progress window showing the current sheet, a per-sheet and an overall progress bar, the number of formulas converted so far, and elapsed time. Click Cancel (or close the window) to stop — the operation halts at the next checkpoint and reports “The conversion was cancelled.” When it finishes, you get a summary like “Conversion completed. Converted 248 formula(s) across 6 sheet(s),” with a reminder that you can click Restore to undo it.

What gets skipped

Flatten is conservative: when it can’t safely rewrite something, it leaves the formula untouched rather than risk breaking it. The summary lists what was skipped and why.

Situation	Behavior
Protected sheet	The whole sheet is skipped with a “Worksheet is protected; flatten skipped” reason. Unprotect it and re-run if you want it converted.
Hidden / very-hidden sheet	Under Entire workbook scope only, these are auto-skipped and reported. Use Active sheet scope to flatten one deliberately.
External references	References into another workbook (`[Book.xlsx]Sheet1!Name` or `Book.xlsx!Name`) are left exactly as-is — names from other workbooks are never flattened.
Table / structured references	Structured references like `Table1[Revenue]` or `[@Amount]` are not flattened — they are recognized and left alone.
Formula too long	If flattening would push a formula past Excel’s 8,192-character limit, the rewrite is discarded, the original formula is kept, and the cell is reported as skipped (with the length). Nothing is truncated.
Write rejected	If Excel refuses the write for any reason, the cell is left unchanged and reported as a write-back rejection with a plain-language explanation.
Nothing to do	A formula with no defined names (or one whose only names are external/structured) is simply left as-is and not counted.

External references are left alone — including a sheet that looks like a filename

External detection is deliberately cautious: anything qualified by a name that looks like an Excel workbook file (.xlsx, .xlsm, .xlsb, …) is treated as external and skipped. In the unusual case where a worksheet is literally named like a file, its names would be conservatively skipped too — the safe choice, since the cost of a wrong rewrite is a broken formula.

Undoing a flatten

Each Flatten run captures an in-memory snapshot of every cell it changes, so you can reverse it:

Restore — click Restore (keytip RN) to write the original named formulas back, cell by cell, from the snapshot. It undoes only the most recent run, only in this session.
Not Ctrl+Z. The add-in writes formulas programmatically, which does not reliably feed Excel’s native undo — use Restore, not Ctrl+Z.
Or re-apply names later — partially. If the snapshot is gone (you closed the workbook, or ran something else), Apply Names can put existing names back over the flattened references that still exactly match a name. It is not an exact reversal, though: a constant that was inlined as a literal (0.21) has no address to look up, and any reference without an exact name match stays as a coordinate. For an exact undo, use Restore while the snapshot lasts.

One conversion at a time

While a snapshot from a previous Flatten or Apply is still active for the workbook, a second conversion is refused with “A previous Flatten/Apply snapshot is still active for this workbook. Run Restore first…” This guarantees the one-level snapshot always corresponds to the change you can actually back out. Run Restore first, then flatten again for a fresh conversion.

Apply Names — the inverse: references back to existing names.
Restore — how the snapshot works, and exactly what it can and can’t undo.
Rules & troubleshooting — every skip reason and the scope rules in one place.
Name Conversion overview — why flatten and apply exist, and the typical workflow.