Another month, another folder of spreadsheets to combine. The copying looks simple; deciding which files count, what their numbers mean, and who checks the result often isn't. A useful first automation tackles one recurring task with clear boundaries. Excel can remain part of the solution, and AI is optional.
Why the manual work persists
People may be compensating for changing templates, undocumented exceptions, restricted access, or systems that don't fit together. Their judgment keeps the process moving. Before replacing it, ask the person doing the work what they check and why.
For a small, irregular, or high-risk task, a documented manual process may still be the best choice. Missing permissions, unstable requirements, or an unfunded maintenance burden can also rule out automation. Clearer instructions and better inputs are worthwhile improvements by themselves. New colleagues still need context and mentoring; input checks cannot teach every judgment the job requires.
Describe one monthly job precisely
Consider a fictional team combining September expense transactions for a supervisor's review. The first version produces a draft summary and an exception list. It sends nothing, initiates no payments, and changes no production records.
Its written specification names the operator, reviewer, approved input folder, and separate output location. It lists exactly three expected files: A_2026-09_v2.xlsx, B_2026-09_v1.xlsx, and C_2026-09_v1.xlsx. Only approved versions for September 2026 qualify; a newer-looking filename is not automatic approval.
One row represents one transaction, with a unique transaction ID, date, department, amount, currency, and tax basis. Preserve IDs as text, including leading zeros. Agree on column names, types, units, valid date formats, and review thresholds. A person resolves ambiguous dates or unexplained unit changes before any rule is implemented.
Make exceptions visible before adding totals
Suppose someone adds a copied version of file A, while file B loses its currency column. The process must flag the unexpected file and reject the malformed input with reasons. Silently skipping either problem would make an incomplete result look finished. A machine can apply the wrong rule impressively quickly.
Missing files, blank required values, and invalid dates mean missing information or errors, never zero. Hold wrong-period records for review. Matching dates and amounts do not prove duplication: two genuine transactions can match. Duplicate IDs require investigation, not automatic deletion.
Check the complete file list and count before processing. Keep currencies, units, and tax bases separate unless an approved conversion rule exists. Unresolved file-level errors block release of the monthly report.
Choose a tool the team can maintain
For explicit, repeatable calculations, consider formulas or a query before a language model. Power Query combines folder files sharing a schema. Check availability in the installed Excel version and license, and obtain the required IT approval.
Dropdowns and input checks can help people enter consistent data. Data-validation alerts may not appear for copied or filled data. Plan a separate check of imported values.
An approved AI tool could suggest test cases from an invented schema. Use synthetic examples; don't upload confidential company or personal records without authorization. Local processing isn't automatically safe, and pseudonyms don't guarantee anonymity. Treat generated code as untrusted: review it before any authorized sandbox testing, and never install or run it unreviewed.
Test the saved result against independent expectations
Use authorized copies in a non-production location, or synthetic data, while preserving untouched originals. Include normal records, a missing file, a duplicate file and ID, an invalid type, a wrong-period date, and a changed unit. Independently check the expected answers; neither the same calculation twice nor an unchecked manual result proves correctness.
Define two non-overlapping categories: accepted rows pass every rule; held rows require resolution. Every readable input row belongs to exactly one, so rows read must equal accepted plus held. Unreadable files remain separate blocking errors.
Reconcile amounts for accepted rows only, on the same currency, unit, and tax basis. Also check IDs, representative details, and boundary cases: offsetting errors can leave totals unchanged. Reopen the actual saved output and review it in a dry run, rather than trusting a tidy preview.
Make reruns and failures predictable
A second run must not append the same transactions again. Rebuild a separate output copy for the approved input set, retaining enough version information to identify it. Record source versions, rule version, run time, counts, and exception status, without unnecessary private data or secrets.
If a check fails, stop and mark the run unsuccessful. A previous valid report may remain available, but label its period and age clearly; never present it as the latest result. Document how to restore that version and when the approved manual fallback applies.
Before real use, an accountable owner should approve the input and output boundaries, minimum permissions, review steps, and recovery procedure. Assign maintenance and a backup person. Retest after changes to rules, templates, or tools. Security restrictions remain in force throughout.
Measure the whole job
Compare actual effort across runs, counting preparation, setup, checking, corrections, recovery, and maintenance alongside processing time. Track errors too. A faster import that creates harder reviews may offer little benefit.
Give the team time for documentation, training, and shared maintenance. Improvements should not depend on one person's hidden unpaid evenings. Start with a bounded process that colleagues understand and can recover when it fails. Expand only when the evidence supports it; a reliable partial automation may be enough.
