Spreadsheet versions out of sync: why it happens and how to fix it

Short answer: team spreadsheets drift because every email attachment, download and "save as" makes a new copy. The fix is to keep one file in one shared place, send links instead of files, and turn on version history. If many people update rows, move the data to a list.

Why spreadsheets drift apart

A spreadsheet starts as one file. Then someone emails it. Someone else downloads it to work on a plane. A third person saves a copy called "final v2" so they can try an idea. Now there are four files, and each one is correct in someone's mind.

The drift is not caused by careless people. It is caused by how files move. A file on a laptop is a copy. A file in an email is a copy. A file on a shared drive that people copy out to work on is a copy. Each copy lives its own life from the moment it is made.

Three habits make it worse:

Sending the file as an attachment instead of a link.
Working offline and merging changes later by hand.
Having no rule for who may change the structure (columns, tabs, formulas).

Signs your team has this problem

People ask "which file is the latest?" in chat.
File names end in FINAL, FINAL2 or a date.
Two reports about the same thing show different numbers.
Someone spends part of each week combining copies.
A formula was changed and nobody knows who changed it or when.

If you see two or more of these, you have a version problem and not a typing problem.

Step 1. Pick one home for the file

Choose one folder in one place, such as a SharePoint document library or a Teams channel. Move the newest file there. Name it with no version words, for example "Customer Orders". The name stays the same for the life of the file.

Then tell the team in one short message: this is the only copy. Anything else is old.

Step 2. Send links, not files

When someone needs the sheet, they get a link to the one file. A link always opens the current version. An attachment is frozen on the day it was sent.

Make this a team rule. If someone asks for an attachment, send the link and say why.

Step 3. Work in the shared file

Open the file in the browser or the desktop app from the shared location. Several people can edit at the same time in Microsoft 365, and the file saves as you work. Avoid "Save a copy" and "Download" unless you truly need an offline copy. If you do, write down who has it and when it must come back.

Step 4. Turn on and use version history

Version history keeps earlier states of the file. When someone breaks a formula or deletes a column, you can open an older version, see what changed and restore it. This ends the argument about who changed what. Version history is on by default in SharePoint libraries. Check that nobody turned it off.

Step 5. Lock down the structure

Most damage comes from changes to columns, tabs and formulas, not from changes to the data. Do three things:

Name one owner for the file. This person approves new columns or formula changes.
Protect the formula cells and header row so others cannot overwrite them by accident.
Use dropdown lists for fields like Status, so people pick a value and do not type their own.

Step 6. Write a short rule sheet

Put five lines in the first tab or a note at the top:

Where the file lives.
Who owns it.
Who may change columns.
How to ask for a change.
What to do before working offline.

Short rules are followed. Long rules are skipped.

When the spreadsheet itself is the problem

Some files cannot stay in a spreadsheet. If ten people update rows every day, if each row is an item with an owner and a status, or if you need reminders, a spreadsheet will keep drifting no matter how good the rules are. A SharePoint list stores one row per item in one place, tracks who changed what, and can send reminders. You can read how to move a tracker in "Replace a spreadsheet tracker with a SharePoint list". Keep Excel for math and models. Use a list for shared tracking.

A simple monthly check

Once a month, search the shared drive and your own downloads folder for copies of the main files. Delete the old ones or move them to an archive folder. Ask the team if anyone is working from a copy. This takes a few minutes and keeps the problem from coming back.

Honest limits

Rules and links reduce drift. They do not remove it. If people have a good reason to work offline, copies will still exist. Plan for it: name who holds a copy and when it merges back. Also, version history helps you recover. It does not tell you which of two conflicting numbers is right. A person still has to decide that.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

How do I find out which version is the real one?

Compare the last modified date and the author of each copy, then ask the people who made them. Pick one, move it to the shared home, and archive the rest. Do not merge more than two copies at once. Merge one pair, check it, then add the next.

Can two people edit the same Excel file at once?

Yes, in Microsoft 365 when the file is stored in SharePoint or OneDrive and opened from there. Changes show up as people work. Files that are emailed or kept on a laptop do not get this.

Should I stop using Excel?

No. Excel is good for formulas, models and one-person analysis. Move to a list only when many people update the same items and you need status, owners or reminders.

How do I stop people from making their own copies?

You cannot stop it fully. Make the shared file easy to open, send links, and give people a clear reason: the copy will be out of date within days.