Automated Spreadsheet Data Synchronisation
A spreadsheet and a business system hold the same data – just not the same values. The sync runs on fixed rules, writes only where it is clear which side changed, and puts every conflict in front of a person instead of overwriting it.
The first import is easy, the second is not
Spreadsheets are not going away, and not because they are the wrong tool. They are the fastest interface anyone has: filter, type into three columns at once, drag a formula across, done. A business system, by contrast, has forms, mandatory fields and permissions. So the same data ends up in two places – the system that is officially authoritative, and the file people actually work in.
The first import from such a file is simple: everything is new, everything gets written. The second one is where it breaks. From then on both sides may have changed, and the file is no help – a spreadsheet does not remember which cell anyone touched, it only knows its state at the moment it was saved. Comparing file against system alone shows you a difference but not its direction. A macro settles that question silently, usually in favour of the file, and overwrites changes somebody made in the system for a good reason.
On top of that, rows in a spreadsheet have no identity. An inserted column, a renamed header, a deleted row – obvious to a person, but to an import a new record or an instruction to delete. So what we build is less an import than a comparison with a memory: a key that recognises rows over time, a stored state from the last run, one rule per field – and a visible route for the cases no rule covers.
Where a rule-based reconciliation pays off
We start with the reconciliation that runs most often and has the clearest key. Further files later share the same checking and logging logic.
Master data between spreadsheet and system
The most common starting point: item, customer or supplier data is maintained in Excel and needs to arrive in the ERP or CRM without anyone comparing row by row.
Lists that arrive from outside
The supplier sends a file every month with slightly different column names. The mapping is defined once and checked on every run.
Connecting two systems without an interface
Where there is no API, the export file is the handover point. Then at least the reconciliation has to be reliable and able to prove what it wrote.
Feeding reports and dashboards
The reporting workbook needs the same structure every Monday. Change one column and the report breaks – or it quietly keeps calculating and is wrong.
Merging lists kept by several people
Three sites maintain the same list in three files. The sync merges them and shows exactly where two versions contradict each other.
Quantities and stock levels
Numbers that change often and where wrong overwrites get expensive. This is where the conflict rule pays for itself fastest.
What the run actually compares
On the left the approved file, on the right the state in the target system. Every cell the run compares is marked – along with what it does about it.
price-list-w28.xlsxSheet “Items” · saved 13 Jul, 17:42
| A · Item no. | B · Description | C · Price € | |
|---|---|---|---|
| 12 | A-1042 | Pipe bend DN 50 | 18.90 |
| 13 | A-1043 | Ball valve DN 25 | 24.50 |
| 14 | A-1051 | Sealing ring set, 12 pcs | 6.80 |
| – | A-1078 does not appear in the file | ||
ERP · item masterTarget system · read 14 Jul, 06:00
| Item no. | Description | Price € | changed |
|---|---|---|---|
| A-1042 | Pipe bend DN 50 | 18.90 | 2 May |
| A-1043 | Ball valve 1 inch | 22.90 | 2 May |
| A-1051 | Sealing ring set, 12 pcs | 7.40 | 12 Jul |
| A-1078 | Wall bracket, galvanised | 34.00 | 30 Jun |
The rule that settles the conflict
- Iffile and target system have both changed since the previous run on 11 Jul – here the price field on A-1051.
- Thennothing is written. The ERP value stands, the row counts as a conflict and does not hold up the rest of the run.
- Andboth values go to purchasing with their timestamps and source. Only that decision writes.
The description field carries the opposite rule: there the file leads and the target system is overwritten. A row missing from the file deletes nothing – A-1078 stays and is reported.
The row that matters is the one in conflict. Without a rule for it, whoever saved last wins in practice – and nobody notices.
A spreadsheet has no timestamp per cell. That is why the run compares both sides against the stored state from 11 Jul rather than directly against each other.
From the saved file to the log
-
Agree the key
First we establish which column identifies a row over time: item number, customer number, order number. Without a stable key there is no reconciliation, only text comparison.
-
Take the file and check its structure
The run collects the file from SharePoint, a mailbox or a folder and checks column names, data types, mandatory fields and duplicate keys before comparing anything. If the structure does not fit, the run ends before a single value is written.
-
Compare against the last known state
The comparison is not file against system but both against the stored state of the previous run. That is the only way to see which side moved – and whether both did.
-
Apply the rules and write
Each field carries a rule: file leads, system leads, or never automatically. Writing happens in one pass, with a ceiling for bulk changes and a stop if a run would touch an unusual number of rows.
-
Log the run and hand out exceptions
Every run leaves a log with row, field, old value, new value and the rule applied. Conflicts go as a list to the role responsible, not as a round-robin email to everyone.
What changes day to day
Today
- Export, edit in Excel, import again
- Nobody is quite sure which file is the current one
- The import overwrites changes made in the system
- Errors surface when a report starts looking odd
- The reconciliation depends on one person and their macro
With a rule-based sync
- The run starts on a schedule or at the push of a button
- The state of the previous run is stored and readable
- Values are written only where exactly one side changed
- Structural errors stop the run before the first change
- The rules are written down, not buried in a macro
What a reconciliation cannot do
Four points we settle before quoting. Two of them are reasons to leave it alone for now:
- Without a stable key there is no reconciliation. If rows can only be matched by name – “Müller GmbH” against “Mueller GmbH & Co. KG” – we are guessing. Fuzzy matching can be built, but it then belongs under human supervision permanently. If there is no key at all, the first project is a clean-up, not an automation.
- Conflicts do not disappear, they become visible. The rule decides only the cases you decided in advance; everything else grows into a list somebody has to work through. If both sides keep changing the same fields, the sync is a plaster. What is missing then is the decision about which system is authoritative, and that is organisational, not technical.
- If you intend to retire the spreadsheet anyway, this is the wrong purchase. A reconciliation stabilises exactly the arrangement you may want to be rid of – it makes the file permanent. Where the business system already has the fields and only the data entry form is unpleasant, a small capture screen or a training session is cheaper. And at two runs a month with eighty rows, doing it by hand simply costs less than automating it.
- It is not real time, and that is deliberate. We run the reconciliation on a schedule: hourly, overnight or on demand. A sync that fires on every keystroke creates conflicts that would not otherwise exist and makes the logs unreadable. If you genuinely need data identical to the second, you do not need a spreadsheet, you need an interface.
Fits your files and systems
Scope and price
The entry price covers one reconciliation between one file and one target system, with a rule set, an exception list and a log. What moves the price, we say before the quote.
- One reconciliation between one file and one target system
- Key, column mapping and a rule per field, documented
- Structure check that aborts before the first change
- Stored state of the previous run for real change detection
- Log per run with row, field, old value and new value
- Exception list and notification to the responsible role
- Handover session, documentation and 30 days of support
What increases the price
- Several source files or several target systems in one run
- Reconciliation in both directions rather than one
- Matching without a unique key, for instance by name and address
- An upfront clean-up that resolves existing duplicates
- An approval step with a preview before anything is written
Several sources, a two-way reconciliation and matching without a unique key typically land in the range of our Workflow Advanced package from €2,490. We quote the binding fixed price after the intro call.
All prices excl. VAT · operation and further development optionally via a support package
What you get
-
Production reconciliation
Set up, tested with your real files and signed off with a dry run that writes nothing
-
Rule set per field
Which side leads, what is never written automatically and what counts as a conflict – in writing
-
Log and exception list
Traceable down to the single cell, including a rollback of the most recent run
-
Handover for your team
What a file has to look like, who decides conflicts and what to do when a new column appears
Frequently asked questions about data reconciliation
These solutions fit alongside
Automated Customer Data Updates
When changes arrive one at a time and continuously rather than collected in a file.
API Integration
When both systems offer an interface – then the file detour is no longer needed.
Email Attachment Processing
The step before: the list arrives as an attachment every month and is filed by hand.
Automated Status Reports
The step after: checked data becomes a report nobody has to recalculate.
Which spreadsheet is the truth in your business?
In the free intro call we look at one real file and the target system: is there a key, how often does what change, and which side is allowed to win. Afterwards you know whether a reconciliation will carry or whether a clean-up has to come first.
Book a free intro callWhere spreadsheet data synchronisation creates value in everyday work
The workflow takes an approved spreadsheet or file, validates structure and values, matches keys and writes only permitted changes to the target system.
Three concrete operating scenarios to compare with your own process.Prepare CRM imports
Contacts are validated, deduplicated and imported with a clear error list.
Reconcile master data
Product or customer data is compared between spreadsheet and business system.
Update reporting
Approved files are loaded into reporting using the same schema each time.
A strong fit when …
Recurring office tasks follow clear rules, consume small blocks of time every day and should run reliably without replacing existing systems.
- You handle recurring spreadsheet rows using repeatable rules.
- The intake, target system and accountable business role can be named clearly.
- Exceptions are allowed to remain visible and move to people deliberately.
Deliberate automation boundary
Unknown columns, conflicting keys, bulk deletions and changes to sensitive fields stop the run and require approval.
Explore the technical approach and platformsEstimate time savings with your own volume
The calculator uses 2 minutes today and 0.4 minutes after automation as fixed example assumptions. It does not replace process analysis.
Illustrative estimate based on the visible assumptions — not a guarantee.