Why does everyone fill in the shared spreadsheet differently?

Because the sheet asks a question it never defined, and everyone answers the version they assumed. The mess is almost never spread evenly. It lives in one or two columns where people type freely, and it traces back to a header that means something slightly different to each person reading it. Fix that column and most of the rows fix themselves.

This came back to me last Monday, when the weekly tracker arrived with dates in three formats, one client spelled four ways, and a row that just said "see email". I have rebuilt this particular spreadsheet three times this year. Each rebuild fixed the thing I was looking at and moved the mess somewhere I was not.

Jomar wrote yesterday about the other kind of broken spreadsheet, where the file is fine and Excel mangles it on the way in. This is the kind where the file is doing its job perfectly, which is the trouble. It is faithfully recording four different answers to a question nobody wrote down.

Match the mess to its cause

Ordered by how often each one turns out to be the real problem in the sheets I end up untangling.

The last row is the one worth sitting with. A perfectly consistent sheet filled in on Friday afternoon from memory is less accurate than a messy one filled in as things happen. It just looks better, and looking better is how it survives.

Rule these out before you rebuild anything

Four checks, in order. Each takes a few minutes and either clears or does not.

1. Find the column with the most variation. Sort each column A to Z and scroll. The messy one announces itself quickly, and it is usually one column rather than the whole sheet.
2. Ask two people what that column means. Separately, without the sheet in front of them. If you get two answers, you have found the cause, and no validation rule fixes a question people disagree about.
3. Find out when it gets filled in. As the thing happens, or at the end of the week from memory. The second one produces vague entries whatever the sheet looks like.
4. Check what reads the sheet. A report, a formula, a person on a Thursday. If nothing reads a column, the fix is deleting it, which is the cheapest fix available and the one nobody suggests.

Most of the time step two is the answer. The header said "Date". To one person that meant the date a request came in, to another the date it was due, and to a third the date they dealt with it. All three were filling it in correctly.

Fix the column, not the people

Once you know which column and why, the repairs are small.

• Put a one line description directly under each header: what goes here, in what form, with an example. This has fixed more columns for me than any setting has.
• Turn free text into a list wherever the answer comes from a known set. Keep the list on its own tab, so adding a client is one edit rather than a rebuild.
• Format date columns as dates, then add validation so a typed "next tues" is refused instead of stored.
• Write down the status values and what each one means. Four is plenty. If you need a fifth, one of the four is doing two jobs.
• Leave one column deliberately free. Notes, comments, whatever you call it. People need somewhere to put the thing that does not fit.

Write the definitions where people type

The one line under each header is the fix I would keep if I could keep only one, so here is what it looks like in practice. This is the top of the current tracker, give or take the client names. The definitions live in the sheet itself, in a frozen row directly under the headers, because a definitions document somewhere else is a document nobody opens.

Two things about that table matter more than the rest. "Received" says which date, because that is exactly the column that caused the Monday mess: three people, three reasonable meanings of the word "Date". And "Filled in by" exists because nobody had ever said, which meant everybody assumed somebody else would fix the blanks. Nobody did. Nobody does. Writing a name, or at least a role, against a column is the cheapest accountability there is.

Look at the handoff, not the entry

Every shared sheet has two kinds of people around it. The ones who type into it and the ones who read it. In most of the sheets I untangle, those are different people, and they have never had a conversation about what the columns mean. The typists fill it in the way that makes sense at the moment of typing. The readers pull from it on Thursday and discover what "done?" meant.

So when I find the messy column, I go to the reader first, not the typists. The reader knows what they need the column to say, because they are the one it breaks on. In my case that is me and the Thursday report, which is an uncomfortable thing to realise when you have been blaming everyone else's data entry for most of a year. The definitions got better the moment the person who needed them wrote them.

The second move is to sit with one typist, once, while they fill in a row. Not to check them. To watch where they hesitate. The hesitation is the undefined question, visible in real time, and it is usually not where you would guess from looking at the finished sheet.

Clean up what is already there

