The Spreadsheet Beside the System, Read Column by Column

Every ERP and treasury system I have worked around had a spreadsheet next to it. The textbook response is to treat that sheet as a problem: shadow IT, a control risk, something the next phase of the programme will retire. Sometimes it is. More often it is the most honest document in the room, because it records exactly what the system does not know and what people had to do about it.

The useful way to read it is not as a file to replace but as a list of columns, each of which exists for a reason. Somebody added every one of them on a day when the system was not enough. The header row is a list of those days.

This piece is a way of reading that header row. It is the first form in the hidden work around enterprise systems — the spreadsheet that compensates for missing context — taken one level down, to the column.

Why columns, not the sheet

A spreadsheet beside a system is not one thing. The AP approval tracker on a shared drive might hold the invoice number, the vendor and the amount, which are already in the ERP; a "real due date", because the one in the ERP is wrong for some suppliers; an approver, because the workflow does not say who signs above a limit; an "OK to pay?" column someone ticks after a phone call; and a notes column full of sentences that begin "waiting for".

Those columns want four different fixes. Replace the sheet in one move and the project rebuilds the columns that were easy to see — usually the ones the system already had — and loses the rest. The approver, the corrected date and the notes come back a month later as a new sheet.

Eight kinds of column

The same few kinds turn up in very different sheets: a payment release tracker, a reconciliation queue, a vendor master change log, a month-end checklist. Each has a tell in its name or its values.

Missing field. The system should hold this and does not, or holds it wrong. The tell is a name with real, actual, corrected, manual or revised in it ("Real due date", "Manual amount"), an owner or approver column, or a fact the system should have recorded when it happened ("Bank confirmed", "Contract signed"). The fix is a field, or a correction at the source.

Unwritten rule. A condition worked out by hand on every row: a threshold, a tolerance, a priority, a colour. "RAG", "Priority", "Minimum buffer", "Tolerance". If two people would fill the column in the same way, it is a rule that has not been written down, and it can be written as a condition the system checks.

Judgement. A person decides, and should. Yes/no columns ("OK to pay?", "Callback done?"), questions, approvals. The fix is not to automate the decision but to record it: who made it, when, and on what.

Reading. Data copied in from somewhere else. "Bank ref from email", "Portal status", a rate copied off a Bloomberg screen, a link to the evidence. Someone opens another source and types what they see. This is the kind of work a tool, or an AI model, can do well, because the answer is on a page somewhere and only has to be read.

Decision log. Notes, comments, remarks: free text explaining the exceptions. It is often the most valuable column in the sheet and the hardest to use, because nobody can count it. Replacing it with a short list of reasons plus a line of free text keeps the explanation and makes the pattern visible.

Reference data. Lookups, rates, codes, mappings: the agreed FX rate table, the fee code, the agreed price. Kept in the sheet because nobody owns the table it should come from. One maintained table, read by the system, beats a copy in every workbook. Which system should own it is the question a system-of-record map answers.

System field. Already in the system, typed in again: the invoice number, the vendor, the amount, the value date. Harmless on its own, but every re-typed field is a place where the sheet and the system can disagree. The fix is to read it from the system.

Unclear. Neither the name nor the values say what the column is for. That is the first question to ask whoever keeps the sheet.

The second date

One pattern deserves its own name. Two date columns side by side — "Due date" and then "Real due date", or "Value date" and "Actual value date" — usually mean the system holds one date and the people hold another. That is a second truth. Reports run from the system will use the first; decisions get made on the second. Either the system's date gets corrected where it is wrong, or the system gets a field for the date people actually use. Keeping both in different places is how a forecast and a payment run end up disagreeing about the same invoice.

Not every pair is a second truth. A start date and an end date are two ends of a range; a booking date and a value date are two facts the bank reports. The tell is two dates for the same event.

A worked example

Take an AP approvals sheet with nine columns: Invoice No, Vendor, Amount, Due date, Real due date, Approver, OK to pay?, Priority, Notes.

• Invoice No, Vendor, Amount and Due date are system fields. The ERP has them; the sheet types them again.
• Real due date is a missing field, and a second truth next to Due date.
• Approver is a missing field: ownership the workflow does not record.
• OK to pay? is a judgement. It stays with a person, recorded.
• Priority is an unwritten rule: someone ranks the invoices by eye, by criteria that could be written down.
• Notes is a decision log.

Seven of the nine columns are work that belongs in the system; two stay with a person. Three changes write themselves: correct the due date at the source or add a field for it, write the priority down as a condition, and replace the notes with a reason field. None of that needs a new platform. Most of it is configuration someone has been meaning to ask for.

What the header row cannot tell you

A column name says what someone called the column, not what they do with it. "Status" can be a copy of the system's own status or a set of states the system has never heard of ("waiting for bank", "query raised"); only the values, or the person who fills it in, can tell which. The reading is a set of questions to take to that person, not a verdict about their sheet.

It also cannot tell you how much the sheet matters. A twenty-column workbook that one person opens once a quarter is a different problem from a six-column tracker that blocks every payment run. The header row tells you what kind of work is in the sheet. Watching someone use it tells you how much.

Try it on your own sheet

I built the Spreadsheet Column Audit to do this reading quickly: paste the header row (and a few sample rows if you can), name the system it sits next to, and every column comes back with its kind, a confidence, the reason, and a suggested next step, plus the share of the sheet that belongs in the system, stays with a person, or could be read by AI. It runs in the browser, with rules rather than a model, and nothing you paste is sent or stored.

For the workflow around the sheet — who fills it in, who reads it, what happens after — describe it to Workflow X-Ray instead. And if the sheet turns out to be the real system, the redesign sprint is that reading done with the people who keep it.