Turning Excel Into a CRM

• 30,000+ lead records in one workbook
• 160+ fields tracked per record
• 6 sources merged into one structure

Before the CRM Conversion Engine existed, someone still had to compile and maintain the data it would later automate. That someone was me, and the tool was Excel.

Every month, lead records arrived from six different sources: two CRMs, two lead-data providers, a legacy database export, and purchased lists. Each had its own format, its own quirks, and no shared structure. The compilation workbook grew into a database of 30,000+ records across 160+ fields, and it had to stay accurate, deduplicated, and reportable without any outside tooling.

You can download a small sample of the workbook from this page. Its 450 rows are made up, but the sheet structure and the formulas described below are the real ones, so you can open it and see them work.

Database snapshot

Cleaning company names with one formula

The core challenge was consistency. The same company showed up under slightly different names depending on the source: “Redwood Group Pte Ltd” in one export, “Redwood Group Pte. Ltd.” in another. Normalizing that by hand at this scale wasn’t realistic.

So I built a cleanup formula that strips legal suffixes automatically. It combines TRIM, LEFT, and an array-entered FIND against a keyword list of common suffixes (Pte, Ltd, Sdn Bhd, Inc, Limited, Tbk, PLC, and more), checks a name against every suffix in one pass, and trims it at the earliest match:

=TRIM(LEFT(C2,MIN(IFERROR(FIND(Keywords!$A$1:$A$40,C2),LEN(C2)+1))-1))

Adding a new suffix later just meant adding a row to the keyword list, not rewriting the formula.

A dashboard that never goes stale

Reporting was the second problem. The numbers people asked for most were how many active and inactive leads existed per source and per year, and nobody should have to open the raw sheet to get them.

I built a dashboard sheet that uses SUMIFS across the whole database, cross-referencing a computed year field against every source and status combination. Every cell recalculates as records are added or a status changes, so the summary always matches the raw data.

One real quirk this reflects: the raw source field sometimes stored the same source name in two different capitalizations, a manual-entry habit the dashboard and lookups had to tolerate rather than assume away.

Treating the database as a living system

Day to day, the workbook was paired with a task-tracking sheet that logged record counts by source and status. Drift or stalled updates showed up immediately instead of weeks later during a reporting cycle.

That habit of tracking the database like a living system, not a static file, is what eventually made the case for automating the whole pipeline. The patterns I leaned on most here, source-aware cleanup rules, a normalized identity field, and status-based aggregation, are the same ones the Python tools later rebuilt at a much larger scale.

What I learned

Good structure outlives the tool. The rules I designed in Excel carried straight over into Python. Only the scale changed.

Watch the data, not just the reports. Daily counts caught problems long before any monthly report would have.