Notes for owners · Software Audit and Digital Transformation
How to Check Your Spreadsheet Data Before Migrating It
Check your spreadsheet data by finding the master copy, counting blanks and bad formats, matching duplicate parties and reading every formula that sets a price. Then pick the totals you will reconcile against. GullySystem’s migration service runs these checks on copies, with your original files left as they were.
Ganesh HS, Strategy and Technology, GullySystem · · 3 min read
Find every copy and agree which one is master
Collect every file that holds customers, items, rates or balances. Ask who edits each one and how often. Include the copies people keep on their own laptops.
Where two copies disagree, someone must say which the business relies on. Write that decision down. Without it, the cleanup argues about files instead of fixing records.
Count the gaps instead of guessing
Most owners believe their data is mostly fine. A simple count often says otherwise. Use filters or a short script and note the numbers.
Counts turn a vague worry into a list of decisions. Some fields can be filled from other records. Others need a person to look them up.
- Blank cells in required fields such as phone or unit
- Dates stored as text or in mixed formats
- Text sitting in number columns
- Phone numbers with the wrong length
- GST numbers that fail a basic format check
- Rows that contradict each other
Look for one party under several names
The same customer can appear as a full name, a short name, a name with the locality and an abbreviation. Each carries its own balance. The true outstanding is only visible once they are combined.
Sort by phone number, GST number and address to find likely pairs. Do not merge automatically. Show both records to someone who knows the customer and let them decide.
Read the formulas before trusting the totals
Pricing slabs, commission and discounts often live in nested conditions. Open hidden sheets and macros too. The author may have left years ago.
Write each rule in plain words. Then take it to the person who uses the output and ask if it matches practice. Flag any rule nobody can confirm rather than carrying it across as a guess.
- Nested conditions that set a rate or slab
- Lookups pointing at hidden or external sheets
- Macros that change values when run
- Hard-typed numbers sitting inside formulas
Pick the totals you will reconcile against
Before any load, agree which reports count as the truth. The trial balance, the receivables ageing and the last stock count are typical choices.
After each test load, compare those totals and list every row held back with a reason. A load that quietly drops rows is worse than one that reports its failures.
Pre-migration data health sheet
One row per source file, with columns for keeper, record count, blanks, bad formats and suspected duplicates. Add the formulas found and whether each rule was confirmed. The totals column lists the report each file will be reconciled against.
Open a blank worksheet to printQuestions owners ask
Should we clean the data before choosing new software?
Start the checks early, since they show how much work the move involves. Final cleanup is easier once the new system’s fields are known, because the mapping decides what each value must look like.
Can a script merge duplicate customers for us?
Not on its own. A script can find likely pairs across spellings and phone numbers. A person who knows the customers should decide which record survives and what happens to its history.
Does the accountant need to be involved?
Yes, for balances. Party balances, receivables and stock value should tie back to the trial balance or the last audited figures. The person who files the returns is best placed to check that.
What if our old software has no export?
The data can often still be read from the database underneath, or recovered from exported reports. Check what can be recovered before agreeing a plan, and note anything that must be re-entered.