A team decides to replace its main spreadsheet with a small internal tool. The tool is built, the screens look right, and then the import is run on a Friday afternoon. Dates turn into text, two customers become five, and nobody can tell which total is correct. The software was fine. The data was never prepared. This article covers the steps between "we have a spreadsheet" and "the tool holds the same information", in the order they are best done.
Start with a frozen copy
Before changing anything, save a copy of the spreadsheet as it is today and give the file a date in its name. This is the original. You never edit it, and you can always return to it. All cleaning happens in a second copy.
Also note who still edits the sheet while you prepare. If people keep adding rows for two weeks, your clean copy will be out of date on the day of the import. Agree a date after which the sheet is read-only, and plan a short final top-up for rows added since the copy.
Clean the data
Cleaning is the longest step and the one most often skipped. Look for the same few problems:
- Duplicates. The same customer typed as "Novak s.r.o.", "Novak sro" and "NOVAK". Decide which spelling is the master and merge the rest.
- Mixed formats. Dates written as 03/04/2026 and 3 April 2026 in the same column, phone numbers with and without a country code, prices with a currency symbol in some cells and not in others.
- Empty cells that mean something. A blank may mean "unknown", "not applicable" or "same as above". The tool needs one rule for each.
- Free text carrying structure. A "Notes" column that says "paid, waiting for delivery" holds two facts: a payment status and a delivery status. If the tool has fields for them, split the text.
- Leftovers. Test rows, totals typed at the bottom of the sheet, hidden columns and merged cells. These break imports quietly.
Keep a short list of what you changed and why. When someone later asks why a customer has a different name, you can answer.
Map every column
A mapping table is a simple list with three columns: the spreadsheet column, the field in the tool it goes to, and any conversion on the way. For example:
| Spreadsheet column | Tool field | Conversion |
|---|---|---|
| Customer | Customer name | Trim spaces, use the master spelling |
| Order date | Order date | Convert to one date format |
| Amount | Order value | Number only; currency in its own field |
| Notes | Internal note | Copy as text; split payment status first |
| Colour of the row | Status | Convert colour to a named status |
Two rules make the table useful. Every spreadsheet column appears in it, even if the decision is "do not import", so nothing is dropped by accident. And every required field in the tool has a source, or a stated default. Colour is a common trap: many teams use cell colours as a status, and an import reads only values, so the colour must first become a real column.
Run a test import
Never make the first import the real one. Use a copy of the tool, or a test area, and load a small batch: fifty rows is often enough, chosen to include the awkward ones, such as a long name, a missing date, a foreign address and a very large amount.
Then read the result row by row against the spreadsheet. Check that dates are the right way round, that special characters survived, that no field was cut short and that linked records, such as an order and its customer, still point to each other. Fix the cause in the cleaning step or the mapping table, not by hand in the tool, and repeat the test. Two or three rounds are normal.
Check the totals
When the test looks right, run the full import into the test copy and compare numbers. Choose a few that matter to the business and that can be computed in both places:
- the number of rows in the sheet and the number of records in the tool, allowing for rows you removed on purpose,
- the sum of order values or invoice amounts,
- the number of open items per status,
- the number of distinct customers.
Write the numbers side by side. If they differ, find out why before going on. A difference of a few records usually has a reason, such as a duplicate that was merged, and the reason should be written next to the number. An unexplained difference is a stop sign.
Keep a way back
Decide how you would undo the import before you do it. In practice that means three things. The original spreadsheet stays available and is clearly marked as the source until the new tool is trusted. The tool has a backup taken just before the real import. And you know whether imported records can be identified, for example by an "imported on" label, so they can be removed without touching records people created afterwards.
If the tool runs in parallel with the spreadsheet for a short period, say one or two weeks, state which one is the master during that time. Two masters drift apart within days. For a larger switch with a fixed cutover window, see moving data out of spreadsheets: a cutover weekend plan with rollback.
After the import
Ask two or three people who used the spreadsheet daily to look up records they know well, such as their ten biggest customers. They will find problems no total shows. Then make the spreadsheet read-only and note the date. If you are still deciding whether to switch at all, spreadsheet or small tool: four signs it is time to switch helps with that choice, and from spreadsheet to small internal tool in 30 days covers the whole project.
Common mistakes
- Importing the live sheet. People keep editing it and the copy is stale.
- Cleaning in the tool. Fixes made after the import are not repeatable.
- Skipping the mapping table. Columns are lost or land in the wrong field.
- Trusting that it looks fine. Without totals, a missing tenth of the rows goes unnoticed.
- No way back. A bad import then has to be repaired record by record.
What to do this week
Save a dated copy of your spreadsheet, list its columns in a mapping table and mark the ones you will not import. Pick fifty awkward rows and note what would break them. That one afternoon shows how much cleaning the real import needs.
Sources
- Common practice for data preparation and migration in small internal-tool projects, offered as a practical guide. The batch size, the check list and the example mapping are starting points to adapt to your data. No client results or market figures are claimed in this article.