Catalog / Everyday Automation & AI Micro-Tools / AUTO-006

Ad-hoc spreadsheet tidying (split/merge columns, normalise formats, AI fill)

Clean a single list or export in minutes - split names and addresses, fix dates and numbers, standardise spellings, fill categories by example - for office staff in SMBs.

Quick win: proven, manageable effort, first results within weeks, low regulatory risk.

Parts needed to run it yourselfAgents & automationLanguage model

Almost every office job starts with a messy list: an export from the shop system, a customer file from a trade fair, a DATEV export with odd date formats. Instead of fighting with formulas, staff describe the result or show two or three examples, and the AI built into the spreadsheet (or a deterministic tool such as Flash Fill or Power Query, with AI helping to build it) does the clean-up. This is a minutes-long desktop job for one file, not master-data management and not systematic data preparation for AI projects.

Problem & value

  • Today: staff split "Müller, Anna" into two columns by hand, retype dates (31.12.26 vs. 2026-12-31), fix "Str." vs. "Straße", and sort items into categories by eye. A list of 500 rows can eat an hour or more and is error-prone.
  • AI value: the same job in minutes, fewer typos, and often a reusable recipe (formula or Power Query steps) for next month's export.
  • Honest limit: AI fill is probabilistic. For rules that are exactly describable (split at comma, convert date), a deterministic tool is safer than a language model.
  • No sourced percentage figure is quoted here; measure your own baseline (see KPIs).

Variants

  • Deterministic helpers (no generative AI): Flash Fill, Text to Columns, Find & Replace, Power Query.
  • In-sheet AI: Copilot in Excel ("Clean data", agent-style multi-step edits, COPILOT function for per-row classification) and Gemini in Google Sheets.
  • Chat on an export: paste a few rows into an approved assistant and ask for a formula or script (see AUTO-001, AUTO-017).
  • Private use: tidying a club member list, a household budget export or a photo-book address list.

How it works

messy list (xlsx/csv)
  -> choose method: rule exactly known? -> Flash Fill / Power Query / formula
                    fuzzy (spellings, categories)? -> AI fill by example
  -> describe goal or give 3-5 example rows
  -> AI proposes the change (preview, formula or applied steps)
  -> spot check on 20 random rows + totals/row count unchanged
  -> keep recipe (query steps / formula) for reuse, save as new file
  • Flash Fill learns a pattern from your typed examples and runs locally in Excel.
  • Power Query records every step, so the clean-up is repeatable; AI can help write the steps (AUTO-019).
  • Generative fill (Copilot/Gemini or a local model) handles fuzzy cases like "Meier GmbH" vs. "Maier Gmbh" or assigning categories.
  • Human-in-the-loop: always check a sample, compare row counts and sums before and after, and never overwrite the original.

Data preparation

Which data: the single list to be cleaned, plus 3-5 hand-corrected example rows and, for categorisation, the list of allowed categories.

Where it lives: Excel files on SharePoint/OneDrive or a file share, CSV exports from DATEV, ERP, shop or CRM, Google Sheets.

Typical gaps: mixed date and decimal formats (comma vs. point), text stored as numbers, merged cells, trailing spaces, several values in one cell, inconsistent spellings, personal data in columns nobody needs.

Preparation steps: 1. Work on a copy; keep the original export untouched. 2. Delete columns you do not need, especially personal data (data minimisation). 3. Turn the range into an Excel table with one header row and no merged cells. 4. Write down the target format (date, decimal separator, allowed categories) and hand-correct 3-5 rows as examples. 5. After the run, compare row counts and sums, and spot-check a sample.

Tools: Excel, Google Sheets, Power Query, a text editor for CSV checks. For larger or recurring preparation see PREP-005 and GUIDE-003.

Rough share of effort: low; the preparation is the task itself and typically takes minutes to a couple of hours for the first recipe.

Implementation paths

Which path for whom

  • SMB - Path A: the tools already exist in Microsoft 365 or Google Workspace, the process is standard and no integration is needed.
  • Private - Path C: local apps (LM Studio, Ollama with a small model) clean a private list without uploading it anywhere.

Path A - Buy (off-the-shelf)

  • Microsoft 365: Flash Fill and Power Query are included in Excel; Copilot in Excel (separate Copilot licence) offers AI-assisted clean-up and agent-style edits. Feature names and rollout dates change quickly, so verify in your tenant before planning [1][2].
  • Google Workspace: Gemini in Sheets, plus built-in "Data cleanup" suggestions. Data-region and AVV settings are admin-controlled [3].
  • EU-hostable alternatives: Nextcloud with OnlyOffice/Collabora and a local AI app; LibreOffice Calc; OpenRefine (open source, runs locally) for fuzzy-matching clustering of spellings [4].
  • Choose A when: staff already have the licence and the list contains no highly sensitive data, or the tenant uses an EU data boundary.
  • Cost: Copilot is a per-user licence; check current list prices with your reseller. Flash Fill and Power Query cost nothing extra.

Path B - Build on cloud APIs

