Replacing Excel with AI makes sense only when a spreadsheet has outgrown its job: several people update it, it feeds financial or client-facing outputs, it depends on one person, or auditors question it. Small, single-user spreadsheets are usually fine. The safe path is to move the data into a database, keep Excel or Power BI as the front end where useful, and add AI for extraction, checks and automation.
This framework is for CFOs, financial controllers and operations managers at mid-sized to enterprise, multi-entity Australian organisations deciding which spreadsheets to keep, harden or replace.
Key takeaways
- Excel is the right tool for personal analysis, exploration and small, stable, single-user datasets.
- Seven warning signs show a spreadsheet has become a liability, from fragile lookups to key-person risk and audit concerns.
- A four-tier framework maps each spreadsheet to keep, enhance, migrate partially or replace.
- Migrate through staging and parallel running so history is kept and errors surface before cutover.
- AI helps most after the data is structured: extraction, anomaly detection and workflow automation.
How can one broken spreadsheet cost a company?
Consider a hypothetical manufacturer. A pricing spreadsheet uses VLOOKUP with column index 4 for unit cost. Months earlier, someone inserted a column for product codes. Nobody updated the lookup. Column 4 now returns the wrong field, and quotes go out below cost for months before anyone notices.
That is not Excel's fault. It is the assumption that a tool designed for personal calculation can safely run a business-critical process across a team.
How common are spreadsheet errors?
A 2024 literature review by Poon and colleagues in Frontiers of Computer Science, covering decades of spreadsheet error studies, reported that around 94 per cent of business spreadsheets used in decision-making contain errors. Professor Ray Panko of the University of Hawaii found in his research that most spreadsheets contain errors, with formula error rates of around 1 per cent of cells or more. In a model with thousands of formulas, that means dozens of potential errors.
Well-known cases show the consequences:
- JPMorgan's "London Whale" (2012): JPMorgan's own task force report identified errors in an Excel-based risk model, including manual copy-and-paste steps. Trading losses exceeded US$6 billion.
- TransAlta (2003): a cut-and-paste error in a spreadsheet led the Canadian power company to buy hedging contracts at higher prices, costing around US$24 million.
- Lazard and Tesla's acquisition of SolarCity (2016): a spreadsheet error in Lazard's analysis was reported to have understated SolarCity's value by around US$400 million.
These organisations had large finance and audit teams. Spreadsheet risk is not a size problem. It is a controls problem.
When is Excel actually fine?
Excel is the right tool when:
The data is flat and stands alone. One-off analyses, simple lists and ad hoc calculations do not need a database. A department head tracking monthly spend is fine in Excel.
The dataset is small and stable. Microsoft's own guidance describes Excel as suited to a flat or non-relational view of mostly numeric data. A few thousand rows, updated monthly with straightforward calculations, is well within its strengths.
You need flexibility. Modelling something new, testing a theory or running what-if scenarios is where Excel shines. Problems start when the quick model becomes a permanent business system.
It is genuinely single-user. One person builds it, maintains it and uses it. Version control and collaboration risks largely disappear.
The real question is whether a spreadsheet has outgrown its job.
Quick decision test
- Single user, simple calculations, small dataset: keep Excel
- Several users, complex formulas, important data: evaluate
- Audit concerns, compliance risk, recurring errors: migrate
What are the seven warning signs you have outgrown Excel?
1. Lookup chains nobody trusts
- VLOOKUPs referencing sheets that reference other workbooks
- Nobody remembers what column index 7 means
- Inserting a column breaks other files
- You moved to XLOOKUP but still do not trust the results
VLOOKUP looks only to the right, hardcodes the column index and defaults to approximate match, which silently returns wrong values if exact match is not specified. A billing workbook with dozens of lookups can shift by one column when a new service category is added, and nobody notices until a customer queries a bill several cycles later.
2. Version control problems
- Files named "Budget_Final_v2_ACTUALLY_FINAL.xlsx"
- Spreadsheets emailed because "the shared one might be wrong"
- Regular "which version do I use?" questions
- Changes overwritten when two people edit
For example, three project managers at a hypothetical construction company each hold their own version of the pricing spreadsheet and quote the same client different prices for the same scope. Every branch does it differently.
3. Single points of failure
- If the builder left tomorrow, could anyone maintain it?
- Are some cells "magic numbers" nobody can explain?
- Is critical data in someone's personal OneDrive?
- Would losing the file set the business back weeks?
Critical spreadsheets often lack documentation, peer review and change logs. When the creator leaves, the next person knows which cells to type into but not why. If the inventory manager retires and nobody understands the 15-tab forecasting workbook, rebuilding the logic becomes an unplanned project. This is the "if Sam leaves we're stuffed" problem.
4. Manual data movement between systems
- Exports from the accounting system are adjusted by hand and re-imported
- Data is copied between spreadsheets every week
- Reconciliation means comparing two spreadsheets by eye
- Someone's job is mostly moving data from one place to another
Every manual transfer is a chance for error, and the risk repeats with every import.
5. Compliance and audit concerns
- Auditors question spreadsheet controls
- You cannot show who changed what, or when
- Historical versions are missing or inconsistent
- "Can you show me the calculation trail?" makes people hesitate
Financial regulators, including the UK's Financial Conduct Authority, have warned about the risks of uncontrolled spreadsheets in key processes. For Australian organisations, spreadsheets that feed financial reports, ATO obligations or sustainability disclosures carry regulatory as well as operational risk.
6. Performance problems
- The file takes a long time to open
- Calculations run for minutes or crash Excel
- Automatic recalculation is turned off just to work
- "My laptop can't handle this file" is a real excuse
Excel can hold 1,048,576 rows per sheet. That does not mean it should. Once you are fighting the software, it has become a hindrance.
7. Many users at once
- Several people update the same dataset daily
- People announce when they are "in the file"
- Conflicts and lost edits are routine
Databases were built to handle concurrent access. Spreadsheets were not.
Which tier is your spreadsheet in?
Assess your spreadsheet
- Small dataset, single userTier 1: Keep Excel
- Moderate size, 2-5 usersTier 2: Enhance Excel
- Large or slow, 5-15 users, feeds other systemsTier 3: Migrate partially
- Critical risk, organisation-wideTier 4: Replace entirely
- Active compliance or audit issuesTier 4: Replace entirely
Tier 1: Keep Excel (low risk)
Single user or occasional sharing, a few thousand rows, simple calculations, no system connections, no compliance use, updated monthly or less. Action: keep it, document the logic and back it up.
Tier 2: Enhance Excel (medium risk)
Two to five regular users, tens of thousands of rows, nested formulas, occasional system connections, internal reporting only, updated weekly. Action: add controls without replacing:
- Move to SharePoint or OneDrive with version history
- Use Power Query for data connections instead of copy and paste
- Protect cells and validate inputs
- Document critical formulas
- Automate backups
Tier 3: Migrate partially (high risk)
Five to fifteen users with conflicting needs, very large or slow files, business-critical logic, regular links to accounting, ERP or CRM, financial or client-facing outputs, daily updates. Action: move the data layer to a proper database and keep Excel or Power BI as the front end. Options include Power BI on a SQL database, a low-code platform for teams without much IT support, or a custom web application for specific workflows.
Tier 4: Replace entirely (critical risk)
Organisation-wide data used by several departments or entities, a single undocumented point of failure, active audit concerns, past errors with financial impact, performance that regularly stops work, or sensitive data in unprotected files. Action: full migration to a database with workflow automation and AI where it adds value. Our guide to moving reporting into Power BI and our data, BI and analytics service cover the target state.
How do you migrate from Excel without losing history?
The most common fear: "We have ten years of data in these spreadsheets." You will not lose it.
- Week 1-2Assessment and inventoryCatalogue files, map dependencies and data flows
- Week 2-3Database designTables and relationships, fix ambiguous fields
- Week 3-4Staging migrationImport, clean and validate
- Week 4-6Parallel runningBoth systems, outputs compared daily
- Week 6-8CutoverGo live, archive spreadsheets read-only
Phase 1: Assessment and inventory
- Catalogue every spreadsheet in the process.
- Identify dependencies: which files feed which.
- Map data flows: where data starts and where it ends up.
- Document business logic: which calculations actually matter.
- Assess data quality: for example, "0", "Zero" and "None" meaning the same thing.
With a hundred or more active spreadsheets, allow a couple of weeks. The alternative is discovering dependencies one broken report at a time after migration.
Phase 2: Database design
Define tables for business entities (customers, products, transactions), map relationships, create lookup tables for consistent values and plan for history with audit tables and effective dates. This is where you fix problems: a column that means three things becomes three properly labelled fields.
Phase 3: Staging migration
Import into intermediate tables first. Run quality checks for duplicates, missing values and inconsistencies. Apply transformation rules. Check totals still match. Review with the people who use the data. Duplicate customers are common: "Johnson & Co", "Johnson and Co" and "JOHNSON & CO." get consolidated here.
Phase 4: Parallel running
New data goes into the database while key outputs are produced both ways and compared daily. Discrepancies surface while the spreadsheet is still there to check against.
Phase 5: Cutover
Stop data entry in the spreadsheets, move users to the new interfaces, keep spreadsheets read-only for reference and set a date to retire access. The history stays available. It just stops being the live system.
Where does AI actually help when replacing Excel?
- ExtractRead documents that used to be typed into spreadsheets
- DetectFlag duplicates, anomalies and formula-like errors
- AutomateCoding, routing and report generation
- ApproveA person approves exceptions and postings
Intelligent data extraction. AI reads invoices, receipts and documents far better than traditional OCR. During migration it can extract data from scanned source documents, interpret handwritten notes and reconcile inconsistent entries such as "Mel" and "Melbourne".
Pattern recognition and anomaly detection. AI is good at spotting what people miss: results that break expected patterns, duplicates across differently formatted entries and transactions outside historical norms.
Process automation. With data in a proper database, AI can take on routine categorisation and coding, rule-based approval routing, report generation and exception escalation, with a person approving anything that reaches a customer or the ledger. Our guide to automating invoice processing shows one common example.
None of this works on a mess. Clean, structured data comes first.
How should you think about the cost of replacing Excel?
The investment scales with the tier, and the comparison that matters is against the cost of spreadsheet risk.
| Typical work | Main risk reduced | Impact | |
|---|---|---|---|
| Tier 2: Enhance Excel | SharePoint setup, Power Query, controls, documentation | Version and input errors | Quick |
| Tier 3: Partial migration | Database design, migration, Power BI or app front end, training | Concurrency, performance, rekeying | Medium |
| Tier 4: Full replacement | Database, application, integrations, AI, training, support | Key-person, audit and compliance risk | Largest |
- Hours a week maintaining and reconciling the spreadsheetYour time study
- Errors found in the last 12 months and their costYour incident log
- People who understand the logicYour key-person count
- Audit findings or queries about the spreadsheetYour audit reports
- Cost of one serious errorYour worst realistic case
How do you make the decision?
This week: pick your three most critical spreadsheets and score them against the seven warning signs. This month: document the business logic in them. Get it out of one person's head. This quarter: place each in a tier and agree the action. Before EOFY: either harden the spreadsheets with controls or start the migration.
The worst choice is no choice. Each month of delay adds data, complexity and risk to the eventual migration. For multi-entity groups, our guide to month-end close automation shows where spreadsheets usually hurt most.
Excel is not the enemy. It is a very good tool used beyond its scope. The question is whether you know which of your spreadsheets are dangerous.
How Solve8 can help
Our consultants and engineers assess spreadsheet risk, migrate critical spreadsheets into databases with Power BI or custom front ends, and add AI automation where it pays, keeping your history intact. See our data, BI and analytics and custom software development services, or talk to us.
