Almost every office runs on Excel or Google Sheets, and almost every employee knows what they want to calculate but not the syntax. An AI assistant turns "sum the net amounts of customer X in Q2, but only open invoices" into a working formula, explains a colleague's 400-character nested IF, and names the cause of an error. It is probably the most common single AI micro-task in SMB offices, needs no project and no data preparation beyond a few good habits.
Problem & value
- Today: staff search forums, copy formulas they do not understand, or ask the one "Excel person" in the office. Inherited workbooks (pricing sheets, controlling files, DATEV exports) contain formulas nobody can explain, which is a business risk when the author leaves.
- AI value: faster building of lookups (XLOOKUP/SVERWEIS), conditional sums (SUMIFS/SUMMEWENNS), date logic and text handling; understanding and documenting old formulas; fewer dependencies on single experts.
- Important boundary: AI writes the formula, it does not verify the business logic. Wrong ranges, wrong joins and silent rounding issues look correct. Calculations that feed invoices, payroll or tax need a test case with a known result.
Variants
- Build: formula from description (Copilot in Excel, Gemini in Sheets, or a chat assistant).
- Explain: select a cell, ask "explain this formula step by step", optionally generate a comment or documentation sheet.
- Debug: paste the formula plus the error; AI proposes the fix.
- Cell-level AI: Excel's COPILOT function classifies or summarises text per cell. Microsoft describes it as not suited to exact, reproducible calculations and documents a limit of 100 calculations per 10 minutes and a #BLOCKED result in workbooks labelled Confidential or Highly Confidential [1].
How it works
- user describes goal (+ column names / example rows)
- assistant (in-app side panel or separate chat) proposes formula + short explanation
- user inserts into a copy of the workbook, tests with known values
- user accepts, edits or asks for a variant
- optional: explanation stored as cell comment / "Documentation" sheet
- Human in the loop: the user always checks the result on at least one row they can calculate by hand; formulas for financial or legal outputs get a second pair of eyes.
- Locale trap: German Excel uses
SUMMEWENN, semicolons and decimal commas. State your language version in the prompt, otherwise a pasted English formula fails.
Data preparation
Which data: none is required. The assistant needs the structure (column headers, data types, an example of the expected result), not the real rows.
Typical gaps: merged cells, header rows in the middle of the sheet, numbers stored as text, mixed date formats, empty rows, and no named ranges or tables, so the assistant (and humans) misread the structure.
Preparation steps: 1. Turn data ranges into Excel Tables or named ranges so formulas are readable and the in-app assistant recognises the structure. 2. Replace real values with invented example rows when using an external chat tool. 3. Mark sensitive workbooks with a sensitivity label so AI features can be blocked on them by policy.
Rough share of effort: low, a few hours of habit-building rather than a data project. See GUIDE-003 for the general readiness check.
Implementation paths
Which path for whom
- SMB — Path A: the assistant is built into tools staff already have, the process is standard and no integration is needed.
- Enterprise — Path A: a commodity function; use the licences you already run for Microsoft 365 or Google Workspace and govern by labels and policy.
- Private — Path A: a free or low-cost chat assistant, or a local app such as LM Studio or Ollama if the sheet is personal and sensitive.
Path A — Buy (recommended)
- Microsoft 365 Copilot in Excel (side panel, formula suggestions, COPILOT function). For EU tenants Copilot is covered by the Microsoft EU Data Boundary; check the Flex Routing setting in the admin centre, which can send processing outside it [3]. Requires a Copilot licence.
- Gemini in Google Sheets ("Ask Gemini": create formulas, explain, insert). Google presents formula help in Sheets as a Gemini feature; check current licence terms for your edition [2]. Workspace data-region settings apply.
- Chat assistants without a licence: Claude, ChatGPT, Mistral Le Chat (EU provider, France). Paste only structure and invented examples, never customer or employee data, and use a business plan with an AVV (Art. 28 GDPR).
- Do not buy 100 Copilot seats for this use case alone; combine with AUTO-002 to AUTO-006.
Path B — Build on cloud APIs
Rarely sensible; an internal formula helper via n8n or Power Automate and an EU-hosted API takes days and adds little over Path A.
Path C — Self-host (open source)
Open-weight code-capable models (e.g. Qwen-Coder family, Mistral/Codestral-class) served with Ollama or LM Studio handle common formulas on a laptop or an HW1 machine; HW1 is enough for small models. Sensible for a fully offline policy or highly confidential sheets (HR, M&A, pricing). Quality drops on complex, long formulas, and no in-app sheet context is available, so users paste structure manually.
Path D — Custom training
Not worthwhile.
State of the art (2026)
- Frontier chat models write correct standard formulas (lookup, conditional sums, dynamic arrays such as FILTER, SORT, LET) most of the time; in-app assistants now work directly on the open workbook.
- Failure modes: invented functions that do not exist in your version, wrong range sizes, English function names in a German Excel, silent type mismatches, and over-complicated solutions where a pivot table or Power Query step is the right tool (see AUTO-019).
- Non-determinism: the same prompt can yield different formulas, and Microsoft states that COPILOT-function results can change over time [1]. Once a formula is accepted it is deterministic; this is the main argument for letting AI write formulas rather than compute values.
Shared resources & combinations
- Same capability set: AUTO-002, AUTO-003, AUTO-005, AUTO-006. Next level: DATA-006, AUTO-017 (macros), CODE-001.
- Baukasten effect: one Copilot or Gemini rollout plus a short prompt library (AUTO-023) serves all of these.
Effort, cost & KPIs
- Path A: hours to set up (licences, labels, policy); one workshop of 60 to 90 minutes for users. Cost is mainly seat licences; check current list prices with your reseller.
- Path C: one to two days to install a local runtime and test models.
- KPIs: time to finished formula in a standard test of five tasks (before/after), number of formula errors found in monthly sheet reviews, number of undocumented critical workbooks that got explanations, share of users who test against a known value.
People & change
Involve early: management (policy and licence budget), IT (licences, sensitivity labels), the DPO (what may be pasted where) and the end users. Add department leads who own critical workbooks (controlling, finance) (GUIDE-007).
Works council: depends. A pure chat helper that does not log individual use rarely triggers co-determination, but Microsoft 365 Copilot and similar integrated tools log user activity and are objectively suited to monitor behaviour or performance, which the BAG treats as sufficient for §87(1) no. 6 BetrVG. §90(1) no. 3 requires timely information when work processes are planned with AI. Public bodies involve the Personalrat. Settle usage-log access in a short works agreement (GUIDE-004).
Data protection: formulas themselves contain no personal data, but sheets often do (customers, salaries). Keep a rule "structure, not rows", sign an AVV with the provider, and check whether a DPIA is needed once Copilot gets access to all workbooks (GUIDE-005).
AI literacy: Art. 4 AI Act requires measures that support staff's AI literacy. A 90-minute session on prompting for formulas, testing results and the "structure, not rows" rule covers this use case (GUIDE-006).
Communication and pilot: start with five to ten Excel-heavy staff in controlling, finance and sales admin for four weeks; name an AI champion who collects good prompts. Position it as a tutor that also explains, not as a replacement for the Excel expert (GUIDE-008, GUIDE-009).
Typical concerns: losing Excel skills (use the explain function to learn) and monitoring through usage logs. The user owns the result.
Risks, regulation & compliance
- EU AI Act: minimal risk; no Annex III use. Only the AI literacy duty (Art. 4) applies. Transparency duties under Art. 50 are not triggered by formula help (GUIDE-014).
- GDPR: depends on what is pasted. Never paste personal data into consumer chat tools; use business plans with an AVV and EU data residency where available.
- Bookkeeping: formulas that produce accounting figures fall under GoBD traceability; keep the final formula and its documentation, not the chat, as evidence.
- Mitigations: test cases, sensitivity labels, copy-before-change, version history on SharePoint/Drive.
Getting started
- Pick the approved tool (the Copilot or Gemini licence you have, or a business chat plan) and write a half-page rule: structure and invented examples only.
- Ask the AI to explain the three most critical inherited workbooks and save the explanation as a documentation sheet.
- Collect good prompts in a shared page (AUTO-023) and review after four weeks.
- Decide on wider licences together with the other AUTO office use cases (GUIDE-001, GUIDE-002).
Sources
- Microsoft Support — COPILOT function (Excel) — https://support.microsoft.com/en-us/excel/functions/copilot-function
- Google Workspace — Spreadsheet AI with Gemini in Google Sheets — https://workspace.google.com/resources/spreadsheet-ai/
- Microsoft Learn — Data, privacy and security for Microsoft 365 Copilot — https://learn.microsoft.com/copilot/microsoft-365/microsoft-365-copilot-privacy
- Regulation (EU) 2024/1689 (AI Act) — https://eur-lex.europa.eu/eli/reg/2024/1689/oj