How to Clean Up a Spreadsheet Mess (2026)

The context: you've inherited a model. Maybe it's an 8-tab LBO someone built in 2023 and patched quarterly since. Maybe it's a revenue forecast your predecessor built during a fundraise and nobody's touched the assumptions since Series B. Either way, the model recalcs in 4 seconds, has 40-plus hardcoded values scattered across 6 tabs, and the P&L doesn't quite tie to the cash flow. You have 3 hours before the deck goes to the syndicate.

Here's how to work through it systematically.

Failure Mode 1: Text-Stored Numbers Corrupting Your Cleanup

Text-stored numbers are the most insidious problem because they look fine. The cell shows 42,500,000. Your SUMIFS pulls $0. You spend 20 minutes questioning your range references before you notice the cell aligns left.

They appear most often in data pasted from ERP exports, accounting software PDFs, or payroll systems. A cell formatted as text that contains 42500000 will fail silently in every arithmetic operation downstream.

How to find them fast:

=SUMPRODUCT((ISNUMBER(IFERROR(VALUE('Revenue'!C2:C500),"")))*(NOT(ISNUMBER('Revenue'!C2:C500))))

This returns a count of cells that look numeric but are stored as text. Run it on every column that feeds a SUMIFS or cross-tab reference before you touch anything else.

The fix:

=VALUE(TRIM(CLEAN(A2)))

TRIM strips leading/trailing spaces that survive copy-paste. CLEAN removes non-printing characters that ERP exports love to inject. VALUE converts the result to an actual number. Paste-special-as-values once confirmed, then delete the helper column.

For a 2,500-row revenue file, this runs in under 2 seconds as an ARRAYFORMULA. Don't do it row-by-row.

Failure Mode 2: Hardcoded Assumptions Buried Across Tabs

A clean model has one Assumptions tab. Every driver - WACC, revenue growth rate, churn, gross margin, tax rate - lives in a named cell there. Every other tab references it.

What you actually inherit: 40 hardcoded values scattered across 8 tabs, half of them inconsistent with each other. The DCF uses a 12.5% discount rate. The LBO returns tab uses 11.8%. Nobody knows which one was the "final" number from the last investor call.

According to a 2023 analysis by F1F9, a UK-based financial modeling firm, over 70% of financial models reviewed in professional audits contain hardcoded values embedded directly in formulas rather than isolated in a dedicated assumptions section. That's not a corner case - it's the default state of most inherited models.

How to find every hardcoded number:

In Google Sheets, use Edit → Find and Replace with regular expression mode on. Search formula cells only:

[0-9]+\.?[0-9]*%

This surfaces percentage hardcodes. Run a second pass for dollar values:

(?<![A-Z])[0-9]{4,}

Any hit that isn't in your Assumptions tab is a candidate for extraction.

The fix: Pull every driver into a structured Assumptions tab. Here's the format that works across a real model:

Then replace each hardcode with a named reference:

=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3,'P&L'!B:B,"<="&Assumptions!$B$4)*Assumptions!$B$7

Every driver is now one cell. When the banker asks you to run a sensitivity at 12.0x instead of 14.2x, you change 1 number, not 12.

Failure Mode 3: Volatile Functions Killing Recalculation Speed

Volatile functions recalculate on every keystroke, regardless of whether their inputs changed. The four main offenders: NOW(), TODAY(), RAND(), and INDIRECT().

INDIRECT() is the worst. It's often used to build dynamic cross-tab references like =INDIRECT("'"&A1&"'!B5"), which looks clever but turns a single cell into a full recalculation trigger. A model with 200 INDIRECT() calls will feel sluggish from the first edit.

As of early 2026, Google's Sheets performance documentation explicitly flags INDIRECT and OFFSET as functions that "force repeated recalculations" and recommends replacing them with INDEX wherever the reference is not truly dynamic.

The replacement:

// Volatile - recalcs on every edit
=INDIRECT("'"&SheetName&"'!B"&ROW())

// Non-volatile equivalent
=INDEX(INDIRECT("'"&SheetName&"'!B:B"),ROW())
// Better: if SheetName is static, just reference directly
='Q1 Actuals'!B5

