Excel is useful because it turns messy, human-driven workflows into repeatable, auditable processes — but most people only use it to add up columns and call it a day.
The Setup
Last month, Sarah Chen in Finance received a raw export from the Alibaba CRM: 973 rows of sales leads across APAC. No formatting. Mixed date formats. Duplicate entries. And one column titled "Status" that held values like "Follow Up", "follow-up", "F/U", and "CLOSED (won)". She needed to produce a clean weekly report for leadership by Thursday at 10 a.m. — no extensions.
| Lead ID | Company | Contact | Amount (USD) | Date Added | Status |
|---|---|---|---|---|---|
| LEAD-8821 | NexaLogistics Pte Ltd | Rajiv Tan | $12,450 | 2024-03-10 | Follow Up |
| LEAD-8822 | BrightWave Tech | Maya Lopez | $8,900 | 03/11/2024 | follow-up |
| LEAD-8823 | Acme Corp Japan | Kenji Sato | $22,100 | 2024/03/12 | F/U |
| LEAD-8824 | SkyBridge Solutions | Aisha Rahman | $15,600 | Mar 13 2024 | CLOSED (won) |
| LEAD-8825 | VistaMed Group | Dr. Lin Wei | $31,200 | 2024-03-14 | Won |
| LEAD-8826 | TerraNova Energy | Elena Petrova | $9,750 | 03/14/2024 | pending |
| LEAD-8827 | Zephyr Design Co | Thomas Okafor | $5,200 | 2024/03/15 | F/U |
| LEAD-8828 | Orion Systems | Sofia Kim | $18,300 | Mar 15 2024 | Follow Up |
The Challenge
Sarah had to deliver three things: (1) a cleaned list where all Status values map to one of four categories — Open, Follow-Up, Won, or Lost; (2) Amounts formatted consistently as currency with two decimals; (3) Dates converted to ISO format (YYYY-MM-DD) and sorted chronologically. The catch? She couldn’t ask IT for help — and her laptop was running Excel 2019 on Windows 10, so Power Query wasn’t available in the ribbon without enabling it first. Also, she noticed that cell D2 contains "$12,450" — but Excel treats that as text, not a number. That breaks SUM() later.
Walking Through It
She started at A1 and worked left-to-right, top-to-bottom — no fancy macros, just built-in tools she already knew.
Step 1: Fix inconsistent dates
She selected column E (E1:E973), then pressed Alt + A + E to open Text to Columns. Chose “Delimited”, unchecked all delimiters, clicked Next → Next → chose “Date: YMD” → Finish. Instantly, all dates became serial numbers Excel recognized — and she applied yyyy-mm-dd format via Home → Number Format dropdown.
Step 2: Clean the Status column
In F1, she typed Status Clean. In F2, she entered:=IF(OR(LOWER(E2)="follow up",LOWER(E2)="follow-up",LOWER(E2)="f/u"),"Follow-Up",IF(OR(LOWER(E2)="closed (won)",LOWER(E2)="won"),"Won","Open"))
Then dragged down to F973. This handled case variations and abbreviations in one go. (Yes, it’s long — but faster than Find & Replace five times.)
Step 3: Convert dollar amounts
Column D held mixed text/numbers. She selected D2:D973, pressed Ctrl + H, replaced $ with nothing, then , with nothing. Then used VALUE(D2) in G2, dragged down, and copied → Paste Values over D2:D973. Finally, applied Currency format.
| Lead ID | Company | Contact | Amount (USD) | Date Added | Status Clean |
|---|---|---|---|---|---|
| LEAD-8821 | NexaLogistics Pte Ltd | Rajiv Tan | $12,450.00 | 2024-03-10 | Follow-Up |
| LEAD-8822 | BrightWave Tech | Maya Lopez | $8,900.00 | 2024-03-11 | Follow-Up |
| LEAD-8823 | Acme Corp Japan | Kenji Sato | $22,100.00 | 2024-03-12 | Follow-Up |
| LEAD-8824 | SkyBridge Solutions | Aisha Rahman | $15,600.00 | 2024-03-13 | Won |
| LEAD-8825 | VistaMed Group | Dr. Lin Wei | $31,200.00 | 2024-03-14 | Won |
The Result
By 9:42 a.m. Thursday, Sarah pasted the final table into a new sheet named "Clean_Leads_202403". She added a pivot table summarizing total value by Status, inserted a slicer for Date Added, and emailed the link to the shared drive. Her manager replied: “This is exactly what Sales Ops needs.”
| Lead ID | Company | Contact | Amount (USD) | Date Added | Status Clean |
|---|---|---|---|---|---|
| LEAD-8821 | NexaLogistics Pte Ltd | Rajiv Tan | $12,450.00 | 2024-03-10 | Follow-Up |
| LEAD-8822 | BrightWave Tech | Maya Lopez | $8,900.00 | 2024-03-11 | Follow-Up |
| LEAD-8823 | Acme Corp Japan | Kenji Sato | $22,100.00 | 2024-03-12 | Follow-Up |
| LEAD-8824 | SkyBridge Solutions | Aisha Rahman | $15,600.00 | 2024-03-13 | Won |
| LEAD-8825 | VistaMed Group | Dr. Lin Wei | $31,200.00 | 2024-03-14 | Won |
| LEAD-8826 | TerraNova Energy | Elena Petrova | $9,750.00 | 2024-03-14 | Open |
| LEAD-8827 | Zephyr Design Co | Thomas Okafor | $5,200.00 | 2024-03-15 | Follow-Up |
| LEAD-8828 | Orion Systems | Sofia Kim | $18,300.00 | 2024-03-15 | Follow-Up |
What Could Go Wrong
Mistake #1: Using Find & Replace on Status before standardizing case
You replace "Follow Up" with "Follow-Up", but miss "follow-up" and "F/U" because they’re lowercase or abbreviated. Result: 32% of statuses remain uncleaned. Always start with =LOWER() or =TRIM() in a helper column.
Mistake #2: Applying Number Format before converting text to numbers
If you select D2:D973 and click Currency format while cells contain "$12,450", Excel displays "$12,450.00" — but the underlying value is still text. SUM() returns zero. You must use VALUE() or Paste Special → Multiply by 1 first.
Mistake #3: Sorting without selecting the full data range
Sarah once sorted only column E (Dates), leaving Leads IDs and Companies misaligned. She spent 22 minutes reconstructing row order from backups. Pro tip: Press Ctrl + A twice — first selects current region, second selects entire sheet — then sort.
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Manual Find & Replace (5 passes) | 18 min | 73% | Low |
| Text to Columns + IF + VALUE | 4.2 min | 99.8% | Medium |
| Power Query (if enabled) | 2.1 min | 100% | High (setup) |
| VBA macro (pre-written) | 1.3 min | 100% | Very High |
