Whole stages of the path exist because of how data arrives: cleaning routines for the exports, validation for the typists, duplicate hunts for the copy-pasters. All downstream repairs. This lesson is the upstream fix: when the data comes from people — sign-ups, requests, audits, returns — stop circulating a spreadsheet and put a form in front of them.
The emailed-workbook ritual fails the same way everywhere: twelve copies come back (three renamed, one re-sorted into scramble, one with a helpfully added column), and someone consolidates by hand. The people weren’t the problem — the interface was. A spreadsheet says “here are ten thousand cells, behave”; a form asks one question at a time and refuses wrong answers.
The setup, in ten minutes
Microsoft Forms lives at forms.microsoft.com in most 365 licences. Build the questions, then apply the only rule that matters: constrain everything you’ll ever want to count, sum or group by.
- Department, site, category → Choice — no
"Finanace", nodubvsDublin; categories arrive drop-down clean because the typo was never possible. - Quantities, costs → Number with a restriction (between 0 and 1,000) — no text-numbers, no “about 5”.
- Dates → the date picker — no
07/03ambiguity, no costume dates. - Free text → the one Long Answer box every form deserves, kept for the genuinely unstructured.
The instinct you built cleaning data tells you exactly what to lock down: every field you’ve ever had to repair is a field the form should constrain.
The magic half: where responses land
Create the form from inside Excel — a workbook on
OneDrive/SharePoint, Insert → Forms → New Form (or start in
Forms and choose “Open in Excel” on the Responses tab, keeping
the sync to workbook option). Every submission now lands, live,
as a new row in a real
Table
— timestamped, typed, and growing downward forever.
Downstream, everything you’ve built just works: a PivotTable on the response Table with a slicer, refreshed as answers arrive; FILTER views of today’s submissions; a dashboard fed by a form. Add a flow if someone should be pinged when a response needs action.
The honest edges
Where forms are the wrong tool: updating existing records — forms append, they don’t edit (correction workflows need the shared-workbook or proper-app world); grids where respondents must see each other’s rows (that’s a shared workbook with protection); and anything beyond ~10k responses or needing approval chains — by then you’ve outgrown into Microsoft Lists or a real database, the graduation path is normal. Also: the synced Table is append-only plumbing — don’t insert columns inside it (add your calculated columns to its right, or better, in a query), and treat the Form’s question wording as a schema: renaming questions renames columns, with downstream consequences.
The mindset shift is the real lesson. Every messy dataset was once a collection moment that could have been designed. You now know both halves — the downstream repairs and the upstream prevention — and the professional instinct is always the same: push cleanliness as far upstream as it will go. The cheapest data to clean is the data that never got dirty.