Spreadsheet changes that can pass CI

For important spreadsheets, the painful question is rarely “which file changed?” It is “did someone replace the calculation behind this number, what now depends on it, and did we notice before the model was shared?”

Excel is flexible because the model and its logic live together. That same flexibility turns ordinary version history into a weak review surface: Git sees a binary file, while a reviewer needs to see a change in intent.

Workbook-aware Git diffs and formula anomaly analysis already address valuable parts of the problem. I wanted the missing merge-boundary layer: a small, local tool that could describe a semantic workbook change, trace its visible consequences, and enforce a rule that a reviewer can read.

That tool is FormulaFence, an MIT-licensed, local-first CLI for .xlsx and .xlsm workbooks.

Put the control at the merge boundary

FormulaFence compares two workbooks without executing formulas or macros. It detects formula-to-value substitutions, formula changes, sheet visibility, defined-name and Excel-table definition changes, explicit external references, broken #REF! formulas, calculation-setting changes, and macro payload changes.

For every changed cell, it follows statically visible A1-style, ordinary named-range, and supported table dependencies and reports downstream formula cells with deterministic shortest-path samples.

formulafence check approved.xlsx candidate.xlsx \
  --policy formulafence.yml \
  --format sarif --output results.sarif

The policy is plain YAML. It can protect an output cell, limit routine edits to an input range, ban formula overrides, and cap downstream impact. It does not replace judgment; it makes a review expectation visible and repeatable.

version: 1
rules:
  no_formula_to_value: true
  no_new_external_links: true
  no_new_broken_references: true
  no_new_parser_warnings: true
  no_new_unresolved_references: true
  no_new_dynamic_references: true
  no_table_definition_changes: true
  max_downstream_impact: 100

protected_cells:
  - Dashboard!B12

Fail closed when the analysis has a blind spot

FormulaFence 0.4.0 resolves ordinary workbook and sheet-local names with static A1 destinations, plus a conservative set of fully qualified Excel-table references: table names, static columns or contiguous column ranges, and #All/#Data/#Headers/#Totals regions. Profiles inventory the table definitions behind those references, and a diff emits FF013 if one changes; no_table_definition_changes can make that a hard boundary.

One practical safeguard is coverage visibility. When the workbook parser encounters an OOXML extension it cannot fully interpret, FormulaFence records a coverage note. A candidate that adds one can be rejected with no_new_parser_warnings. Profiles also list unresolved range tokens and dynamic reference functions; a change can be rejected with no_new_unresolved_references or no_new_dynamic_references. This-row (@) and complex table syntax remain coverage notes rather than guessed dependencies.

Test beyond toy files

Unit fixtures are necessary, but an Office-file reader also needs to meet real workbooks. I validated FormulaFence against the public Foresight Cap Table and Exit Waterfall Tool: 18 sheets, 6,623 non-empty cells, and 4,228 formula cells.

The inspection found an unsupported OOXML extension and recorded it as a structured coverage note instead of leaking a raw dependency warning into CI output. It also identified 36 cells using INDIRECT, making the model’s dynamic-reference surface explicit. On a local, non-distributed copy, replacing one exit-waterfall formula with a number traced 330 downstream formula cells; the starter policy rejected both the formula override and the impact limit.

I also checked a public structured-reference workbook: FormulaFence identified its one table and 15 table-reference formulas with no unresolved tokens. Changing one table data cell on a local copy traced 16 downstream formulas, including a table total and an output outside the table.

Those results are a compatibility demonstration, not a claim that the source model is correct. The full limits and validation record are in the FormulaFence repository.

What it does not claim

FormulaFence does not calculate Excel or prove a financial model correct. Material models still need a qualified owner, recalculation in the approved spreadsheet engine, and independent review.

But a review process should at least make it hard to silently replace a formula with a number. That is the narrow, useful boundary FormulaFence is built to enforce.

The current release is FormulaFence 0.4.0 on GitHub. The canonical version of this post lives at sybilgambleyyu.github.io/posts/formulafence.html.