Where leads get lost in a move
The new system is rarely the problem. Leads go missing in four places: the sheet itself (duplicates, merged cells, notes only one agent understands), the file (phone numbers that lose their leading zero), the import (a column sent to the wrong field), and the weeks afterwards, when half the team is still typing in Excel.
The leading zero deserves a warning of its own. Microsoft documents that Excel removes leading zeros from numbers such as phone numbers and personal identification numbers. A Malaysian mobile number starts with 0, so a number stored as a number can arrive as 1X-XXX XXXX and fail when someone tries to call it. The first six digits of an IC number are the date of birth, so anyone born from 2000 to 2009 has a number that starts with a zero too.
Excel also keeps only 15 digits of precision in a number, so very long codes must be stored as text. Both problems have the same fix, which comes next.
Before you touch the file
- Gather every list. Agents' own sheets, exported WhatsApp contacts, the boss's master list. Decide which one is the truth for each lead.
- Pick a cut-off date. After it, every new lead goes straight into the new system.
- Make a copy and work on the copy. Name it with today's date. The original stays untouched.
- Count the rows. Write the number down. You will compare it with the system after the import.
- Name one person who owns the move. One person, not a committee.
The sheet is a list of people's personal data, so keep the working copy in one place with limited access. Once the import is checked, delete stray copies from phones, email and USB drives. Our guide on keeping credit reports and IC copies safe covers the habits.
Clean the copy
Most sheets have the same eight problems. Fix them in the copy, where a mistake costs nothing.
| Problem | What it looks like | Fix |
|---|---|---|
| Phone number lost its zero | The number starts with 1 instead of 01 | Format the column as Text before you paste the numbers in, and put the zero back |
| Mixed phone formats | +60, 60, 0, spaces and dashes in one column | Pick one format for every number and convert the whole column |
| The same person twice | Two agents' sheets, two spellings, different notes | Match on phone number, merge the notes, keep the earliest date and decide who owns the lead |
| Status typed freely | 'Called', 'called back', 'CB', 'follow up' | Agree a short fixed list of stages and map each old word to one stage |
| Several facts in one cell | A name and a phone number together, or three notes in one cell | Split into columns, and keep old notes in one notes field with their dates |
| Meaning kept in colour | Red text means do not call | Add a column that says it in words, because colours do not survive an import |
| Merged cells and blank rows | Headings across columns, empty lines between groups | Unmerge, fill down, delete blank rows, and leave one row per person |
| Dates typed as text | '3/4' could be 3 April or 4 March | Rewrite every date in one unambiguous format, such as 2026-04-03 |
The sheet, as it is
| Name | Phone | Status |
|---|---|---|
| Aisyah R. | 1X-000 0001 | called, CB fri |
| Aisyah Rahman | +60 1X-000 0001 | no answer |
| Tan | 01X-000 0002 | follow up |
- The phone number lost its zero.
- One person on two rows, with two spellings and two formats.
- Red text means do not call, and a colour does not survive an import.
- Status typed freely: three words for one stage.
Clean a copy, then import
In the new system
- Name
- Aisyah Rahman
- Phone
- +60 1X-000 0001
- Stage
- Contacted
- Next step
- Call back, Friday
- Notes
- Merged from two rows
- Name
- Tan
- Phone
- +60 1X-000 0002
- Contact allowed
- No
Import in stages
If you are buying a system, ask who does the import and whether they check it with you. Some vendors do and some leave it to you. NexFlows does it free on custom builds and includes it in the Loan Advisory CRM setup.
Map the columns
For each column, name the field it goes to in the new system. Decide where anything with no home will live, usually a notes field.
Test with fifty rows
Import a small sample that includes the messy rows. Open ten records and compare each one with the sheet, field by field.
Fix the rule, not the row
If the same fault shows up in several records, fix the cleaning step and run it again, so the full import is clean too.
Import everything, then count
Rows in the sheet should equal records in the system, plus duplicates you merged, plus rows you rejected on purpose. If the sum is off, find the gap before you go on.
Check the leads that matter most
Open the hot ones: leads with a call due this week, leads waiting for documents, anything with money attached. Check the owner, the stage and the next date on each.
Switch over on one day
Choose a quiet day, tell the team the date in advance and have the owner of the move on hand. From that morning Excel is read-only: save one copy for reference, remove edit rights, and rename the file so nobody mistakes it for the live list. New leads, calls and notes go into the new system only.
- At the end of day one, check that every agent can see their own leads and nobody else's.
- List the leads that exist only in someone's WhatsApp chats or notebook, and enter them during the first week.
- After one week, count again: new leads in the system against what the team says came in.
- Keep the archived sheet in one safe place, not on personal phones, and delete it when you no longer need it. Ask a professional how long you must keep customer records.
Questions people ask next
- Should I clean the data before or after the import?
- Before, on a copy. It is far easier to fix a spreadsheet than a live system, and a clean import means your first week is not spent hunting for duplicates.
- What do I do with old leads that went cold?
- Import them with a stage that says so, such as 'Old, not contacted', so they do not clutter active work. Do not delete them just because you are moving. Check how long you need to keep customer records first.
- Can I keep using Excel for some things?
- Yes, for reports you export from the system. Not as a second place to enter or update leads.
Sources
This guide is general information, not legal, tax or financial advice. Rules and bank policies change, so check the current position with the bank, the regulator or a professional before you act.