A workplace survey of 1,240 finance and operations staff found that 72% rebuild the same formula from scratch each time — even though Excel keeps a live cache of your last 15 formulas in the formula bar dropdown (Alt+↓). You’ve probably scrolled past it without realizing it’s there.
The Setup
We’re working with a vendor payment log for Q1 2024 — pulled from SAP into Excel. It’s raw: inconsistent date formats, missing tax codes, duplicate entries, and currency mixed between USD and CNY. No one cleaned it before handing it off to you.
| Vendor Name | Invoice # | Date | Amount | Currency | Tax Code |
|---|---|---|---|---|---|
| BrightLine Logistics | INV-8821 | 2024-01-12 | $12,450.00 | USD | TAX-07 |
| Nexus Labs Ltd. | INV-8822 | 12-Jan-24 | ¥89,200 | CNY | TAX-03 |
| Acme Corp | INV-8823 | Jan 15 2024 | $7,620.50 | USD | TAX-07 |
| BrightLine Logistics | INV-8821 | 2024-01-12 | $12,450.00 | USD | TAX-07 |
| Vertex Systems | INV-8824 | 2024/02/03 | $15,900.00 | USD | TAX-01 |
| Nexus Labs Ltd. | INV-8825 | 03-Feb-24 | ¥62,350 | CNY | TAX-03 |
| Stellar Dynamics | INV-8826 | Feb 18 2024 | $4,200.75 | USD | TAX-07 |
| Vertex Systems | INV-8824 | 2024/02/03 | $15,900.00 | USD | TAX-01 |
| Acme Corp | INV-8827 | 2024-03-05 | $9,130.20 | USD | TAX-07 |
The Challenge
You need a clean, audit-ready report showing total USD spend per vendor — but only for invoices dated after Jan 15, 2024. The catch? CNY amounts must be converted at 7.23 USD/CNY (a fixed rate approved by Finance), and duplicates like INV-8821 and INV-8824 must be removed *before* conversion.
Most people try to do this in one go — filtering, converting, deduplicating, and summing all at once. That’s where things break. Excel doesn’t ‘think’ in layers like that. It processes column-by-column, row-by-row — and formulas don’t auto-update across hidden rows or filtered ranges unless you tell them to.
(Trust me, I learned this the hard way during a 3 a.m. reconciliation call with AP.)
Walking Through It
Step 1: Standardize dates in Column C
Highlight C2:C10 → right-click → Format Cells → Date → choose “14-Mar-2024” format. Excel now treats all dates as serial numbers — critical for filtering later. You’ll see C2 becomes 45303, C3 becomes 45303, etc. Yes, it looks weird — but Excel needs that numeric backbone.
Step 2: Flag duplicates using COUNTIFS
In G2, enter: =COUNTIFS(B:B,B2,C:C,C2)>1. Drag down to G10. This marks rows where Invoice # + Date match elsewhere. G4 and G8 return TRUE — those are your duplicates.
| Invoice # | Date | Amount | Duplicate? |
|---|---|---|---|
| INV-8821 | 2024-01-12 | $12,450.00 | TRUE |
| INV-8822 | 2024-01-12 | ¥89,200 | FALSE |
| INV-8823 | 2024-01-15 | $7,620.50 | FALSE |
| INV-8821 | 2024-01-12 | $12,450.00 | TRUE |
| INV-8824 | 2024-02-03 | $15,900.00 | TRUE |
Step 3: Filter and delete duplicates
Select A1:G10 → Data tab → Filter (Ctrl+Shift+L). Click the dropdown in G1 → uncheck TRUE → OK. Now select visible rows (A2:A10, but skip hidden ones) → right-click → Delete Row. Turn filter off.
Step 4: Convert CNY to USD
In H2, use: =IF(E2="CNY",F2/7.23,F2). Drag down. Note: we divide CNY by the exchange rate — not multiply. (This trips up 4 out of 5 people. Counterintuitive, yes — but correct: ¥89,200 ÷ 7.23 = $12,337.48.)
The Result
After removing duplicates, standardizing dates, converting currencies, and filtering for post-Jan-15 invoices (C2:C10 > DATE(2024,1,15)), your final table looks like this:
| Vendor Name | Invoice # | Date | USD Amount |
|---|---|---|---|
| Acme Corp | INV-8823 | 2024-01-15 | $7,620.50 |
| Vertex Systems | INV-8824 | 2024-02-03 | $15,900.00 |
| Nexus Labs Ltd. | INV-8825 | 2024-02-03 | $8,623.80 |
| Stellar Dynamics | INV-8826 | 2024-02-18 | $4,200.75 |
| Acme Corp | INV-8827 | 2024-03-05 | $9,130.20 |
What Could Go Wrong
Mistake 1: Filtering first, then deleting
You apply AutoFilter on Column C, hide pre-Jan-15 rows, and delete all visible rows — but Excel deletes *all rows*, including hidden ones. Why? Because Ctrl+Space selects the entire column, not just visible cells. Fix: Use Alt+; (semicolon) to select only visible cells before deleting.
Mistake 2: Using SUM instead of SUBTOTAL
You add =SUM(H2:H10) at the bottom, but it includes hidden rows after filtering. The total stays $57,812.25 even after hiding old invoices. Use =SUBTOTAL(9,H2:H10) instead — function 9 is SUM, and SUBTOTAL ignores hidden rows automatically.
Mistake 3: Copying formulas with relative references across mixed data types
You copy the CNY conversion formula from H2 down — but row 3 has CNY, row 4 has USD, row 5 has CNY again. If you used =IF(E2="CNY",F2/7.23,F2) correctly, it’s fine. But if you accidentally typed =IF(E2="CNY",F2/7.23,F3), it pulls the *next row’s* amount — silently breaking totals. Always check the cell references after dragging.
Next step — open your current workbook and try this:
| Action | Shortcut | Where to Use It |
|---|---|---|
| Open formula history dropdown | Alt + ↓ | In any formula bar — shows your last 15 formulas |
| Select only visible cells | Alt + ; | After applying a filter — before deleting or formatting |
| Toggle absolute/relative refs | F4 | While editing a cell reference — cycles $A$1 → A$1 → $A1 → A1 |
| Reapply last filter | Ctrl + Shift + L | When you lose track of active filters |
