You've decided to stop running your business on a spreadsheet. Good call. But before you make the move, it's worth spending an hour to prepare your Excel file properly: a tidy file becomes a tidy business app, while a messy one carries all its mess along with it.
This guide walks you through the practical steps to clean up your Excel data, fix the structure of your sheets and get clear on who needs to see what. You don't need to be a database expert, just some care and a few features you probably already use.
Why prepare your Excel file before turning it into a business app
Over the years, the workbook you use to run the company has grown one piece at a time. Columns added in a hurry, sheets duplicated "just in case", notes typed into cells, colour codes that only make sense to whoever applied them.
Excel puts up with all of that. A business app does too, but it hands the mess back to you as filters that don't work, wrong reports and duplicate customers. Cleaning up first is the fastest way to start on the right foot, and it forces you to understand how your process really works.
1. Take stock of your sheets
Open the file and list every sheet. For each one, ask what it's for and who uses it. You'll usually find three groups:
- Data sheets: customers, products, orders, invoices, stock movements.
- Calculation or summary sheets: monthly totals, commissions, dashboards.
- Dead sheets: old copies, experiments, the "Sheet1 (2)" nobody has opened in months.
Dead sheets get archived in a separate file, not carried into the app. Summary sheets aren't data: they're views the app can recalculate. The real work is in the data sheets.
2. One table per type of information
The most important rule: each data sheet describes one thing, and each row is one item of that thing. One sheet for customers, one for orders, one for products.
If your orders sheet repeats the customer's address, phone number and VAT number on every line, you're mixing two tables. Move customer details to the customer list and keep only a reference to the customer in the orders. Less repetition means fewer errors when something changes.
3. Clear headers, one header row
Every table should have a single header row at the top, with short, unique column names. Avoid:
- two-line titles or merged cells above the columns;
- unnamed columns or vague ones like "Misc" or "Column2";
- total or comment rows in the middle of the data.
A quick test: select the range and use Format as Table (Ctrl+T). If Excel picks up the headers and the range without fuss, you're on the right track.
4. Clean up your Excel data: consistent values in every column
Each column should hold one kind of data. Dates only in date columns, amounts only as numbers, statuses chosen from a fixed list. Here's what to check.
Dates and numbers
Make sure dates are real dates, not text ("March 15", "15/3", "mid-month"). Same for amounts: no "approx. $1,200" or "TBD" in a numeric column. If something is uncertain, put it in a separate notes column.
Text and spaces
Extra spaces and inconsistent capitalisation make identical values look different. The TRIM function strips unnecessary spaces, and a filter on the column instantly reveals variants like "London", "london" and "LONDON ".
Lists of values
Statuses, categories, tax rates, sales reps: anything with a limited set of options should be standardised. Use Data Validation with a list to see which values actually exist and narrow them down to a clean set. That list will become a drop-down menu in your app.
5. Remove duplicates and assign a unique ID
Duplicate customers are the classic shared-file problem. Use Remove Duplicates carefully: sort and eyeball the data first, because "Smith Ltd" and "Smith Limited" won't be recognised as the same company.
Then make sure every row has a unique ID: customer code, product code, order number. That's what lets tables link to each other without ambiguity. If you currently match orders to customers by name, now is the time to switch to codes.
6. Separate data from calculations
In workbooks that have grown over time, formulas end up everywhere, including places they shouldn't be. Before migrating:
- Identify calculated columns (line totals, due dates, margins) and write down the logic for each one in a "Rules" sheet.
- Check links to external files: they often point to paths that no longer exist.
- Look for formulas returning errors such as
#N/Aor#REF!and decide whether to fix or remove them.
Documenting your calculation rules is valuable in itself: it's business knowledge that today lives only inside cells, and it will need to be reproduced correctly in the app.
7. Decide who should see and edit what
With Excel, whoever opens the file usually sees everything. Before switching to a business app, make a simple list:
- who uses the file today (accounts, sales, warehouse, external contractors);
- which sheets or columns each of them needs;
- who should only view and who should be able to edit.
This map is the foundation for setting up users and permissions, and a concrete step towards handling personal data more carefully. If that's on your mind, we've covered the risks of customer data in shared spreadsheets.
8. Freeze a reference version
Finally, create one official version of the file. Gather the copies scattered across desktops, USB sticks and email threads, reconcile the differences and save the final file with the date in its name. From then on, that's your starting point: everything else gets archived.
Keep a backup of the original file too, before any changes. If something doesn't add up later, you can always compare.
Next step: from a clean file to a business app with Nitor
A well-organised Excel file is already a small business app blueprint: tables are your entities, headers are your fields, lists are your drop-downs and the access map is your permissions.
That's exactly where Nitor starts. It turns the Excel file you use to run your business into an online business app, hosted and maintained by Nitor. Your data lives in the cloud with backups, there's a single always-current version, everyone signs in with their own user and permissions, and several people can work at the same time without overwriting each other. When you need more features, the app can be extended.
The preparation you've done isn't wasted: it's what makes the switch quick and the result true to the way you work. Not sure it's the right time yet? Check the signs your business has outgrown Excel.
In short
Preparing your Excel file before turning it into a business app takes little time and saves a lot of trouble later. The key points:
- archive unused sheets and separate data from summaries;
- one table per type of information, with a single header row;
- consistent values in every column, standardised lists, no stray spaces;
- no duplicates and a unique ID for every row;
- documented calculation rules and a map of who sees what;
- one official version, with a backup of the original.
Get these basics right and your business app will start life in good shape.