Building Bulletproof Excel Workbooks with AI-Assisted Validation

What you'll build

A quarterly-forecast workbook with the seven validation layers in place: mapped failure modes, an AI Validation Skill for the workbook context, Data Validation on all input cells, structured Tables replacing raw ranges, LET-wrapped formulas with reference integrity, dedicated sanity-check cells asserting invariants, snapshot-based change detection, and a one-click health check that reports the workbook's status. By the end you'll have a template you can apply to any workbook and a discipline you can teach to your team.

Prerequisites

• Excel 2021 or Microsoft 365 (older Excel works with fallbacks noted where relevant).
• An AI provider account (Claude Desktop, ChatGPT, Gemini, or Mistral) — used for validation prompts throughout.
• A real workbook you care about — ideally one that already had a "how did that number get wrong" moment.
• Optional but strongly recommended: Excel Formula Generator Skill installed. Not required but the tutorial cross-references it repeatedly.

01Map the failure modes before touching Excel

Validation without a threat model wastes effort. Spend 30 minutes listing every way this workbook has failed or could fail. Do this before opening Excel. Without a threat model you'll validate what's easy, not what's risky.

For most business workbooks the failure modes cluster into eight patterns. Copy the list below into a text file and mark each as seen, plausible, or not applicable for your workbook.

Failure mode checklist

[ ] Hard-coded value pasted into a formula cell
    (Someone overwrote a formula with the current number.
    Survives all future updates as a wrong number.)

[ ] Vendor CSV column rename
    (Column renamed "Total" → "Total_USD". VLOOKUP still
    runs but references wrong column. Silent wrong answers.)

[ ] Row insertion breaking absolute ranges
    (Formula references A2:A100 hard-coded. Someone adds
    rows past 100. New rows silently excluded.)

[ ] Broken cross-sheet reference
    (Sheet renamed. Cross-sheet formulas return #REF! or,
    worse, point to a differently-named sheet with unrelated
    data.)

[ ] Circular reference that "worked"
    (Iterative calculation enabled at some point. Formula
    converges to a value but the value is wrong.)

