Do not copy every workbook unchanged. Identify reference data, open transactions and history with genuine value. Define mapping and an exception rule for every field, test a representative sample, compare totals before and after, then run a short cutover with a fallback plan.
Inventory sources and owners
List client, engagement, staff, time, expense, budget, invoice and payment workbooks, including unofficial files maintained by managers. For each source, record its owner, period, frequency, row count, keys, formulas, macros, known duplicates and business use. Two files with the same name may implement different rules.
Select the right amount of history
Migrating all history may cost more than its value. A common approach is to migrate active reference data, open engagements, balances and unclosed transactions, followed by the limited history required for comparison. Older files remain in a read-only archive with their date and owner. The decision must reflect applicable record-keeping requirements and evidential needs.
Build explicit mapping
| Excel source | Target | Transformation | Control |
|---|---|---|---|
| Client code | Client identifier | Remove spaces and normalise case | Uniqueness |
| Engagement name | Engagement label | Keep text and add stable code | Client required |
| Duration | Time | Convert HH:MM to minutes | Weekly total |
| Gross amount | Invoice | Split net and tax from approved source | Total by month and rate |
| Free-text status | Coded state | Approved lookup table | Reject unknown value |
Handle duplicates and identifiers
Define the business key before deduplication. Two people named Smith are not necessarily the same person; one client may have several entities. Never merge automatically on label alone. Retain a cross-reference from source ID to target ID so that the migration can be explained and repeated.
Reconcile before opening to users
- Count of active clients, engagements and staff.
- Total hours by month, team and engagement.
- Invoice count and amount by period, net, tax and gross.
- Client balances and unallocated payments.
- Open budgets and, if migrated, work to complete.
- Count of rejected, corrected and deliberately excluded rows.
Business owners, not only the technical team, must approve the totals. Retain frozen source files as reference data for comparison with the migrated data.
Plan your migration
- Define the source data formats. The files to migrate, their columns and formats, and the mapping rules are identified.
- Schedule training. Dates, user groups and topics are agreed early enough to fit into the cutover timetable.
- Stop working in Excel. At the agreed time, data entry is frozen to prevent discrepancies between the source files and the new environment.
- Migrate the data. The final files are exported, checked, transformed and then imported into the new environment.
- Make the environment available with the integrated data. Authorised users receive access to their environment containing the migrated reference data and transactions.
- Verify the data. Business owners compare the migrated data with the frozen source files and approve the totals and key records.
- Train users. Teams learn how to carry out their day-to-day tasks in the new environment using examples close to their work.
- Start billing. Once validation is complete, the first invoices are prepared and issued in the new environment.
- Support the first monthly billing run. Support is provided throughout this first cycle to check the complete process and address questions quickly.
See also: choosing software, imports, exports and APIs, user training and the operational FAQ.