Document single-owner spreadsheets before automating

Signs this is happening in your business

What this is costing you a year

Document single-person spreadsheet before automation

A spreadsheet owned by one person is both a technical and operational risk. Treat it as a short discovery: capture the keeper’s actions, extract decision rules and produce testable artefacts so you can choose build, buy or hire without replacing one single point of failure with another.

• Red flags: single owner, last-minute fixes, manual joins of multiple exports, undocumented exceptions, magic-number formulas.
• If multiple people wait on the keeper rather than owning parts of the process, schedule a discovery.
• If stakeholders cannot state 'done' for the report in a single sentence, the sheet encodes tacit rules you must extract.

How to extract tacit rules from the keeper's spreadsheet

Extract rules by shadowing the keeper, running focused interviews, and reverse-engineering outputs. Do those steps in that order, keep the engagement short, and produce verifiable artefacts so decisions are testable rather than guessed during build.

• Shadow one full cycle to capture keystrokes, files opened, cells changed and manual fixes with timestamps.
• Run 30–45 minute structured interviews that convert observed actions into one-line conditional rules.
• Trace each important output to the minimal set of input columns and mark manual edits.
• Record data lineage: file names, query names, export formats and refresh cadence.

What to ask the keeper in interviews

Ask concrete, forced-choice questions that turn judgement into rules. Demand recent examples with timestamps and the exact condition used to decide so you capture edge cases, not only the happy path.

• Show me the last time you ignored a rule and explain why.
• List the inputs you always check and how you validate them.
• Where do you correct data by hand? Give three recent examples.
• Name every exception you handle and the exact condition you use to decide.
• If you were unavailable, what would the person taking over copy-and-paste from, step by step?

If you automate a broken spreadsheet you pay to preserve the mess, not to remove it.

A step-by-step checklist for extracting rules

Work from observable actions to documented rules and produce short, verifiable outputs at each step so the discovery can be accepted or rejected quickly.

• Schedule: a two-week window; shadow one cycle, then run interviews and a formula audit.
• Deliver a walkthrough log with timestamps and files touched.
• Produce a rule list of one-line conditional rules for every manual decision.
• Produce a field map linking outputs to source columns and the applied transformation.
• Capture sample files and anonymised historical data for validation.
• Produce a prioritized backlog: automated wins, rules that need clarification, and items requiring policy decisions.

Concrete outputs the discovery must produce for automation

The discovery stops being opinion when you hand a builder or vendor explicit, testable artefacts. Those artefacts must let someone run the same logic and reproduce the report without asking the keeper for tacit knowledge.

• Data model: one table per feed listing field name, type, example value and required/optional flag.
• Field mappings: spreadsheet linking each report column to its source column, file name and refresh cadence.
• Decision tables: each conditional written as 'When [condition] then [result]' with priority order for conflicts.
• Example inputs/outputs: anonymised CSVs showing raw inputs and expected cleaned output for representative rows and known edge cases.
• Test cases: reconciliation checks and acceptance tests runnable automatically and by a human reviewer.
• Acceptance criteria: core rows must match 100%; define acceptable variance for optional lines.

How to validate rules against historical data and live samples

Validation proves the documented rules match reality. Reconcile your rules against historical exports, back-test across prior cycles and run targeted fuzzing to surface gaps and false assumptions before you build.

• Back-test: run rules on the last 6–12 months of data and list mismatches by frequency and business impact.
• Edge-case fuzzing: inject malformed or rare examples—odd date formats, blank fields, unexpected codes—to observe rule behaviour.
• Reconciliation metrics: count mismatched rows, dollar/value differences and root-cause split between data quality and rule ambiguity.
• Live sample validation: run rules in parallel on the next cycle and have the keeper sign the reconciliation before cutover.

Operational risk lives in undocumented judgement, not in formulas.

Concrete reconciliation steps

Make reconciliation repeatable and objective: define core rows, run a diff that highlights the first divergent column, classify mismatches by root cause and require keeper sign-off on the reconciliation report.

• Define core rows that must match 100% because leadership acts on them.
• Define non-core rows with explicit tolerances and explain why differences are acceptable.
• Automate a diff that lists mismatched rows with the first column that diverges and the originating source.
• Produce a reconciliation report the keeper can validate within 48 hours; their sign-off moves the spec to build.

Scope this as a timeboxed discovery: roles, deliverables, timeline and cost

Treat this as a short consulting engagement with three roles, fixed deliverables and a one- to three-week timeline. Timeboxing keeps cost predictable and gives you the evidence required to decide whether to build, buy or hire.

• Roles: an ops lead from your side, the keeper who does the work, and a consultant to extract rules and produce artefacts.
• Deliverables: walkthrough log, rule list, field map, decision tables, example I/O, test cases, reconciliation report and acceptance criteria.
• Typical timeline: 1–3 weeks depending on complexity and file availability.
• Expect a one-time consultancy fee that fits inside a small operations budget and prevents much larger build costs later.

How this fails in practice and the sequence that kills projects

