Task

The spreadsheet that does the work the system doesn't

The spreadsheet is a reasonable answer to a real need.

The spreadsheet next to the system usually exists for a good reason.

Not every business has this problem, and some should leave it alone.

Most of these can be done more efficiently. Whether it's worth doing is a separate question. What follows is one common form of the problem, described in general terms, because your version will differ. These are possible challenges, not a description of your business, and some should be left exactly as they are. If it sounds like your week, that's worth a conversation.

Sound familiar?

There is a spreadsheet next to the system, and it's the one people trust.
Only one person can explain the formulas.
Two people quote two different numbers in the same meeting.
Nobody knows when the figures were last true.

What it looks like

There is a system that holds the business's records, and there is a spreadsheet, maintained alongside it, that somebody actually uses. Numbers are copied from one into the other, weekly or every morning. The spreadsheet shows the figures in the arrangement somebody needs, or adds a column the system has no field for, or presents things so they can be understood at a glance.

It usually started as a temporary workaround. It has been in use for years.

What it costs

A copy is a snapshot, and it keeps being treated as though it were live. The moment numbers are copied out, they stop updating. Everything afterward happens in the system and not in the copy.

The copy does not look stale. It looks like a formatted spreadsheet of current figures, opened this morning. So decisions get made on figures that were accurate at some unstated point in the past. Not wrong, exactly; expired.

It gets worse when the file is shared. Several snapshots from several moments circulate, and two people in the same meeting can confidently quote different numbers with no way to tell which is current.

Where it goes wrong

Nothing records when the copy was made. The age of the figures cannot be known from the file.
Manual edits get made to the copy. Now the copy and the source disagree, and the next refresh either destroys the correction or stops happening.
The formulas are load-bearing and unexamined. They often hold the business's real logic, written once and not checked since. A range that does not extend to new rows works perfectly and silently stops including recent data.
Only one person can drive it. It is often the most important file in the business and the least documented.

What a better version looks like

Start by asking what the spreadsheet is for. Usually the system cannot present the information the way somebody needs it. That is a real need, and the spreadsheet is a reasonable response. The workaround is not the problem.

The improvement removes the copying while keeping what it achieved. The arrangement gets built against live data, and the logic buried in formulas, which encodes how the business thinks about its numbers, gets written down.

Where copying must continue, every copy should carry its own timestamp and source. Where the spreadsheet will persist regardless, a standing comparison against the system, reporting differences and changing neither side, is worth more than trying to eliminate it.

What still needs a person

A person still decides what the numbers mean, which is what the spreadsheet was built to support. And someone has to explain what the formulas were meant to do. That knowledge is usually in one head, it is valuable, and writing it down is worth doing before anything else changes.

Questions worth asking about your own operation

When was the figure in front of you last true?
Has anyone edited the copy directly?
Who can explain every formula in it?
If the person who built it left, what would stop working?

If this sounds familiar

Bring me the version you actually have. I'll learn how the process really works before I suggest anything. If it isn't worth changing, or isn't a fit for me, I'll say so. Talk through a problem