Help for owners
I copy and paste data between spreadsheets every week
Short answer
Copying data between spreadsheets can be automated with links, scheduled scripts or an import. It breaks when someone inserts a column, when rows have no unique key, when two people edit the same data in both places, and when a sheet has grown so large that it is slow or hits limits.
Does this sound like your situation?
- I paste the same export into three files every Monday.
- The numbers in the report differ from the numbers in the source sheet.
- Someone added a column and the report went blank.
- Two people edit different copies and nobody knows which is right.
- The file takes a long time to open.
Why does this happen?
Columns are matched by position
A copy that takes 'column D' stops being correct the day someone inserts a column. The numbers shift into the wrong place without any error.
Rows have no unique key
Without an order number, id or similar, the copy cannot tell whether a row is new, changed or a duplicate, so it appends again or overwrites the wrong line.
Two places can be edited
If both sheets accept changes, a sync has to decide which side wins. Without a rule, one person's edit is lost.
Text that looks like numbers and dates
Dates, leading zeros and decimal separators change when data moves between files and regional settings. A date can silently turn into text or swap day and month.
The sheet has become a database
Large, many-sheet workbooks used by several people are slow and fragile. Copy rules around them are patches on a tool being used for a job it was not made for.
What can I do about it today?
- Decide the single source for each kind of dataOne sheet is the master. Others only read from it and are not edited.
- Add a unique id column to every tableAn order number or any stable value. Never reuse or retype it.
- Lock the structureProtect header rows and ask people to add columns only at the far right.
- Back up before any big pasteMake a copy of the file with the date in its name before overwriting data.
- Check one number by hand after each updateCompare a total in the source and the copy. If they differ, stop before sending the report.
When should I bring someone in?
- The data is needed by an app, website or accounting tool as well as by the sheets.
- Several people edit the data every day.
- Mistakes in the numbers have already reached customers or your accountant.
How do I handle it?
- Find the master and the readersI draw which sheet holds the truth and which ones only show it.
- Match by key, not by positionRows are matched on the unique id and columns by their header name, so inserted columns do not break the copy.
- Validate on the wayDates, numbers and required fields are checked, and rows that fail go to a list for a person to review.
- Keep a log of each runRows added, changed and skipped are recorded, so an odd figure can be traced.
- Say when the sheet should become a databaseIf the volume or the number of editors has outgrown it, I tell you plainly and describe the smaller change that would help first.
The price depends on what I find, so I quote in writing after a short discovery call. Tell me what is happening and I reply within 24 hours.
What do owners ask about this?
Can a sheet update itself from another sheet?
Yes, using the spreadsheet tool's own linking or a scheduled script. The right choice depends on how much data, how often and who edits it.
Is Google Sheets or Excel better for this?
Both can do it. The better one is the one your team already uses, because moving everyone costs more than the sync.
Will old data be affected?
I test on a copy and keep the original untouched until the numbers are checked against it.
How was this page made?
I drafted this page with AI assistance. It is general guidance for business owners, not advice for your own system. Found a mistake? Tell me.