If you genuinely need a dynamic sheet reference (rare in a real model), keep the INDIRECT() but isolate it to a single lookup column rather than repeating it across 50 rows.

NOW() and TODAY() should be replaced with a static date cell in Assumptions unless you actually need real-time timestamps. A board pack doesn't need to recalc the date every time someone types in a cell.

Failure Mode 4: Circular References You Didn't Build

Circular references in a clean model are intentional - the interest expense loop in a three-statement model, for example, where interest feeds net income feeds retained earnings feeds debt paydown. They require iteration turned on (File → Settings → Calculation → Iterative calculation) and a convergence threshold.

According to Microsoft's documentation on iterative calculation, Excel defaults to a maximum of 100 iterations at a convergence delta of 0.001 before stopping. Google Sheets uses the same defaults. The problem: inherited circulars are often unintentional, created when someone dragged a formula one row too far or copied a tab and forgot to repoint a reference.

Unintentional circulars don't converge. They produce either 0 or a wildly incorrect number depending on which iteration the engine happened to stop at. And because iteration is already turned on for your intentional interest loop, the unintentional circular just silently picks up a bad value.

How to find them: In Google Sheets, unintentional circulars produce a yellow warning banner. More reliably: turn off iteration temporarily (File → Settings → Calculation → uncheck Iterative), force a full recalculate with Ctrl+Shift+F9, and watch for #REF! or the circular warning to surface cells you didn't expect.

Fix intentional loops first (document them with a comment in the cell). Then track down and break every unintentional one. The interest expense loop in a standard three-statement model should be your only circular - if you have more than 1-2, something is wrong with your model structure.

Failure Mode 5: Broken Cross-Tab Links After Tab Renames or Copies

This is the failure mode that kills board-pack credibility at 11pm the night before the meeting. Someone renamed "FY26 Revenue" to "Revenue_v2" six weeks ago, and 14 cross-tab references that pointed to the old name are now returning #REF!. The P&L summary tab shows $0 for three line items.

The #REF! errors are obvious. What's less obvious: some references silently follow a tab rename and some don't, depending on how they were built. Direct references (='FY26 Revenue'!C5) follow renames automatically in both Google Sheets and Excel. References built with INDIRECT() or concatenated strings don't.

The systematic audit:

// Find all external tab references in current sheet
// Grep for single-quotes followed by ! in formula bar
// In Apps Script:
function auditCrossTabRefs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var issues = [];
  
  sheets.forEach(function(sheet) {
    var data = sheet.getDataRange().getFormulas();
    data.forEach(function(row, r) {
      row.forEach(function(formula, c) {
        // Flag INDIRECT with string-concatenated tab names
        if (formula.includes('INDIRECT') && formula.includes('&')) {
          issues.push({
            sheet: sheet.getName(),
            cell: sheet.getRange(r+1,c+1).getA1Notation(),
            formula: formula
          });
        }
      });
    });
  });
  
  Logger.log(JSON.stringify(issues));
}

This surfaces every INDIRECT-based cross-tab reference in the workbook. Those are your fragile links. Replace them with direct references wherever possible.

For a real LBO with 8 tabs, expect to find 10-20 of these. Fixing them takes 45 minutes. Not fixing them takes one embarrassing moment in front of a managing director.

The Cleanup Sequence in Summary

In a $42M ARR model with 8 tabs, working through this sequence front-to-back takes 3 hours of focused work. Skipping steps and fixing things ad-hoc when they break takes weeks, spread across every future model update.

Where AI Cleanup Actually Helps

The parts of this process that are genuinely tedious - scanning 2,500 rows for text-stored numbers, grepping 8 tabs for hardcoded percentages, auditing every INDIRECT call - are exactly where an AI assistant in your spreadsheet sidebar earns its keep.

ModelMonkey sits inside Google Sheets and can scan your model, flag cells matching cleanup patterns, and draft the corrected formulas in context. You still approve every write. But "find all hardcoded percentages not in the Assumptions tab" is a 4-second ask instead of a 20-minute manual grep.

The two failure modes it can't fix for you are intentional circular-reference design (you need to document what those loops mean) and the business logic call on which hardcoded value is "correct" when you find duplicates. Those require a human who understands the model. Everything else is pattern matching at scale.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.