The common failure sequence is familiar: skip discovery, build on assumptions, exceptions appear in production, the keeper patches the automation, and the organisation ends up with brittle automation that still needs the keeper. Prevent that by insisting on reconciliation and keeper sign-off first.

• Failure sequence: skip discovery → build on assumptions → automation misclassifies exceptions → keeper patches → automation becomes brittle.
• Who notices first: downstream users or finance; their complaints trigger costly fire-drills.
• Avoid it by reconciling against historical data and requiring keeper sign-off on the reconciliation report before build.

A signed reconciliation report is the only acceptable handoff before any build begins.

Handover and governance to avoid a new single point of failure

Handover is a set of artefacts and rules: assign an operations owner, publish decision tables with a short changelog, set monitoring thresholds, and provide a one-page playbook for failures. Make the ops owner responsible for maintaining rules, not the developer.

• Monitoring: a daily reconciliation check that flags row-level mismatches above an agreed threshold to a monitored queue.
• Alerts: a single message to the ops owner when reconciliation fails, including the exact test case that failed.
• Playbook: isolate the data problem, run the reconciliation script, apply a documented override if necessary, and log the change with reason and timestamp.
• Change control: any change to a decision table requires a two-person review (ops owner + stakeholder) and a test on a sample month before acceptance.

Decide: build, buy or hire after the discovery

Use the discovery outputs to make a rational choice. If rules are stable, data predictable and scope small, build. If rules are volatile and you need vendor SLAs, buy. If ongoing judgement calls are frequent, hire to increase internal capacity.

• Build: pick this when you have clear rules, predictable data and an available developer; keep the ops owner as spec author.
• Buy: pick this when the domain has many edge cases and you value vendor support and SLAs.
• Hire: pick this when frequent judgement calls mean ongoing human capacity is the priority.

Next step: run a Single-Sheet Discovery

If you recognise the symptoms, book a short scoping call to run a fixed-scope discovery that produces an automation-ready spec and a reconciliation report. That call sets the budget and timeline and gives you the evidence to decide build, buy or hire.

I offer a focused discovery engagement that produces the deliverables listed here and a recommendation for next steps. See services, examples in the portfolio and how I work on how-can-i-work-with-you.

• After the discovery you will have decision tables, a field map, example inputs/outputs, test cases and acceptance criteria you can hand to any vendor or developer.
• You will learn the true maintenance cost, the frequency of exceptions, and receive a recommendation on whether to build, buy or hire.

FAQ

Practical follow-ups people ask after reading the spec and doing a quick audit. These answers give direct next steps: timeline, handling an uncooperative keeper, and what acceptance looks like.

Where businesses waste the most time

1. Re-keying exportsSeveral hours per cycle and frequent typos that require reworkDocument the export format and automate the transfer so the same files are reused rather than copiedA person still checks: A person spot-checks the first run each month
2. Manual correctionsHidden judgement scattered across many cells and months of undocumented fixesCapture each correction as a decision rule in a decision table instead of changing cells in placeA person still checks: Ops owner approves exceptions listed in the change log
3. Last-minute reshapingUnplanned late work, extra hours, and downstream confusionFreeze the output schema for a cycle and document any requested changes with a test caseA person still checks: Stakeholder signs off on schema changes before they are applied

How this compares with what you already run

Operational area	The manual way	The automated way	Annual business impact
Monthly reporting	Keeper runs, fixes by hand	Rules run reproducibly with reconciliation	Lower annual labour hours
Exception handling	Keeper decides ad hoc	Decision table with logged overrides	Fewer late-night interventions annually
Data collection	Multiple exports copied manually	Defined sources and automated pulls	Reduced annual hours on data assembly
Change management	Format or rule changes are verbal	Logged change requests with tests	Lower annual audit and rollback costs
Onboarding	New hires watch the keeper	Handover docs and test suites	Faster ramp; fewer training hours yearly

What the end-to-end workflow looks like

The metrics that tell you it worked

Metric	What it tells you
Hours saved per cycle	Total team hours no longer spent on assembly and manual fixes; shows direct payroll impact
Reconciliation pass rate	Percentage of core rows that match exactly between keeper reports and automated outputs; measures correctness
Exception frequency	Number of flagged exceptions per cycle; indicates rule completeness
Time to resolve exception	Average time from exception alert to documented resolution; shows operational burden
Onboarding time for new analyst	Days to full competency using the documented process and tests; shows quality of knowledge transfer

When this is the wrong move

The mistakes that cost the most

• Skipping shadowingTeams skip watching the keeper work, invent rules from the final sheet, then build automation that misses the human fixes the keeper always makes.
• Documenting only happy-pathThe discovery records the textbook path but ignores the dozen exceptions handled monthly; the automation fails on live data.
• Subjective acceptanceAcceptance is 'looks right' instead of reconciliation metrics; build proceeds on opinion and breaks when exceptions appear.
• Treating the keeper as an obstacleManagers demand a handover without time to explain edge cases; the keeper stops cooperating and defects climb.
• No reconciliation in productionTeams deploy automation without daily reconciliation checks; small errors compound until the report is unreliable.

Frequently asked questions