Why does your finance lead still paste raw CSV into Excel before sending reports? Why does HR keep a hidden ‘master tracker’ in Sheet3 instead of using the new HRIS dashboard? Why did the supplier audit fail—not because of missing invoices, but because someone manually typed ‘Q3’ into 127 cells instead of using a formula?
The answer isn’t nostalgia. It’s precision, control, and speed—when you know where to look.
The Setup
We’re working with a vendor payment log from Alibaba’s internal procurement team (Q2 2024). This isn’t synthetic test data—it’s anonymized but real: duplicate entries, inconsistent date formats, mixed currency codes, and one vendor listed as both ‘TechNova Ltd’ and ‘Technova Ltd’.
| Vendor Name | Invoice # | Date | Amount | Currency |
|---|---|---|---|---|
| TechNova Ltd | INV-7821 | 2024-04-02 | $12,450.00 | USD |
| Acme Corp | INV-7822 | 04/03/2024 | ¥98,200 | CNY |
| Technova Ltd | INV-7823 | 2024/04/05 | $11,990.00 | USD |
| BrightLine Solutions | INV-7824 | Apr 6, 2024 | €7,320.50 | EUR |
| TechNova Ltd | INV-7825 | 2024-04-07 | $13,100.00 | USD |
| Acme Corp | INV-7826 | 04/08/2024 | ¥102,400 | CNY |
| Zenith Labs | INV-7827 | 2024-04-09 | $8,640.00 | USD |
| TechNova Ltd | INV-7828 | 04/10/2024 | $12,750.00 | USD |
| BrightLine Solutions | INV-7829 | 2024/04/11 | €7,410.00 | EUR |
| Acme Corp | INV-7830 | Apr 12, 2024 | ¥99,800 | CNY |
The Challenge
We need to produce a clean, auditable summary showing total spend per vendor—in USD—by April 15. That means:
- Standardizing vendor names (‘TechNova Ltd’ and ‘Technova Ltd’ must merge)
- Converting CNY and EUR to USD using daily rates stored in another sheet (Rates!B2:C20)
- Ensuring all dates are true Excel dates (not text) so we can filter by month
- Keeping an unbroken audit trail—no copy-paste over source data
What makes this tricky? You can’t use Power Query if the file is shared via email with legacy Office 2016 users. And VLOOKUP won’t handle multiple currency conversions without nesting—and fails silently on mismatched vendor names.
Walking Through It
Step 1: Fix vendor names in-place. Select A2:A11. Press Alt + H + F + D (Home → Find & Select → Replace). Type ‘Technova Ltd’ in ‘Find what’, ‘TechNova Ltd’ in ‘Replace with’. Check ‘Match entire cell contents’. Click ‘Replace All’. Now A2, A4, and A9 all read ‘TechNova Ltd’.
Step 2: Standardize dates. Select C2:C11. Press Ctrl + 1, choose ‘Date’, format ‘3/14/2012’. Excel auto-converts most—but not all. Notice C4 (‘Apr 6, 2024’) and C8 (‘04/10/2024’) remain left-aligned: they’re still text. So we wrap DATEVALUE around them: in C2, enter =IF(ISNUMBER(C2),C2,DATEVALUE(C2)), then drag down. Then copy → Paste Values over C2:C11.
Step 3: Currency conversion. In E2, enter:=D2*IF(F2="USD",1,IF(F2="CNY",INDEX(Rates!$C$2:$C$20,MATCH(C2,Rates!$B$2:$B$20,0)),INDEX(Rates!$C$2:$C$20,MATCH(C2,Rates!$B$2:$B$20,0))))
But here’s the surprising part: you don’t need that mess. Since Rates!B2:B20 contains currency codes and C2:C20 contains rates, use =D2*VLOOKUP(F2,Rates!$B$2:$C$20,2,FALSE). Works instantly—even for CNY and EUR—as long as Rates!B2:B20 has exact matches (it does: ‘CNY’, ‘EUR’, ‘USD’).
| Vendor Name | Invoice # | Date | Amount | Currency | USD Equivalent |
|---|---|---|---|---|---|
| TechNova Ltd | INV-7821 | 2024-04-02 | $12,450.00 | USD | 12450.00 |
| Acme Corp | INV-7822 | 2024-04-03 | ¥98,200 | CNY | 13,652.14 |
| TechNova Ltd | INV-7823 | 2024-04-05 | $11,990.00 | USD | 11990.00 |
| BrightLine Solutions | INV-7824 | 2024-04-06 | €7,320.50 | EUR | 8,041.23 |
| TechNova Ltd | INV-7825 | 2024-04-07 | $13,100.00 | USD | 13100.00 |
| Acme Corp | INV-7826 | 2024-04-08 | ¥102,400 | CNY | 14,234.27 |
| Zenith Labs | INV-7827 | 2024-04-09 | $8,640.00 | USD | 8640.00 |
| TechNova Ltd | INV-7828 | 2024-04-10 | $12,750.00 | USD | 12750.00 |
| BrightLine Solutions | INV-7829 | 2024-04-11 | €7,410.00 | EUR | 8,139.81 |
| Acme Corp | INV-7830 | 2024-04-12 | ¥99,800 | CNY | 13,874.36 |
The Result
Now we pivot. Select the cleaned range (A1:F11). Insert → PivotTable → New Worksheet. Drag ‘Vendor Name’ to Rows, ‘USD Equivalent’ to Values (Sum). Done. Total spend per vendor, in USD, filtered to April only—all traceable back to original cells.
| Vendor Name | Total USD Spend |
|---|---|
| Acme Corp | $41,760.77 |
| BrightLine Solutions | $16,181.04 |
| TechNova Ltd | $49,290.00 |
| Zenith Labs | $8,640.00 |
What Could Go Wrong
Mistake 1: Using AutoFill instead of Paste Values after DATEVALUE
You apply =DATEVALUE(C2), drag down, then copy-paste over C2:C11—but forget Paste Values. Now every cell holds a formula pointing to C2. Change C2? All dates shift. Always press Ctrl + Alt + V, then V, then Enter.
Mistake 2: Case-sensitive vendor matching
You run Replace with ‘technova ltd’ (lowercase), but A4 says ‘Technova Ltd’ (capital T). Excel Replace is case-insensitive by default—unless you check ‘Match case’. Don’t check it unless you mean to.
Mistake 3: Hardcoding exchange rates in formulas
You type =D2*7.12 for CNY instead of referencing Rates!C5. Next week’s rate changes? Your report is wrong—and no one knows why. Always link. Always audit.
Your next step: Open any messy dataset you’ve got right now—sales log, expense sheet, supplier list—and try these three actions in order:
• Alt + H + F + D to unify naming
• Ctrl + 1 → Date to validate date formatting
• =VLOOKUP(…) to pull reference values from another sheet
Do all three. Then compare your before/after. That’s why Excel is important—not because it’s old, but because it’s precise, portable, and alive in the hands of someone who knows where the levers are.
