Format Selection & Format Sheet

The scenario

You’ve pasted raw numbers into a schedule, built a new section, or inherited an unformatted model. You want it to read consistently — thousands separators, a sensible date mask, percents as percents — and, more importantly, you want the formatting to signal which numbers are typed assumptions and which are calculated. That’s what this pass does: it classifies each numeric cell and applies a named style, with inputs styled differently from formulas.

Where to find it

ModelXcel Pro tab → Format group, the first two large buttons. Both run the same engine; only the scope differs.

• Format Selection (keytip FS, Ctrl+Shift+J) — formats the current selection. With nothing selected it shows “Please select a range to format.”
• Format Sheet (keytip FA, Ctrl+Shift+Q) — formats the active sheet. Because it rewrites the whole sheet, it asks first: “This will format all used cells in the current worksheet. Continue?”

Selection vs. sheet scope

Format Selection passes your current selection straight to the engine. Format Sheet builds the target from the sheet’s effective used range — it finds the last truly populated row and column (via a backward cell search), which is usually tighter than Excel’s tracked UsedRange and avoids the phantom overhang a sheet accumulates. In both cases the engine then intersects the target with the sheet’s used range before doing anything, so cells outside the used range are dropped.

What it classifies

The classifier is deliberately focused: it styles numeric and date cells, sorting each into one of five families, and it splits every family into a typed inputvariant and a calculated (formula) variant. The split is decided purely by whether the cell holds a formula.

Beyond the five families, the pass also detects two special cases:

• Flag ranges. A series that is dominantly 0/1 is treated as an indicator and gets the MxFlag style plus a conditional-formatting rule (how existing rules are handled is governed by a Settings option).
• Empty referenced cells. If you’ve enabled it, the same pass highlights blank cells that a formula points at — see Empty Referenced Cells.

Step by step

1. Scope it. Select the range to format (for Format Selection), or just click any cell on the sheet (for Format Sheet — no selection needed).
2. Run it. Click Format Selection (FS / Ctrl+Shift+J) or Format Sheet (FA / Ctrl+Shift+Q). Format Sheet asks you to confirm.
3. Watch the pass. A progress window shows the work and lets you cancel a long run.
4. Review. Numeric and date cells now carry the house styles, with inputs visually distinct from calculations. If nothing matched the rules, the pass reports “No formatting changes needed”; an empty sheet reports “No used cells found in the active worksheet.”
5. Not happy? Click Undo Format — not Ctrl+Z — to reverse the pass.

What it writes to the workbook

For each classified cell the pass sets range.Style to the matching named cell style. A named style carries a font, an interior fill, borders, and a number format together, so one assignment brings all of those at once:

• Calculated (base) styles — Arial 10, black font, no fill, no borders. Just the number format and a clean look.
• Input styles — the same, but with the theme’s input font color and fill, and the cell left unlocked — the visual cue that a number is a typed assumption.
• Flag style (MxFlag) — the theme’s flag font, fill, and border, plus a conditional-format rule on the range.

Styles are written in coalesced batches (contiguous runs of cells) rather than cell by cell, which is what keeps a full-sheet pass fast. The colors come from the active theme; the number formats come from your Settings defaults (or the built-in defaults above).

The optional riders: Text Case, Empty Refs, Auto-Fit

Three other Format tools can run as part of the same pass when you’ve ticked their “Include in Format Selection / Sheet / Auto-Format” option (or the matching Settings checkbox). They execute in a fixed order:

1. Text Case runs first, before classification — it recases text cells and the quoted text inside label formulas. This rewrites cell values, so the pass captures a full-range undo snapshot up front.
2. Styles are applied to the classified numeric/date cells.
3. Empty Refs highlights blank cells that any formula in the workbook references (the scan is workbook-wide even though the highlight stays in scope).
4. Auto-Fit runs last, resizing the affected columns, and the workbook recalculates once.

Each rider is independent — turn any of them off to keep the pass style-only. If you didn’t ask for them and text got recased or blanks got highlighted, those riders were enabled; see the linked pages to turn them off.

How Settings and themes feed the pass

• Number formats — if you’ve changed the Number / Date / Percent / Factor / Ratio defaults in Settings, the pass uses your strings instead of the built-ins.
• Colors — the input, flag, and empty-ref colors come from whatever theme is active; the pass paints cells in the theme’s palette.
• Riders — the Auto-Fit and Empty-Refs include flags are read from your persisted settings; the Text-Case include flag and chosen case are session-only.
• Auto-Save — if you’ve enabled it in Settings, the workbook is saved at the end of the pass.

What it does not change

Cell values and formulas are untouched by the styling itself — the one exception is the optional Text Case rider, which rewrites text. The pass does not delete data, change results, or restyle text/header/total cells. The only structural change beyond styles is the optional Auto-Fit, which resizes columns.

Undo & safety

• Use Undo Format, not Ctrl+Z. The styles are written through Excel’s object model and do not feed Excel’s native undo stack. Undo Format reverses the pass from a snapshot taken before it ran — restoring styles, number formats, fonts, fills, borders, conditional formats, and any text-case value change.
• Each pass is one undo step, and the history holds up to 20 passes for the current Excel session only. It is cleared when you close Excel.
• Gaps to know: the included empty-reference highlight and the Auto-Fit column widths are not part of the style snapshot, so Undo Format will not reverse those. The style snapshot is only taken when the pass actually styles numeric or flag cells, so a pass that produces only empty-ref highlights isn’t snapshotted at all — Undo Format may not remove those highlights. And a pass over roughly 100,000 cells or more is not snapshotted either, so a huge Format Sheet may be non-undoable. Save before formatting a very large sheet.

Caveats & classification limits

• The classifier is a heuristic, not a parser of intent. A plain number that falls in the date-serial window can be styled as a date when a date-looking column header or row context is present — large IDs or amounts near a date heading can be mis-dated.
• Percent shortcut. Any numeric cell that already carries a % number format is treated as a percent, regardless of its label.
• Flag over-detection. A row that is mostly 0/1 can be read as a flag even when it’s really a ratio or percentage; the engine rescues such rows when the row label says percent/ratio/factor, but unlabelled 0/1 data may still be flagged.
• Format Sheet is broad by design. It rewrites the styles of every classified cell in the used range — deliberate, and the reason it confirms first. Review the result, and remember it’s reversible (within the snapshot limits above).

Related

• Auto Format — the same pass, run automatically as you type.
• Themes — recolor the styles this pass applies.
• Formatting Settings — the number-format defaults and rider toggles the pass obeys.
• Undo Format — the recovery path and its limits.