Spreadsheet analysis assistant

Spreadsheets remain the most widely used analysis tool in business, but most users know only a fraction of their functions, and large inherited models are hard to understand and error-prone. A spreadsheet assistant lets users say what they need ("sum revenue by region and month, flag outliers, add a chart"), writes the formulas or code, explains existing models and audits them for errors. Typical benefit: hours saved on routine formulas, cleaning and reporting, and fewer silent formula mistakes, as long as every AI change is reviewable.

Problem & value

• Today: finance, controlling, sales-ops and HR staff build and maintain business-critical workbooks by hand. Knowledge sits with one person, nested formulas are opaque, copies of the file circulate by email, and errors (shifted ranges, hard-coded overrides, wrong lookups) are found late. Research on spreadsheet risk has documented high error rates in real-world models for decades [6].
• AI value: (1) generate and explain formulas (XLOOKUP, SUMIFS, LET, LAMBDA, array formulas, regex), (2) clean and reshape data, (3) build pivots and charts, (4) audit and document a model ("what does this workbook do?"), (5) write Python or Office Scripts for what formulas cannot do, (6) summarise results in text.
• Why now: assistants moved from single-cell helpers to agents that read the whole workbook, plan multi-step edits and write them back with a change log. Microsoft's Agent Mode in Excel [1], Anthropic's Claude for Excel [2] and Gemini in Google Sheets [3] all work this way. your own baseline before promising savings.

Variants

• Workbook agent: reads all tabs, edits multiple ranges, builds pivots and charts, keeps an undo trail.
• Model audit & documentation: reviews financial models, maps dependencies, flags hard-codes.
• Data cleaning in the sheet: splitting columns, normalising dates and names, deduplication (see DATA-003).
• Code-interpreter analysis: upload the file, the model writes Python/pandas, returns charts and a cleaned file.
• Private use: household budget, tax-return preparation, hobby data; consumer tiers of the big assistants suffice.

How it works