Only when the same clean-up runs every week on a fixed export: an n8n or Power Automate flow takes the file, calls an EU-hosted model API (e.g. Mistral, Azure OpenAI EU region) for fuzzy steps and writes the result back. Effort: 1-3 days per flow. Usually Power Query is enough and cheaper; AI is added only for the fuzzy columns.

Path C - Self-host (open source)

  • OpenRefine for clustering and normalising text, no AI model needed.
  • Ollama or LM Studio with a small open-weight model (about 7-14B, quantised) for categorisation and address splitting; runs on a laptop or HW1/HW2 machine.
  • n8n self-hosted to batch rows through the local model.
  • Effort: half a day to set up; best when the lists are customer or HR data that must not leave the company.

Path D - Custom training

Not sensible. A handful of examples in the prompt replaces any training.

State of the art (2026)

  • Spreadsheet assistants now plan and execute multi-step edits and can run per-cell AI functions, with preview before applying; availability depends on licence and tenant rollout [2].
  • Small local models handle splitting and normalising well; they are weaker on rare categories and mixed languages.
  • Failure modes: silently "correcting" valid values, inventing missing data, altering numbers, locale mix-ups (day/month order), and per-cell AI results that change between runs. Mitigate by using deterministic steps wherever a rule exists, checking totals and logging what was changed.

Shared resources & combinations

  • Same capability (office-suite-ai): AUTO-001 formulas, AUTO-002 mail, AUTO-005 document comparison.
  • Scripts and queries: AUTO-017 macros, AUTO-018 Python/PowerShell, AUTO-019 Power Query, AUTO-021 regex and validation rules.
  • Escalation: when the same clean-up recurs across systems, move to DATA-003 (master data) or the PREP family.
  • A local Ollama server (HW1/HW2) can serve this and other small text tasks (Baukasten effect).

Effort, cost & KPIs

  • Path A: hours to set up; one short training session (30-60 minutes).
  • Path B: 1-3 days per recurring flow plus API cost per row (usually small).
  • Path C: about half a day for the local setup plus occasional updates.
  • KPIs: minutes per list before/after, error rate in a 20-row sample, share of lists with a reusable recipe, number of manual re-corrections.

People & change

Involve early: end users and their department lead decide which lists are tackled first; IT checks licences, tenant settings and which AI tools are approved (GUIDE-007); the DPO is consulted when lists contain customer, employee or applicant data (GUIDE-005).

Works council: depends. A purely local clean-up tool does not monitor anyone, but Copilot or Gemini log prompts and usage, and the BAG treats objective suitability for monitoring as enough for co-determination under §87(1) no. 6 BetrVG. §90(1) no. 3 also requires timely information when AI-supported work processes are planned. A short works agreement on the AI assistants usually covers this use case (GUIDE-004). Public bodies involve the Personalrat.

AI literacy: Art. 4 AI Act requires measures that support staff's AI literacy; a 60-minute session on "check samples, never overwrite the original, no confidential data in unapproved tools" is enough to start (GUIDE-006).

Communication and pilot: pick 3-5 curious power users from accounting or sales, collect their best prompts, and share them as recipes; position it as relief from tedious copy-and-paste (GUIDE-008, GUIDE-009).

Typical concerns: fear of wrong data (sample checks), fear of control (no individual evaluation of prompts), uncertainty about what may be pasted (policy).

Humans in control: the user reviews every result; nothing is written back into ERP or accounting without a check.

Risks, regulation & compliance

  • EU AI Act: minimal risk; no Annex III use. Art. 4 literacy measures apply. Art. 50 transparency duties do not apply to internal data clean-up [5][6].
  • GDPR: lists often contain personal data. Use only approved tools with an AVV (Art. 28), prefer EU data boundary or local models, remove unneeded columns first. A DPIA is normally not needed for one-off clean-ups of ordinary contact data; involve the DPO for special categories or large-scale HR data (GUIDE-005).
  • Shadow AI: pasting customer lists into private chatbot accounts is the main risk; offer an approved alternative (GUIDE-007).
  • Data quality: wrong auto-corrections that reach invoices or mailings; keep originals and a change log.

Getting started

  1. Pick one recurring annoying list (e.g. the monthly customer export) and copy it.
  2. Try deterministic tools first: Flash Fill, Text to Columns, Power Query.
  3. For fuzzy columns, give 3-5 examples to the approved AI assistant and review a 20-row sample.
  4. Save the recipe (query steps, formula or prompt) in a shared prompt library (AUTO-023).
  5. Write a one-page rule: which data may go into which tool (GUIDE-007).

Sources

  1. Microsoft Support - Flash Fill and Power Query in Excel - https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
  2. ICAEW - Excel community: new technology features Dec-Feb 2026 (Copilot Clean data, agent mode) - https://www.icaew.com/technical/technology/excel-community/excel-community-articles/2026/new-tech-features-dec-feb-26
  3. Google Workspace Learning Center - Gemini in Google Sheets - https://workspace.google.com/products/sheets/
  4. OpenRefine project - https://openrefine.org/
  5. Regulation (EU) 2024/1689 (AI Act) - https://eur-lex.europa.eu/eli/reg/2024/1689/oj
  6. Regulation (EU) 2016/679 (GDPR) - https://eur-lex.europa.eu/eli/reg/2016/679/oj