AI for spreadsheet analysis: what it actually does and when it's worth it

Most spreadsheet problems aren't spreadsheet problems. They're data structure problems — inconsistent formats, mixed data and presentation logic in the same cells, values that should be in a database living in a tab someone named "FINAL_v3_USE THIS." AI can help with analysis when the underlying data is reasonably clean. What it can't do is extract useful signals from a fundamentally broken data model.

That's the first thing worth understanding before evaluating AI tools for spreadsheet work. The second is that "AI for spreadsheets" covers at least four different capabilities that work very differently: natural language querying, formula generation, data extraction from unstructured sources, and anomaly detection. Each has a different fit profile, and conflating them leads to mismatched expectations.

What AI actually does with spreadsheet data

Natural language querying is the most visible capability: you type "show me Q3 sales by region, sorted by revenue" and the system returns the answer. This works well when the underlying data is clean and consistently structured — column names are clear, data types are consistent, there are no merged cells or decoration logic mixed in. The AI is translating your question into a formula or a code snippet (typically Python/pandas) that runs against your data. The quality of the output is directly proportional to the quality of the data structure.

When this fails: ambiguous column names, mixed data types, inconsistent date formats, values stored as text that should be numbers. The AI will usually produce something plausible-looking that answers the wrong question. The failure mode is subtle because the output format looks correct.

Formula generation is often the highest-leverage application. Complex Excel formulas — nested IFs, XLOOKUP with fallback logic, array formulas for cross-sheet aggregations, dynamic named ranges — are genuinely hard to write and debug. An AI that can generate a working formula from a natural language description, or explain what an existing formula does in plain terms, saves real time. This is largely independent of data quality: it's a text-to-code translation task.

The practical ceiling: formulas that depend on business logic the AI doesn't have. "Apply the Q3 bonus calculation from HR policy rev 4" isn't a task the AI can complete without the actual policy rules as context.

Data extraction from unstructured sources — turning a PDF invoice, a scanned report, or a structured XML file into spreadsheet rows — is where AI creates the most significant time savings for document-heavy workflows. For Italian businesses specifically, processing fatture elettroniche (SDI XML format) or extracting line items from PDF DDTs into a reconciliation sheet is a well-defined, high-volume task that AI handles reliably when configured for the specific document format. We covered the underlying architecture in our post on small language models and on-premises deployment.

Anomaly detection — flagging unusual values, identifying patterns that don't fit historical trends, highlighting statistical outliers — is genuinely useful for financial review and audit prep. The limitation is that it works on pattern recognition, not business context. An AI can flag that this month's travel expenses are 3x the historical average; it can't tell you whether that's a legitimate conference or an error. The flag is useful. The interpretation still needs a human.

The Italian context: bilanci, IVA and fiscal data

Italian businesses work with structured financial data that has characteristics AI tools need to handle correctly.

Bilanci XBRL — financial statements filed with the Registro Imprese in XBRL format — are machine-readable by design and can be imported directly into Excel or parsed programmatically. AI-assisted analysis of XBRL bilanci can extract standard balance sheet and income statement fields, flag year-over-year variances, and structure them into a comparison template without the manual transcription that typically consumes hours in due diligence and credit analysis workflows.

IVA reconciliation is a recurring pain point: matching declared IVA across liquidazioni periodiche, fatture emesse and ricevute, and the annual dichiarazione IVA. The data exists in multiple sources and formats. AI-assisted workflows that aggregate and cross-reference these sources — flagging discrepancies before they become problems in an audit — are a well-scoped, high-value use case where the data is structured enough for reliable automation.

Reporting from contabilità systems — exporting from Zucchetti, TeamSystem, or similar Italian accounting software into Excel for management reporting — often involves inconsistent export formats that require manual cleanup before analysis. This cleanup step is where AI formula generation and data normalisation tools make the biggest immediate difference, before adding any analytical layer.

What AI doesn't handle well

Large models with complex cross-sheet logic. AI can help write a formula for a specific calculation. It can't redesign a 15-tab model with circular references and hardcoded values spread across sheets. For financial models that have grown organically over years, the right intervention is usually a structural rebuild, not an AI layer on top.

Real-time data requirements. AI tools that analyse spreadsheet snapshots don't help if the question is about current inventory or live sales data. For real-time analysis, the data needs to move out of a spreadsheet into a database or data warehouse, and the AI connects to that.

Complex macro automation. AI can generate simple VBA or Apps Script snippets, but automation that interacts with external systems, handles error states, and runs unattended at scale is better built as proper software. AI-generated macros work for simple tasks and become increasingly fragile as complexity grows.

Verification of numerical outputs. AI-generated formulas should always be spot-checked against known values before being used for any consequential decision. The formula looks right; that doesn't mean it is right, especially when the AI was working from a sample of the data rather than the full range of edge cases.

When to fix the data model instead

The highest-leverage intervention is often structural rather than technological. Before evaluating AI tools, ask: could a non-technical analyst answer this question from the spreadsheet in under five minutes, given enough time? If no, the problem is usually the data structure, not the analysis capability.

Signs the spreadsheet needs rebuilding rather than AI layering: data and formatting logic in the same cells, manually maintained summary rows that duplicate source data, values that should be dates stored as text, the same information appearing in multiple places with no single source of truth.

AI tools work best on top of clean, consistently structured data — which is the same thing a database requires, and roughly the same thing a skilled analyst needs before starting any serious analysis. Getting the structure right first isn't a precondition to adopting AI; it's what makes the AI adoption actually produce reliable results.

Practical setup options

For teams that primarily work in Excel or Google Sheets, the most practical current options:

Claude in Excel (via the Excel add-in or API integration) handles natural language querying and formula generation directly in the spreadsheet interface, without requiring users to switch tools. It works from the data in the open workbook and can explain, generate, and troubleshoot formulas.

Python with pandas — via Jupyter, a local script, or a code interpreter tool — is the highest-capability option for data analysis but requires some technical comfort. The combination of an AI that generates the Python and an analyst who reviews and runs it is often the most powerful setup for complex analysis tasks.

Local models via Ollama for organisations handling sensitive financial data — relevant for Italian businesses where fatture and contabilità data carries confidentiality requirements — run the AI on-premises without data leaving the organisation's infrastructure. A 7B parameter model handles formula explanation and simple data transformation tasks on standard hardware.

The right choice depends on your team's technical level, the sensitivity of the data, and whether you need one-off analysis or a repeatable, auditable workflow.