Smart Formatting
Make a model read consistently in one pass — and make the formatting itself carry meaning, so a reviewer can tell typed inputs from calculated cells at a glance. The Format group classifies numeric and date cells, applies a coordinated set of named styles, recolors the whole model with reusable themes, and folds in cleanup steps (text case, empty-reference highlighting, column auto-fit). Most style and text passes are recoverable through Undo Format, the add-in’s own recovery path — though Auto Fit column widths and theme changes are not.
What it is
The Format group (labelled Format on the ribbon, internally “Smart Formatting”) is the first group on the ModelXcel Pro tab. It does four related jobs:
- Consistent house style. Format Selection and Format Sheet read each cell’s content and formula structure and apply a matching named cell style — with hardcoded inputs styled differently from calculated cells, so the formatting signals which numbers are typed and which are derived.
- Reusable color themes. Themes recolor the model’s named styles from a coordinated palette — eight built-ins plus your own saved palettes — and the choice is remembered with the workbook.
- Cleanup automation. Text Case normalizes capitalization, Empty Referenced Cells highlights blanks that formulas read as zero, and Auto Fit sizes columns to their contents.
- Safety and recovery. Undo Format reverses a formatting pass from an in-session snapshot stack. It is not Excel’s Ctrl+Z — the add-in writes through Excel’s object model, which does not feed the native undo stack.
The commands
On the ModelXcel Pro tab, the Format group reads left to right. Three commands are large buttons; the cleanup tools are split buttons (a primary action plus a dropdown of scopes and options):
| Command | Keytip | What it does | Writes to the workbook |
|---|---|---|---|
| Format Selection | FS | Classify and style the numeric/date cells in the selection (Ctrl+Shift+J). | Named Mx* cell styles |
| Format Sheet | FA | The same pass over the active sheet’s used range (Ctrl+Shift+Q). | Named Mx* cell styles |
| Auto Format | AF | Toggle live styling as you type (Ctrl+Shift+X). Session-only. | Named Mx* cell styles, on each edit |
| Auto Fit | AT | Resize columns to fit; dropdown for Fit Sheet and “while typing”. | Column widths only |
| Text Case | TC | Recase text to UPPERCASE / lowercase / Sentence / Proper. | Cell values (text) — rewrites content |
| Empty Refs | ER | Highlight empty cells that a formula references; In Selection/Sheet/Workbook. | MxEmptyRefCell named style |
| Themes | TH | Recolor the named styles from a palette; gallery + Customize manager. | Style colors + theme name (doc property) |
| Settings | SE | Number-format defaults, automation toggles, undo depth, shortcuts. | Preferences; number formats on save |
| Undo Format | UF | Revert the most recent formatting pass; click again to walk back. | Restores the prior state |
What the automatic pass styles — and what it leaves alone
Format Selection and Format Sheet style numeric and date cells, split into five families — Number, Date, Percent, Factor, and Ratio — each with an inputvariant (a typed value) and a calculated variant (a formula). They also detect 0/1 “flag” ranges and, optionally, highlight empty referenced cells. They do not auto-style text labels, section headings, or total lines — those cells are skipped and keep their existing formatting. (Styles for headers and totals exist and are recolored by themes, but you apply them yourself; the automatic classifier does not assign them.)
Inputs vs. calculations — the core idea
The thing that makes Smart Formatting a modeling tool rather than a beautifier is the input/formula split. When you run a format pass, a cell holding a typed number gets an input style (a distinct font color and fill), while a cell holding a formula gets the plain calculated style. That difference is the convention financial modelers rely on: anyone opening the model can see at a glance which numbers are assumptions that may be changed and which are derived and should not be typed over. The split is decided purely by whether the cell contains a formula.
What is written, and where it lives
Smart Formatting writes to a few distinct places. Knowing which is which is the key to using it safely:
| Item | Where it lives | Persisted? |
|---|---|---|
| The Mx* cell styles (and which cell wears which) | Inside the workbook (workbook.Styles) | Yes — saved in the .xlsx |
| Theme colors + the active theme name | The named styles + a ModelXcelTheme custom document property | Yes — with the workbook |
| Number-format defaults, Auto-Fit on/off, Empty-Refs on/off, shortcuts, flag-rule mode | %APPDATA%\ModelxcelPro\smart-formatting-settings.json | Yes — per Windows user, across all workbooks |
| Auto Format, Auto-Save, Undo Levels, the Text-Case choice | In memory on the running add-in | No — reset to defaults each Excel launch |
| The Undo Format history | In memory (a stack of up to 20 snapshots) | No — cleared when Excel closes |
| Column widths (Auto Fit) | The worksheet | Yes (with the workbook), but not in the Undo Format stack |
Two safety facts to internalize
Undo Format is not Ctrl+Z — and it does not cover everything
Because the add-in writes styles and values through Excel’s object model, those writes do not populate Excel’s native undo stack — pressing Ctrl+Z will not reliably reverse a Smart Format pass. Use the Undo Format button instead. And Undo Format covers styles, number formats, fonts, fills, borders, conditional formats, and text-case value rewrites — but not column widths from Auto Fit, and not theme changes. Those are reversed by other means (manual column width; re-applying a theme). The history is in-memory and gone after you close Excel.
License
Every command in the Format group is enabled whenever the license state is Trial, Licensed, or Grace; the buttons grey out only when the license is inactive (unlicensed, expired, or invalid). There is no per-feature tier — formatting is available in full during the trial. The Undo Format button has one extra condition: it is also greyed out when there is nothing to undo. See Activation & licensing.
In this section
- Format Selection & Sheet — the core styling pass, and exactly what it classifies and writes.
- Auto Format — live styling as you type (and why it resets each session).
- Auto Fit — column auto-fit, one-shot and “while typing”.
- Text Case — normalize capitalization in cells and label formulas.
- Empty Referenced Cells — find the blanks a formula treats as zero.
- Themes — recolor a model from a reusable palette.
- Formatting Settings — the defaults every Format command obeys, and what persists.
- Undo Format — the recovery path, its reach, and its limits.
Related
- Audit & Review — review a model after you’ve formatted it; the highlight lenses there are temporary, unlike Smart Formatting’s saved styles.
- Formula Tools — trace and understand the formulas behind the cells you’re styling.
- Keyboard shortcuts — the Format-group shortcuts and how to disable them.