Once the column is fixed, the old rows are still a mess. The order matters here, and it is the opposite of what most people do first.

Fix the column before you clean the data. If you clean first, new entries keep arriving in the old broken shape while you are cleaning, and you do the job twice. Nobody does this in the right order the first time. I did not.

Then sort the messy column A to Z. The variants of each value end up next to each other, and four spellings of the same client become four adjacent rows you can fix in one go. For anything with more than a handful of variants, I make a small mapping tab: the messy spelling in one column, the correct one beside it, and a lookup to replace the old values. It is slower to set up than typing over them by hand, and it leaves a record of what you changed, which you will want the first time somebody asks why their entry looks different.

Keep the original column until you have checked the new one. Hide it rather than delete it. The honest version is that every so often I find a cleaning decision I got wrong, and having the original sitting there, hidden, turns that from a problem into a quick correction.

When a spreadsheet is the wrong tool

Most of the time the sheet is fine and one column is the problem. I mention the exception so you can rule it out, not because it is usually the answer.

A spreadsheet starts to be the wrong tool when several people need to edit the same row at the same time, when you need a reliable history of who changed what and when, when rows need to point at other rows (a client with many jobs, a job with many invoices), or when something has to be approved before it counts. Each of those is possible in a spreadsheet. Each is also a workaround, and a sheet held together by workarounds is the sheet that gets rebuilt three times a year.

If you hit two or more of those, a form in front of the sheet fixes the entry side, and a tool built around records, such as Airtable or whatever your organisation already pays for, handles the rest. The already-paid-for option is usually the right one, because it already has logins, permissions and somebody who knows how it works. Which is fine, except that the undefined question moves with you into the new tool. A database with a field called "Date" has exactly the same problem as a spreadsheet with a column called "Date". Define the question first, wherever the answers end up.

Spend one hour on it this week

None of this needs a project. Most of it needs one hour, and the hour works best split across a few days, because two of the steps depend on other people answering you.

On the first day, take fifteen minutes and do check one from the list above: sort every column and find the messy one. Write down its header exactly as it appears. That is the whole task for the day. On the second day, ask two people what that header means, separately, in a message rather than a meeting. A meeting produces agreement on the spot and disagreement on Friday. A message gets you what each person actually thinks.

When both answers are back, write the definition yourself, as the person who reads the sheet, and put it in the row under the header. Change the column's format or add a dropdown only if the definition makes it obvious that one would help. Then leave it alone for a week and look at what arrives. If the new rows are cleaner, clean the old ones using the sorting method above. If they are not, the definition is still ambiguous, and the week told you where.

That is the entire method. It is not impressive. It does not need buy-in, a new tool or a kickoff meeting, which in my experience is why it gets done, and why the big rebuilds I planned in previous years mostly did not.

Ask what each fix moved

Every one of those moves some work onto the person entering the data. A dropdown is slower than typing for someone who already knows the answer. Validation stops a mistake, and it also stops the entry, and a stopped entry does not disappear. It goes somewhere. The question to ask of any fix is the one I ask of any tool: did that remove the work, or move it?

In practice the answer is usually "moved it", and that is fine, as long as you know where to. Moving five seconds of care to the moment of entry, in exchange for not untangling the sheet every Monday, is a trade worth making. Moving the whole entry into a column nobody reads is not a trade. It is a leak.

What I had wrong

On the second rebuild I validated everything. Every column had a dropdown or a rule, the dates were locked, and for about a week the sheet was the tidiest thing I had ever made. Within a fortnight the Notes column held the real data. "Client: the one from row 12." "Due next tues, see email." People had routed around every rule I wrote, into the one column I had left open, and the tidy columns beside it were being filled with whatever the dropdown would accept.

The work had not gone anywhere. I had moved it into a column I could not sort, and made the sheet worse at the one thing it was for while making it look better.

The third version validates the three columns that feed the Thursday report and nothing else. Everything else is free, with a line of description under each header. It is less tidy. It gets filled in. Somebody told me last week that it was nicer to use, which I did not measure, and which I have started to suspect mattered more than any of the validation did.