Shared Excel Files and the Lost Update Problem Explained
How two people editing the same Excel file lose each other's changes, what co-authoring and locks do about it, and how a database prevents lost updates.
Two people open the same price list. One raises the price of a product; the other fixes a typo in its name. Both save. One of the changes is gone. Neither saw an error, and both believe their change is in. This is the lost update problem, an old one in data management, and shared spreadsheets are a textbook case of it. This post explains how it happens, what Excel does and does not do about it, and how a database handles the same situation. It is a spoke of when a spreadsheet has become a database.
The anatomy of a lost update
An update has three steps: read the current value, change it, write it back. Two people doing this at once can interleave the steps.
| Time | Ana | Bo |
|---|---|---|
| 1 | Reads the sheet (price 10, name "Pen") | |
| 2 | Reads the sheet (price 10, name "Pen") | |
| 3 | Sets price to 12, saves | |
| 4 | Fixes name to "Pencil", saves |
If saving writes the whole file, Bo's save at time 4 replaces the file with Bo's version, which has the old price. Ana's change is lost. If saving were per cell, both changes would survive, because they touched different cells. The difference between the two is the unit of the write: the whole file, a row, a cell or a field.
Lost updates are silent. There is no error. The first symptom is a colleague asking why the price is back to 10, long after nobody remembers who saved when.
What a plain shared file does
If the workbook sits on a network share and two people open it, Excel's behavior depends on the file format and the version. For an .xlsx on a file share, Excel places a lock: the first person to open it gets read-write access, and the second sees a message that the file is in use and is offered read-only access or a notification when it becomes free. The lock stops simultaneous editing, and with it the lost update. The price is that only one person can work at a time, and that the lock is only as good as the file server. A crash can leave a stale lock (the ~$ file), and a copy made to work around the lock restarts the problem with two files.
The old Shared Workbook feature allowed several people to edit one file on a share with a merge at save time, tracking changes and conflicts. Microsoft has deprecated it in favor of co-authoring, and it is unreliable with modern features. Do not build on it.
Co-authoring
In OneDrive and SharePoint, Excel supports co-authoring: several people edit the same workbook at once, in Excel for the web or the desktop app, and changes appear within seconds. Edits are merged at cell level. In the example above, Ana's price and Bo's name both survive, since they are different cells.
Co-authoring removes the locking problem and most whole-file overwrites. It does not remove all lost updates:
Two people edit the same cell. The last change wins, and the other is overwritten. The cell shows the final value, and there is no conflict dialog in many cases.
Logic depends on several cells. If a total should equal a quantity times a price and two people change both from different starting points, the cells merge into a combination nobody intended.
Features that do not merge. Some operations, such as certain macros or older features, are not supported in co-authoring and disable it.
Offline copies. Someone who works on a downloaded copy and uploads it later reintroduces the whole-file problem.
Version history in SharePoint can restore an earlier version of the whole file, which lets you recover a lost change, if someone notices in time. It does not tell you who changed which cell and why.
Copies are the larger lost update
The biggest form of the problem is not two people on one file but two files. prices.xlsx is emailed, edited by three people and sent back as prices_v2_AN.xlsx, prices_v2_BO.xlsx and prices_final.xlsx. Someone must reconcile them by hand, and the reconciliation loses changes. No Excel setting prevents this, because the problem is the existence of several copies of what should be one thing (ten signs your Excel file needs to become a database lists it as a sign).
How a database prevents it
Databases were designed for concurrent writers. They have two main tools.
Transactions and locks. An update statement locks the rows it changes until the transaction commits. A second update on the same row waits. When the first commits, the second proceeds against the new value:
UPDATE products SET price = price * 1.2 WHERE id = 7;Written like this, with the new value computed in the database, the statement cannot lose an update: two concurrent runs apply one after the other, and the price rises by 44 percent in total, not 20. The trap is the read-modify-write cycle done in the application: read the price, add 20 percent in code, write the result. Two such cycles can overlap and the second write replaces the first.
Version checks. For edits that take a person minutes, a lock held that long is impractical. Instead, each row carries a version number. The edit form reads the row with its version; the save includes the version in the WHERE clause:
UPDATE products
SET name = 'Pencil', version = version + 1
WHERE id = 7 AND version = 3;If someone else saved in between, the version is now 4 and the statement updates zero rows. The application sees that and tells the user: "this record changed while you were editing; here is the new version". Nobody's change is lost silently. This pattern is the standard solution, implemented in most frameworks as an automatic version or timestamp column.
For the common case of cell-level edits, the database updates only the columns that changed (SET price = 12), so Ana's and Bo's edits to different fields both survive, just as with co-authoring, with a clear failure when they hit the same one.
Isolation levels in one paragraph
A transaction's isolation level defines how much it sees of other transactions' work. In PostgreSQL's default, Read Committed, each statement sees data committed before it started, which is enough for the single-statement update above. For logic that reads, decides and writes within one transaction, the stricter Repeatable Read or Serializable levels detect conflicts and abort one transaction, which the application then retries. For most business applications, atomic UPDATE statements and a version column are enough, and the stricter levels are for special cases.
Preventing lost updates in a spreadsheet process
If a database is not available yet, reduce the risk with process.
One master, one owner. Name the editor of record. Others submit changes to them.
No copies. Share a link to the file in SharePoint, not attachments.
Use co-authoring, and ask people to use the web or current desktop app.
Separate input from calculation. Inputs on one sheet, formulas on another, so that edits touch less.
Record changes. A log sheet with date, person and what changed, filled in by the editor, is better than nothing.
Check after merges. Totals compared with the source and the last version show when something went missing.
These measures reduce the chance of loss. They do not remove it, and they cost attention that a database does not need.
A test you can run
Open the same workbook in two sessions (co-authoring, or two windows on a share with a copy), have both edit the same cell and different cells, and save. Note what survives. Then try the editing sequence from the table above with the whole-file case. It takes ten minutes and shows your team exactly what protection you have, which is often less than assumed.
When the lost update is not the real risk
Edits are rare. A file edited by one person monthly will not lose updates.
The risk is wrong data, not lost data. If the issue is that anyone can type anything, the answer is validation and constraints.
The need is history. If you want to know what changed, you need an audit trail, not conflict prevention (audit trails and history that Excel cannot give).
The data should not be shared at all. Sometimes the better answer is a read-only published copy and a controlled path for changes.
Microsoft documents the behavior on its page about collaborating on Excel workbooks with co-authoring, and PostgreSQL explains the options in its chapter on concurrency control. To convert a small extract, use the CSV to JSON converter.
