Join the beta

Excel · Editor — formulas & numbers

First third of the Excel Editor group: reconcile columns, paste formulas without shifting references, wrap errors, paint check cells, and bulk arithmetic.

When to use it: Budget vs Actual columns, reconciliation residuals, and bulk scale/round on a model block.

1. Map (this chapter)

Control Notes
Variance (split) Main button = Variance % (Ctrl+Shift+V)
Paste same formula No reference shift (Ctrl+Shift+F)
Handle errors IFERROR wrap (Ctrl+Shift+I)
Insert check Green/red near-zero pass/fail
Numbers Bulk arithmetic / scale / round

Text, dates, fill, and cleanup tools: Editor — text & fill, Editor — cleanup. Tracing: Formula audit.

2. Variance & CAGR

Selection

  • Exactly two cells in the same row (columns need not be adjacent), or
  • Two row-aligned columns, or
  • Several Ctrl-selected blocks where each block has exactly two distinct columns.

Steps

  1. Select the pair(s) — e.g. Budget and Actual.
  2. Click Variance for Variance %, or open the menu: - Variance % - Difference in percentage points - Difference in value - CAGR… (prompts for number of periods first)
  3. Navifia writes a live formula in the cell immediately to the right of the second (rightmost) cell in each pair. If that cell has content, you are asked before overwrite.
Menu item Formula idea Format Use for
Variance % (second/first)−1 0.0% Relative change (Actual vs Budget)
Difference in percentage points (second−first)×100 0.0" pp" How many points a margin moved
Difference in value second−first #,##0.0 (always) Plain delta
CAGR… (second/first)^(1/periods)−1 0.0% Compound growth over N periods

“n.a.”: Variance % and CAGR show n.a. when the two values are on opposite sides of zero (sign change). Diff (value) and Diff (pp) still work.

3. Insert check

Pass/fail colour on a reconciliation range (Excel Good/Bad style colours).

  1. Select the check cells (e.g. Assets − Liabilities − Equity residual).
  2. Click Insert check.
  3. Values with absolute value ≤ ~1e−5 go green; anything larger goes red.
  4. Re-run on the same selection replaces prior Insert check rules (no stacking).

4. Handle errors

  1. Select formula cells that show #DIV/0!, #N/A, etc.
  2. Click Handle errors (Ctrl+Shift+I).
  3. Choose fallback: blank, n.a., or a custom value — wraps with IFERROR.

5. Paste same formula

Pastes formulas without Excel’s usual reference shift.

  1. Copy a cell or a continuous range (Ctrl+C).
  2. Select destination (one cell expands to the copied shape; a larger range tiles).
  3. Click Paste same formula (or Ctrl+Shift+F).

Non-contiguous (Ctrl-click multi-area) copy or paste is refused — use one continuous range. Huge copies (beyond ~16k cells) store only the first cell so capture stays instant.

6. Numbers

Bulk arithmetic across the selection — values and formula cells together — from one dialog.

Steps

  1. Select the cells to change (numeric values, formulas, or a mix).
  2. Click Numbers.
  3. Pick an operation (see table below) and fill in its operand, decimal places, or units.
  4. Check the live preview at the bottom of the dialog.
  5. Set Wrap formulas and Skip blanks as needed (both checked by default).
  6. Click Apply.

Operations

Operation What it does
Add / Subtract / Multiply / Divide Applies a constant you enter to every numeric cell
Increase by % / Decrease by % Scales values up or down by a percentage
Scale by unit… Rescales between Units / Thousands / Millions / Billions — e.g. Thousands → Millions divides each value by 1,000 — with its own decimal-places setting and an option to update the cell’s number format to match (adds the K/M/B suffix)
Round Rounds to a chosen number of decimal places (0–6)
Absolute value Drops the sign (ABS)
Negate Flips the sign (Change value sign also has its own shortcut, Ctrl+Shift+−)
Fix trailing minus Turns accounting-style trailing signs like 123- into -123

What it does

  • Value cells are recalculated and written in place.
  • Formula cells are wrapped, not destroyed, when Wrap formulas is checked — e.g. =A1+B1 × 1000 → =(A1+B1)*1000; Round → =ROUND(A1+B1,2); Absolute value → =ABS(A1+B1); Negate → =-(A1+B1). Uncheck it to leave formula cells untouched instead.
  • Fix trailing minus always skips formula cells — it only rewrites the raw text of value cells.
  • Dividing by zero is refused before anything is written.
  • The dialog remembers the last operation you used.

7. Suggested workflow

  1. Select Budget + Actual columns → Variance (Ctrl+Shift+V).
  2. On residual check rows → Insert check.
  3. Chase a red cell with Precedents.

Troubleshooting

  • Variance writes nowhere / wrong place — need a valid pair selection; result goes right of the second column. Target occupied → confirm overwrite dialog.
  • “n.a.” on Variance % — sign change between the two values; use Difference in value or Diff (pp) instead.
  • Paste same formula refused — multi-area selection; copy/paste one continuous range.

← Threads · Next: Editor — text & fill →