Last week I wrote about advances — the earn-out math, the transition month, the cumulative tracker that keeps the whole thing from drifting. This week I want to talk about something less complex but somehow more irritating: the morning you sit down to do your reconciliation and discover that a distributor has changed their report format. Again.
You know the moment. You've got your rhythm. Maybe it's the monthly session I described back in the first post — one day, all the reports, download everything, update the spreadsheet, get your number. You've done this enough times that it's routine now. You open the latest CSV from a distributor, paste it into your raw data tab, and immediately something's wrong. The columns don't line up. There's a new field you've never seen. The currency column that used to say "USD" now says "US Dollar." Your VLOOKUP returns an error. The formula you built three months ago and haven't thought about since is suddenly broken.
And now you're not doing royalty work. You're doing detective work — reverse-engineering what changed before you can even start the actual task you sat down to do.
The frustration isn't that it's hard. It's that you already solved this. You built the spreadsheet, you got the formulas working, it was handling your data correctly — and now you're back at square one through no fault of your own.
Why this keeps happening
Distributors update their systems. They add features, change accounting methods, comply with new tax regulations, merge internal platforms. When they push an update, the report format is a side effect — not the product. They don't send you a changelog because they don't think of you — the small publisher with a spreadsheet — as their audience for change management. The report is a byproduct of their system, not a product designed for your workflow.
KDP has changed column headers and added fields over the years. IngramSpark has restructured how returns appear. ACX has adjusted what shows up in the "transaction type" column. Findaway has shifted field names after platform migrations. These aren't bugs. They're updates that nobody told you about, because from the distributor's perspective, the report still works. It just doesn't work the way your spreadsheet expected it to.
And the timing is always bad. Mid-quarter. Mid-reconciliation. Right when you're trying to close out a period and generate statements for your royalty holders.
The cascade problem
Here's what makes format changes more disruptive than they seem: it's rarely just one tab that breaks.
If you built the advance tracker I described last week, it pulls from your earnings data. When the earnings tab breaks because a column shifted, the advance tracker breaks too. Your royalty split calculations, if they reference specific cells in the imported data, break. Your statement totals break. One format change in one downloaded CSV cascades through every downstream tab you've built.
This is the maintenance tax on a manual system. Not the building of it — the upkeep. Every formula that references a specific column by position is a fragile link. And the more sophisticated your spreadsheet becomes, the more links there are to break.
Building a spreadsheet that expects change
You can't prevent distributors from changing their formats. But you can build your spreadsheet so that when it happens, the fix takes fifteen minutes instead of an afternoon. Four specific techniques.
Use MATCH-based lookups instead of fixed column references. If your formula says =B2 for gross earnings, it breaks the moment the distributor inserts a new column and gross moves to C. Instead, use INDEX/MATCH to find the column by its header label. Something like =INDEX(2:2, MATCH("Gross", 1:1, 0)) — the formula looks for the column called "Gross" regardless of where it sits. When the distributor rearranges things, the formula still finds it. This single change eliminates the most common category of breakage.
Keep a "raw import" tab and a "processed" tab. Never modify the downloaded data directly. Paste it into a raw tab exactly as it came from the distributor. Your processed tab pulls from the raw tab using formulas. When the format changes, you only need to update the processed tab's references — the raw data is untouched, and you can compare this month's raw import to last month's side by side to see exactly what moved. No guessing, no detective work.
Document your column map. At the top of each raw import tab — or in a small reference tab — write down which column is which: "Column A = Transaction Date, Column B = Title, Column C = Units Sold, Column D = Gross Earnings." When a format change hits, compare your documented map to the new layout. You'll see exactly what moved, what was added, and what was renamed in about two minutes. Without the map, you're reconstructing from memory, and memory is unreliable when you haven't looked at column positions in three months.
Use named ranges or table references. If your spreadsheet supports structured table references — and both Excel and Google Sheets do — use them. Earnings[Gross] survives a column insertion. =C2 doesn't. Named ranges take five minutes to set up and save you hours over the life of the spreadsheet.
The goal isn't to make your spreadsheet unbreakable. It's to make format changes a fifteen-minute remapping exercise instead of an afternoon of debugging. You're converting a surprise into a short checklist: compare the new format to your column map, update the references that changed, verify the downstream totals still add up, and move on with your day.
How Royaltally handles this part
This is one of the things I built Royaltally to solve, because it's one of the things a spreadsheet genuinely can't solve well on its own.
Royaltally's parsers are maintained centrally. When a distributor changes their report format, the parser gets updated once — on my end — and every user benefits. You don't find out about the change by watching your formulas break. You don't find out about it at all. You upload the report the same way you always do, and the system reads it correctly, because absorbing format changes is the parser's job.
This is hard to replicate in a spreadsheet because it requires someone to be watching every distributor's report format continuously and updating the logic when something shifts. That's sustainable for a maintained system. It's not sustainable for one publisher doing this between everything else.
Royaltally is in open beta right now — week seven. You can try it free with up to 3 titles at royaltally.com.
The unglamorous work that keeps everything running
Format changes aren't complex like advance tracking. They're not emotionally loaded like sending statements. They're just annoying — the kind of small, unpredictable disruption that makes you question whether all the spreadsheet work is worth maintaining on a bad afternoon.
But every system needs maintenance, whether it's a spreadsheet or software or a filing cabinet. The difference is whether you built for it. A spreadsheet that assumes the data will always arrive the same way is a spreadsheet waiting to break. One that uses flexible lookups, keeps raw data separate, and documents its own structure — that's a system that can absorb surprises and keep working.
Build for change. Label everything. And the next time a distributor quietly rearranges their columns, you'll fix it in fifteen minutes and get back to the work that actually matters.
Next week: what happens when you inherit someone else's publishing business — and their books. Where do you even start?