Five passes every messy spreadsheet needs.

A canonical key is the one row-identity you commit to — SKU plus date, invoice number plus line item, customer plus month. Every variant in the file gets folded back to that key before anything else happens. Two SKUs that look almost the same probably are the same product with a typo; two invoice numbers with different casing are the same invoice. Fold them once, here, and the rest of the cleanup is straightforward. Without this pass, totals silently double-count and downstream formulas lie about which customer shipped which order.

Pick a convention and apply it column by column. ISO dates (2026-07-12), USD with two decimal places, trimmed whitespace, sentence-case channel names. Don't accept 'we have a few formats' — accept one and rewrite. The file should look like one person wrote it on one day, even if it took a week and three exports. A consistent format is what makes the next formulas correct, and what makes the file readable in five minutes instead of fifty. The pass is mechanical; the payoff is permanent.

Clawbacks and fees need one sign convention — negative for money leaving, positive for money arriving — and the rule written down at the top of the sheet. Currency conversions need a stated FX rule ('all amounts in USD as of last business day of the month') so the next person can defend the number. Without these rules, totals silently cancel and reconciliation becomes theatre. With them, a reviewer can read one line at the top and trust every row below it.

Replace #REF!, ?, —, N/A, and TBD with placeholders that say what should go there. Repopulate totals so they tie to the source rows. Merge multi-tab files into one continuous range so quarter-to-date comparisons don't break at the sheet boundary. The pass that closes the file is the pass that lets the next person open it without a swarm of red flags. This is also where the cleanup gets reviewable — every cell that changed sits in row N, with a one-line note explaining the rule that was applied.