Forty spreadsheets are not forty systems. How to build the inventory, sort each file into four dispositions, find sources of record, and reconcile the result.
Key takeaways
- The file count is not the project size. What decides it is how many of them hold a value that exists nowhere else.
- Four dispositions are available per file: keep it as a working tool, rebuild it as a report, migrate its data, or archive it.
- A file with no consumer is a finding, not a problem. It shortens the project.
- The dependency map has to include the relationships that are not formula links, chief among them manual copy and paste between files.
- A parallel run without a numeric reconciliation table and a pre-agreed tolerance is not a parallel run. It is a feeling.
Forty Excel files are not forty systems. Some are monthly copies of one structure, some are reports containing no original data at all, and some have not been opened in a year. How many of them genuinely have to migrate is decided by the inventory, and only after that is there a basis for talking to a vendor.
Forty files are not forty systems
Before mapping anything, it helps to know what you are looking for. A file holding a value that originated in it and exists nowhere else is a source of record, and it is the only thing whose migration is mandatory. Everything else is derived: a report summarising sources, a copy saved for a particular month, or a personal version somebody made for themselves and forgot about.
That distinction changes the size of the project more than any technical decision will. A project migrating forty files and a project migrating eleven tables are two entirely different projects, even when the folder on the server looks identical either way.
The inventory row: what to record per file
| Field | Where it comes from | What you conclude from it |
|---|---|---|
| Filename and path | File explorer | Reveals monthly copies by naming pattern |
| Last modified date | File properties | A file untouched for a year is almost always dead |
| Owner | A short question, not a guess | A file with no owner is an archive candidate |
| Number of users | Ask the owner | A single-user file needs no multi-user solution |
| Does it hold original data | A scan of the file | This is the field that decides source-of-record status |
| Links to other files | The workbook's external links list | Builds the dependency graph |
| Does it hold personal data | A column scan | Sets storage and access requirements |
| Rate of change | Actual update frequency | Separates live data from a snapshot |
The two critical fields are "does it hold original data" and "links to other files". The first decides what migrates, the second decides in what order.
Which of the four dispositions does each file get?
Every file in the inventory gets exactly one disposition, derived from the fields rather than from how anybody feels about the file:
- Keep it as a working tool. A file one person uses for a personal calculation, with no consumer and no original data, stays exactly as it is. A spreadsheet is an excellent calculation tool when that is all it is doing.
- Rebuild it as a report. A file whose entire content is derived from data that exists elsewhere does not migrate. It gets rebuilt as a view over the source, which also removes the manual refresh work attached to it.
- Migrate the data. A file holding a source of record. This is the only category that justifies migration work and data validation.
- Archive it. A file with no consumer, no updates and no unique data. Keep a frozen copy for history and stop treating it as live.
The order you work through them is not by file size and not by how ugly the file is. It is by risk: first the files where a failure stops collections or shipping, then the files creating duplicated work, and convenience last.
The dependency graph, including the links that are not links
A workbook's external links list shows only the dependencies a formula created. The more dangerous part of the graph does not appear there at all, because it is performed by hand.
- Copy and paste between files. It leaves no trace in the workbook whatsoever, and it is the most common relationship between spreadsheets in any organisation.
- A file that arrives by email once a week and gets saved under the same name. The dependency is on the sender, not on the path.
- A manual export from another system pasted in as a tab. Here an interface already exists, which makes it an unusually good starting point for automation.
- A named range pointing at another workbook. Microsoft's documentation notes that the auditing tools cannot show formulas in another workbook while that workbook is closed, so a relationship like this disappears from a superficial check.
Where a repeated export from another system is involved, it is worth knowing that Power Query's preparation engine exists for exactly this: it saves the transformation steps as a refreshable query rather than a one-off pass somebody repeats every month. More on choosing between an existing system and custom development in the Israeli context is in migrating from Excel to an Israeli ERP.
A worked pass over 41 files
This is what an ordered inventory of a single folder looks like once every file has a row:
| Group | File count | Disposition |
|---|---|---|
| Sources of record, data originating only there | 11 | Migrate |
| Monthly copies following the structure of 3 of those sources | 14 | Archive, the structure is already covered |
| Reports derived entirely from other sources | 9 | Rebuild as a report |
| Personal copies with no consumer | 5 | Keep as a working tool |
| Files unchanged for over a year | 2 | Archive |
Out of 41 files, 11 tables remain to migrate, and 14 of those files are not a new structure at all but monthly instances of three structures already counted. That is the entire insight: the migration surface shrinks by roughly three quarters, and most of what looked like work was double counting. If several of the files are updated by more than one person at once, a separate consideration applies, covered in concurrent spreadsheet edits and conflict control.
How do you know the migration is correct?
You run the old and the new side by side over the same month and compare pre-agreed numbers. A parallel run without that table is a feeling, not a test.
| What you compare | Tolerance | Action on a breach |
|---|---|---|
| Record count loaded | Zero | Stop and identify the missing records |
| Total monetary value for the month | Zero | A money difference is always a bug, never rounding |
| Count of active customers | Zero | Usually a filter rule that did not come across |
| Empty values in a required field | Zero | Either a gap in the load or an unmapped field |
| Rounding differences on a single row | One minor unit | Record it, do not stop |
- Do not delete the old file at the end of the first cycle. Two complete cycles is the minimum.
- The comparison runs on real data from a closed month, not on test data.
- The person who signs off the reconciliation is the consumer of the report, not the person who performed the migration.
- Every gap that gets closed is recorded with its cause. The list of gaps is the real documentation of the rules that were never written down.
A similar parallel-run structure, in the context of replacing an older system, is set out in the Access to web system migration plan.
Sources
Frequently asked questions
Where do you start with forty files and nobody who remembers all of them?
With the last modified date. Sorting the folder by it separates live files from files nobody has touched in a year within about a minute. That is a distinction you can make without asking anybody, and it usually shortens the list considerably before any real work has begun.
Do we need to migrate history or only the current state?
That is a separate decision per table, and it is worth making it explicitly. Balances and customer details are needed in full. Years of historical transactions are often not needed in the new system and can be kept as a frozen archive, which shortens the migration substantially.
Our sheet is near Excel's size limit. Is that reason enough to move?
Usually not, because the limit is high: a worksheet holds up to 1,048,576 rows and 16,384 columns. Real files break long before that for other reasons - slow recalculation, formulas nobody understands, and several people needing to work in them at once.
Can we migrate everything in one go and be done with it?
You can, and it is also the fastest way to lose the ability to compare anything. When everything moves together and a number comes out wrong, there is no way to tell which part of the move caused it. Small waves, each with its own numeric reconciliation, locate the problem the same day it is created.
Keep reading
Related service
Access & Excel Rescue
Stabilise the file the business runs on, then fix it or move it to the web.
About the author
Yehonatan Saadia
Freelance automation, web & MVP developer
I'm Yehonatan Saadia, a senior developer who builds business automation, custom websites, and MVPs for small and mid-sized companies across the US, Europe, and Israel. These guides come from real client work, not theory.
Work with meHave a project like this?
Tell me what you're trying to automate or build. I reply within 24 business hours with a few targeted questions, then we walk through it on a free 30-minute call, with no commitment. You come away with a scope, a timeline and a fixed price - or a straight answer that it isn't worth building.