1. user request (natural language)
2. workbook context extraction (sheet names, headers, used ranges, formulas, named ranges, sample rows)
3. planning (LLM decides: formula | pivot | script | explanation | audit)
4. tool calls (read range, write range, create pivot/chart, run sandboxed Python/Office Script)
5. preview of the change (diff, highlighted cells) and user confirmation
6. write-back with change log and undo
7. verification (recalculate, compare totals, check errors such as #REF!, #N/A)

• Context handling: the full workbook rarely fits the context. Tools send a structural summary (headers, types, samples) and fetch ranges on demand; large tables are processed by generated code, not by the model reading every row.
• Code beats mental arithmetic: reliable assistants let the model write formulas or Python and let the spreadsheet engine or interpreter compute, instead of having the LLM add numbers itself.
• Human-in-the-loop: preview-and-accept for every write, protected input cells, a tracked change log, and human review of any result that feeds a decision, filing or payment.

Data preparation

The data is already in spreadsheets, but these live on personal drives, file shares, mail attachments and SharePoint, often as many diverging copies. Typical gaps: merged cells, multi-row headers, totals mixed into data, inconsistent date and number formats, hidden sheets, hard-coded values in formulas, and sensitive columns (salaries, customer names) in the same workbook.

Preparation steps: 1. Pick two or three high-value workbooks and reshape them into tidy tables with one header row (PREP-011). 2. Agree KPI definitions and column names in a short glossary (PREP-012). 3. Clean permissions and oversharing on the SharePoint or file share the assistant can read (PREP-023). 4. Define which workbooks may be sent to which AI tool (PREP-024). 5. Collect a few correct example reports as gold standards (PREP-016).

Tools: Power Query, Excel tables, Copilot or Gemini itself for finding inconsistencies. Rough share of effort: about 10 to 20 percent, because the assistant tolerates messy sheets but answers get unreliable on merged cells and unlabelled columns. Related: DOC-014 and DATA-003.

Implementation paths

Which path for whom

• smb: Path A. Copilot in Excel, Gemini in Sheets or similar add-ins are mature, priced per seat and fit the standard Excel workflow.
• enterprise: Path A. Spreadsheet help is a commodity function; use the platform already licensed and govern it centrally.
• developer: Path B. Build agents or add-ins on cloud APIs with code execution so calculations are reproducible rather than guessed.
• private: Path A. Consumer assistants in Excel or Sheets cover household budgets and small analyses without any setup.
• public: Path B. Authorities should use APIs or assistants hosted in sovereign EU or German cloud for procurement and secrecy reasons; Path A only with certified products.

Path A: Buy (recommended for most SMBs)

• Microsoft 365 Copilot in Excel with Agent Mode (formulas, pivots, charts, multi-step edits; Purview sensitivity labels and audit apply) [1]. Best fit for Microsoft-centric organisations.
• Claude for Excel (Anthropic add-in; reads multiple tabs, explains formulas, edits with tracked changes) [2]. Check plan, data-retention terms and EU data-residency options before roll-out.
• Gemini in Google Sheets (included in eligible Workspace plans; generates formulas, tables and analysis from prompts) [3].
• ChatGPT with data analysis / file upload and add-ins for Excel or Sheets for ad-hoc work.
• Specialist tools: Julius AI, Rows, Numerous.ai, Ajelix, Formula-bot, SheetAI; EU-based: Rows (Portugal-founded), and spreadsheet-like analytics in Quadratic. Check hosting and DPA.
• Choose A when: staff already live in Excel or Sheets and the licence is affordable.

Path B: Build on cloud APIs

• Models: Claude, GPT, Gemini or Mistral (EU provider) via API in EU regions (Azure OpenAI EU Data Zone, Vertex AI EU, Amazon Bedrock Frankfurt, Mistral La Plateforme).
• Integration surfaces: Office Add-ins (Office.js) for Excel, Google Apps Script / Workspace Add-ons for Sheets, Microsoft Graph Excel REST API for server-side workbooks, openpyxl, pandas or xlwings in a sandbox. Model Context Protocol (MCP) servers for Excel files let any agent framework read and write workbooks.
• Pattern: function calling with a small tool set (describe_workbook, read_range, write_range, run_python), schema-constrained outputs, code execution in an isolated container.
• Choose B when: you need custom guardrails, company-specific functions or embedding in an internal tool.

Path C: Self-host (open source)

• Models: open-weight code and reasoning models such as Qwen3-Coder / Qwen3, DeepSeek, gpt-oss, Mistral Devstral / Mistral Small; served via vLLM or Ollama. Table-specialised research models exist, but general code models are usually the pragmatic choice.
• Stack: LibreOffice (headless) or Univer / OnlyOffice as the spreadsheet engine, pandas, polars, DuckDB for large tables, PandasAI or Open Interpreter-style sandboxes, Jupyter.
• Hardware: HW2 handles ~30B quantised models for formula and pandas generation; HW3 for better multi-step reasoning on messy workbooks.
• Choose C when: workbooks contain sensitive personal, health or trade-secret data that must not leave the premises.

Path D: Custom training

Rarely worthwhile. Fine-tuning a small code model on company-specific templates, named functions and naming conventions can cut cost for very high volumes. Data needed: a few thousand (request, workbook context, accepted edit) triples, harvested from accepted suggestions.

State of the art (2026)

• Agentic workbook editing is the standard product shape: planning, multi-range edits, tracked changes, undo [1][2][3].
• Benchmarks: SpreadsheetBench evaluates real-world spreadsheet manipulation tasks drawn from forum questions and scores the resulting workbook, not the text answer [4]. Vendors publish self-reported scores and independent runs differ; treat leaderboard numbers as directional and test on your own files. Realistic workbooks with messy layout, merged cells and multiple tabs remain much harder than clean tables.
• Techniques: table-to-structure encoding (headers plus samples instead of full dumps), code generation for heavy lifting, self-checking by recalculation, and retrieval of named ranges and documentation.
• Limits and failure modes: wrong assumptions about ambiguous headers or units; plausible but incorrect formulas; silent overwriting of hard-coded inputs; mishandled locale (decimal comma, German function names such as SUMMEWENN vs SUMIF); volatile or very large workbooks exceeding context or usage limits; hallucinated functions not available in the user's Excel version; weak handling of macros and external links. Models doing arithmetic "in their head" are unreliable.

Shared resources & combinations

• Same stack (llm-code + llm-reasoning + agent-framework): DATA-001 natural language to SQL and conversational BI, DATA-024 FP&A copilot, DATA-002 dashboard narratives. One LLM gateway and one sandboxed code runner serve all of them.
• Data cleaning: DATA-003 for deduplication and master data; DOC-014 to get tables out of PDFs into the sheet.
• Automation: AGENT-002 workflow engines trigger scheduled spreadsheet updates; AGENT-006 reconciliation and close agents often work on spreadsheets.

Effort, cost & KPIs

• Path A: days; per-user subscription (often an add-on to Microsoft 365 or Workspace). Main effort is licence selection, pilot group, training and policy.
• Path B: 1-3 months for an add-in with guardrails and logging; API cost scales with workbook size and number of tool calls per request.
• Path C: 1-3 months; one GPU server plus sandbox hardening.
• KPIs: time per recurring report, share of AI edits accepted without change, formula errors found per audit, number of workbooks with documented logic, user adoption, incidents of unreviewed AI changes (target zero).

People & change

Start with the department lead in finance or controlling and a handful of heavy Excel users; the owner or management sets the budget and approves the AI policy. IT handles licences and tenant settings, the DPO checks whether workbooks contain personal data and whether an AVV with the vendor exists (Art. 28 GDPR).

Works council: depends. Copilot-style assistants log user activity, and the Federal Labour Court holds that objective suitability for monitoring is enough, so §87(1) no. 6 BetrVG typically applies where a council exists; a works agreement should rule out performance evaluation. Inform the council under §90(1) no. 3 and use §80(3) if an AI expert is needed. Public bodies involve the Personalrat.

AI literacy: offer a hands-on workshop on prompting for spreadsheets and, above all, on verifying results; support staff with measures per Art. 4 AI Act. Name AI champions, run a four-week pilot, and track hours saved. Typical concern: job loss in controlling. Position the assistant as a way to remove routine work, and require that people check formulas and figures before use. Guides: GUIDE-008, GUIDE-006, GUIDE-004.

Risks, regulation & compliance

• EU AI Act: minimal risk for general analysis support; AI literacy duty (Art. 4) applies to staff using it. Risk rises if outputs feed Annex III decisions (e.g. a sheet scoring job applicants or creditworthiness): then the use case is classified by that downstream purpose, not by the tool.
• GDPR: workbooks often hold employee, customer or patient data. Need a data processing agreement, EU data residency where possible, no training on customer data, data minimisation (remove personal columns before upload), DPIA for large-scale HR or health data.
• Confidentiality and prompt injection: cell contents and imported data can contain instructions that an agent might follow. Restrict tools to the open workbook, block external calls, require confirmation for writes, and disable access to other files by default.
• Accuracy and accountability: financial reporting, tax and regulatory figures need review. German bookkeeping rules (GoBD) require traceable changes; keep versioned files and a log of AI edits.
• Mitigations: preview and undo, protected cells, independent recalculation of key totals, review checklist, sensitivity labels and DLP.

Getting started

1. Pick 5 recurring spreadsheet tasks (monthly report, cleaning an export, reconciling lists) and measure time today.
2. Check the data classification of those workbooks and choose the tool by data class (cloud assistant vs self-host).
3. Pilot one assistant from Path A with 5-10 users for four weeks, with change log enabled.
4. Create a short policy: what may be uploaded, mandatory review of figures, no AI edits to protected input cells.
5. Collect accepted prompts into a shared prompt library and review error cases monthly.

Sources

1. Microsoft Support / Learn: Copilot in Excel and Agent Mode: https://support.microsoft.com/en-us/copilot-excel
2. DataCamp (third-party), Claude in Excel add-in overview: https://www.datacamp.com/tutorial/claude-in-excel
3. Google Workspace: Gemini in Google Sheets: https://workspace.google.com/products/sheets/
4. SpreadsheetBench project: https://spreadsheetbench.github.io/
5. Regulation (EU) 2024/1689 (AI Act): https://eur-lex.europa.eu/eli/reg/2024/1689/oj
6. European Spreadsheet Risks Interest Group (EuSpRIG), horror stories and research: https://eusprig.org/research-info/horror-stories/
7. Microsoft Graph Excel API overview: https://learn.microsoft.com/en-us/graph/api/resources/excel
8. openpyxl documentation: https://openpyxl.readthedocs.io/