[ ] External link to file that no longer exists
    (Formula pulls from a file that got moved or deleted.
    Cached value still displays; refresh returns #REF!.)

[ ] Date column silently coerced to text
    (CSV import treated dates as strings. Date formulas
    return #VALUE! or, worse, appear to work but sort wrong.)

[ ] Rounding accumulation
    (Currency stored to 2 decimals but summed at 15
    decimals. Sub-cent errors accumulate to visible
    discrepancy over thousands of rows.)

Mark each. The ones marked seen get validated first — you already know they happen. The ones marked plausible get validated next. The not applicable ones get skipped; you can't validate infinite failure modes.

The exercise takes 30 minutes but changes what you build. Skip it and you'll spend three hours implementing input validation on a workbook whose real failure mode is external links breaking.

02Set up an AI Validation Skill for the workbook

Give the AI a document describing the workbook's structure, expected inputs, and invariants that must hold. This is a "workbook contract" the AI can validate against without you re-explaining it every session.

Create a plain text file — workbook-contract.md — that captures the essential facts. This is Skill definition syntax adapted for a specific workbook rather than a general capability.

# quarterly-forecast.xlsx contract

## Sheets
- Inputs: pasted CSVs from three vendors (Acme, Globex, Initech)
- Model: derived calculations, no manual inputs
- Report: leadership-facing outputs only

## Input sheet columns (must exist exactly)
- Acme table:    order_id, order_date, product, quantity, unit_price_usd
- Globex table:  invoice_number, invoice_date, sku, qty, price_usd
- Initech table: id, date, item, count, amount_usd

## Invariants that MUST hold
- No hard-coded numeric values in Model sheet formulas
- All cross-sheet references use Table names, not A1:B100
- Report sheet totals match sum of Model sheet line items to
  within 0.01 (currency rounding tolerance)
- No external file links (all data pasted in as values or
  linked via Excel Table structured references)
- All formulas in Model sheet start with = or use LET/LAMBDA
  (no leading apostrophes indicating text-mode formulas)

## Validation prompts
When asked to validate this workbook, check ALL invariants
above and report violations by cell reference. If any input
sheet columns are missing or renamed, halt validation and
report the schema drift before proceeding.

Give this file to the AI at the start of every session. In Claude Desktop, add it as a project knowledge file. In ChatGPT, upload it as a Custom GPT knowledge. In Gemini, paste it into the Gem instructions. Now the AI has the same context you do.

03Input layer — Data Validation and Excel Tables

The input layer catches bad data before it ever reaches formulas. Two mechanisms carry most of the load: Excel Data Validation (Data > Data Validation) and Excel Tables (Ctrl+T on any data range).

Data Validation lets you constrain what can go in a cell: a value in a specific list, a number in a range, a date after a certain point, or a custom formula. The most valuable use is on cells where someone might paste the wrong thing.

Data Validation cheat sheet

Product column (Inputs sheet):
  Data > Data Validation > Allow: List
  Source: =Products_List  (a Named Range of valid products)
  Error alert: "Product not in master list. Add to
              Products_List first, or check spelling."

Date columns:
  Data > Data Validation > Allow: Date
  Data: between
  Start date: =DATE(2020,1,1)
  End date: =TODAY()+30
  (Catches paste-of-year-2224 typos and future dates
  that indicate wrong column pasted in.)

Currency columns:
  Data > Data Validation > Allow: Decimal
  Data: greater than or equal to
  Minimum: 0
  (Catches accidental negative values. If negatives are
  legitimate for refunds, use a wider range with an
  "are you sure" alert instead of a hard block.)

Excel Tables (formal name: ListObjects) are the single highest-leverage change you can make. Convert any data range to a Table with Ctrl+T. Now formulas reference Sales[Revenue] instead of A2:A1000. When someone adds a row, formulas auto-extend. When someone renames a column, formulas break loudly with a clear error instead of silently referencing wrong data.

Ask AI to walk your workbook and identify every A1:X999-style range in formulas that should be a Table structured reference. Prompt: "List every formula in the Model sheet that uses hard-coded row ranges. For each, suggest the equivalent structured reference assuming the source data becomes a Table named after its sheet." AI produces the audit in seconds.

04Formula layer — reference integrity checks

The formula layer catches broken references, hard-coded values in derived cells, and legacy patterns that hide bugs (VLOOKUP+IFERROR being the worst offender).

Run this validation prompt against the Model sheet:

Validation prompt (paste into AI with workbook context):

Walk the Model sheet and report:

1. Every cell where a numeric literal appears inside what
   should be a formula. Example: =Sales[Revenue] * 1.05
   The "1.05" is a magic number — where should this
   come from? Named range? Assumption cell?

2. Every VLOOKUP formula. For each, generate the XLOOKUP
   equivalent with a default value for missing matches.
   VLOOKUP+IFERROR pattern is a smell.

3. Every formula with a #REF! error. Report cell reference
   and the last-known-good formula from workbook history
   if available.

4. Every cross-sheet reference using literal sheet name.
   Confirm the referenced sheet still exists with the exact
   name in the formula.

5. Every formula longer than 200 characters. These are
   candidates for LET wrapping — refactor to name
   intermediate results for readability and performance.

The AI walks the workbook (via Excel MCP if connected, or via a description you paste) and returns a numbered list of issues. Fix them by category rather than one-by-one — refactoring all VLOOKUPs to XLOOKUP is faster in one pass than mixed with other work.

05Sanity check cells — hard invariants

The most reliable validation is a formula that returns TRUE if everything is right and FALSE if anything is wrong. These live in a dedicated section of the Report sheet, usually labeled "Validation" with a colored background so anyone opening the file sees them.

Every workbook has invariants — things that must always be true regardless of what data flowed through. Codify each as a cell formula.

Sanity check cells for quarterly-forecast.xlsx

V1: Report total equals model total (within 1 cent)
=LET(
  report_total, SUM(Report[Quarterly_Total]),
  model_total,  SUM(Model[Line_Item_Total]),
  ABS(report_total - model_total) < 0.01
)

V2: No negative quantities (unless refund column exists)
=SUMPRODUCT((Inputs_Acme[quantity] < 0) * 1) = 0

V3: All input dates within expected range
=AND(
  MIN(Inputs_Acme[order_date]) >= DATE(2020,1,1),
  MAX(Inputs_Acme[order_date]) <= TODAY() + 30
)

V4: Row count in Model matches sum of inputs
=LET(
  model_rows,  ROWS(Model),
  input_rows,  ROWS(Inputs_Acme) + ROWS(Inputs_Globex) + ROWS(Inputs_Initech),
  model_rows = input_rows
)

V5: No formula in Model uses a hard-coded date
(Manual audit; AI can flag but this one is harder to
codify as a formula. Flag in the contract instead.)

Validation summary cell:
=IF(AND(V1, V2, V3, V4), "OK", "VALIDATION FAILED")
Then Conditional Format: green if "OK", red otherwise.

Add one sanity check per meaningful invariant. Ten checks is usually the right number — fewer misses things, more becomes noise. Every check should be reviewable in one glance: TRUE means the invariant holds, FALSE means something went wrong. The summary cell at the top uses AND() to fail if any check fails.

Ask AI to generate additional sanity checks: "Given the workbook contract above and the Model sheet structure, suggest five more invariant-checking formulas that would catch failures the current V1-V5 don't cover." AI is good at this because invariants are the kind of formal reasoning it does well.

06Change detection — snapshot vs. current state

Some failures only show as diffs against last known good state: a value that shouldn't have changed did, or a total shifted by more than expected. Snapshot the workbook periodically and diff.

The mechanism is simple: on a hidden "Snapshot" sheet, paste-as-values the key output cells from your last accepted version. Add a "Delta" sheet that computes the difference between current and snapshot. Then AI-audit the deltas: what changed, by how much, and does it make sense given what was updated in the inputs?

Change detection workflow

1. After accepting a quarterly forecast, snapshot the
   Report sheet:
   - Right-click Report tab → Move or Copy → Create a copy
   - Rename copy to "Snapshot_2026_Q2"
   - Select all cells → Copy → Paste Special > Values
   - Hide the snapshot sheet (right-click tab → Hide)

2. On a new "Delta" sheet, compute cell-by-cell diffs:
   =Report!B5 - Snapshot_2026_Q2!B5

3. Add conditional formatting to Delta: green for expected
   changes (within 5%), yellow for medium changes (5-15%),
   red for anything larger.

4. Run this validation prompt when preparing the Q3 forecast:

   "Compare the Delta sheet values against the changelog of
   input updates between Q2 and Q3. For each significant
   delta (red cells), explain whether the change is
   consistent with the input updates. Flag any change that
   doesn't have a matching input reason."

Change detection catches the failure mode nothing else does: values changing when they shouldn't have. If a cell shifted 12% but no upstream input for that cell changed, something is wrong — probably a formula that was silently modified.

07One-click workbook health check

The final layer is a single button that runs all the validation and reports status. In Excel this is a small VBA macro triggered by a button on the Report sheet.

Ask AI to generate the health check macro:

Sub WorkbookHealthCheck()
    Dim results As String
    Dim allPass As Boolean
    allPass = True
    results = "Workbook Health Check — " & Now & vbCrLf & vbCrLf

    ' Layer 3: Input validation errors
    Dim invalidCells As Long
    invalidCells = Application.WorksheetFunction.CountIf( _
        Sheets("Inputs").Range("A:Z"), "#N/A")
    If invalidCells > 0 Then
        results = results & "[FAIL] Input sheet has " & _
                  invalidCells & " #N/A errors." & vbCrLf
        allPass = False
    Else
        results = results & "[OK]   Input sheet clean." & vbCrLf
    End If

    ' Layer 4: Formula errors in Model
    Dim modelErrors As Long
    modelErrors = Application.WorksheetFunction.CountIf( _
        Sheets("Model").UsedRange, "#REF!")
    If modelErrors > 0 Then
        results = results & "[FAIL] Model sheet has " & _
                  modelErrors & " #REF! errors." & vbCrLf
        allPass = False
    Else
        results = results & "[OK]   Model formulas clean." & vbCrLf
    End If

    ' Layer 5: Sanity check cells
    If Sheets("Report").Range("ValidationSummary").Value _
       = "VALIDATION FAILED" Then
        results = results & "[FAIL] Sanity checks failed." & vbCrLf
        allPass = False
    Else
        results = results & "[OK]   All sanity checks pass." & vbCrLf
    End If

    ' Layer 6: Delta sheet red cells
    Dim redDeltas As Long
    redDeltas = Sheets("Delta").Range("RedCount").Value
    results = results & "[INFO] " & redDeltas & _
              " unexpected deltas >15% — review manually." & vbCrLf

    results = results & vbCrLf
    results = results & IIf(allPass, "OVERALL: PASS", "OVERALL: FAIL")

    MsgBox results, IIf(allPass, vbInformation, vbCritical), _
           "Workbook Health Check"
End Sub

Insert a Form Control button on the Report sheet, assign it to WorkbookHealthCheck, label it "Run Health Check." Now anyone opening the file can validate the workbook status in one click. When you hand this workbook to a colleague or a future you, they don't have to remember which cells to check — the button does it.

Common pitfalls

• Building all seven layers at once. Do Steps 1, 3, and 5 first — failure mapping, input validation, sanity checks. Those three catch 80% of failures. The other four are marginal improvements you add when you have time.
• Sanity checks that are too vague. "Numbers look reasonable" isn't a sanity check. "Report total matches Model total within 1 cent" is. Every check must evaluate to a specific TRUE/FALSE.
• Ignoring the workbook contract when the workbook changes. If you refactor the Model sheet and don't update the contract, next validation run reports every legitimate change as a violation. Update the contract in the same commit as the refactor.
• Trusting AI validation output blindly. AI can miss issues and can falsely flag non-issues. Validation output is a first pass; the human still reviews every flagged item. AI multiplies your effectiveness; it doesn't replace judgment.

Where to go next

The seven layers here are a foundation. Three natural extensions:

Automated snapshot rotation: Instead of manual copy-paste snapshots, use a VBA subroutine triggered on save that writes a snapshot to a separate archive workbook with a timestamp. Compare against last N snapshots to see whether values are drifting over time.

External data source validation: If your Inputs sheet pulls from a database or API via Power Query, add schema-drift detection at the query level — halt refresh if column names or types change from expected. The CSV Cleaning Skill handles this for CSV inputs; equivalent patterns work for database queries.

Chart validation: Charts have their own failure modes (data series pointing to wrong ranges, hidden filters skewing visuals). The Excel Chart Auto-Generator Skill flags common chart-junk patterns and can audit existing charts against best practices.

Frequently asked questions