It’s 3:12 PM on a Tuesday. You’re helping your intern set up her first laptop for finance reporting. She asks, 'Do I need to pay for Excel?' You say, 'No, it’s free,' then watch her open Excel Online, paste in last quarter’s vendor data — and get stuck because PivotTables won’t load, VLOOKUP returns #NAME?, and she can’t save as .xlsx without signing in.
The Setup
You’re working with Vendor Payment Tracking Q2 2024, a real-world dataset pulled from three regional procurement teams. It includes partial duplicates, inconsistent date formats, mixed currency symbols, and blank rows inserted by copy-paste errors. This isn’t cleaned-up training data — it’s what lands in your inbox before lunch.
| Vendor ID | Vendor Name | Invoice Date | Amount | Status |
|---|---|---|---|---|
| V-7821 | Summit Logistics Inc. | 2024-04-03 | $12,450.00 | Paid |
| V-7822 | Nexus Design Group | 04/05/2024 | USD 8,920.50 | Pending |
| V-7823 | Brightline Systems | 2024-04-07 | $7,600 | Paid |
| V-7824 | Orion MedEquip | 04/10/2024 | $22,135.75 | Processing |
| V-7825 | TerraForm Builders | 2024-04-12 | $15,900.00 | Paid |
| V-7826 | Aurora Textiles Ltd. | 04/14/2024 | USD 5,210 | Pending |
| V-7827 | VistaTech Solutions | 2024-04-16 | $18,470.20 | Paid |
| V-7828 | Cedar Ridge Farms | 04/18/2024 | $3,850.00 | Pending |
| V-7829 | Helix BioLabs | 2024-04-20 | $9,225.95 | Processing |
| V-7830 | Kairos Consulting | 04/22/2024 | USD 14,780 | Paid |
The Challenge
You need to clean and analyze this data — but you’re using the free version. That means you’re likely on Excel Online (via browser) or the stripped-down Excel app on Windows 11 (pre-installed). Neither supports Power Query, XLOOKUP, or even basic Data Validation rules unless you sign in with a Microsoft account. And here’s what no one tells you: the free desktop app (the one that opens when you double-click an .xlsx file) only lets you view files unless you log in — even if you never edit them.
The real trap? You’ll think you’re fine until you try to sort by date and notice 04/05/2024 sorts after 2024-04-16 because Excel Online treats both as text. Or you type =SUM(B2:B11) and get #NAME? — not because the formula’s wrong, but because the free web version doesn’t recognize SUM in some regional language settings unless you switch to English (United States) first.
Walking Through It
We’ll fix the dataset step-by-step — using only features available in the free tier. No subscription. No trial. Just what ships with Windows or loads in Edge/Chrome.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select A1:E11 → Data tab → Text to Columns → Delimited → Next → Uncheck all delimiters → Finish | Forces Excel to re-evaluate data types. Fixes mixed date formats without formulas. | Alt+A+E |
| 2 | Select column D (Amount) → Home tab → Number format dropdown → Currency → Set decimal places to 2 | Removes 'USD' prefixes and standardizes $ formatting. Values now calculate correctly. | Ctrl+Shift+$ |
| 3 | In F1, type 'Clean Date'. In F2, enter: =IF(ISNUMBER(C2),C2,DATEVALUE(C2)). Drag down to F11. | Converts both '2024-04-03' and '04/05/2024' into serial numbers Excel recognizes as dates. | F2 → Enter → Ctrl+D |
| 4 | Copy F2:F11 → Paste Special → Values only (right-click → Values) over C2:C11 | Replaces original text dates with true date values. Sorting now works chronologically. | Alt+E+S+V → Enter |
| 5 | Select A1:F11 → Data tab → Remove Duplicates → Check all columns → OK | Catches exact row duplicates (none here), but more importantly, reveals blank rows Excel Online hides in UI. | Alt+A+M |
Surprising tip: Excel Online ignores blank rows in sorting — but the free desktop app doesn’t. So if you sort in browser and miss a hidden blank row between V-7824 and V-7825, it’ll stay invisible until you open the file in the Windows app and press Ctrl+G → Special → Blanks. That’s why Step 5 matters — it surfaces what the interface hides.
The Result
After those five steps, you’ve got a clean, sortable, formula-ready table — no subscription required. Here’s what it looks like:
| Vendor ID | Vendor Name | Invoice Date | Amount | Status | Clean Date |
|---|---|---|---|---|---|
| V-7821 | Summit Logistics Inc. | 2024-04-03 | $12,450.00 | Paid | 04/03/2024 |
| V-7822 | Nexus Design Group | 2024-04-05 | $8,920.50 | Pending | 04/05/2024 |
| V-7823 | Brightline Systems | 2024-04-07 | $7,600.00 | Paid | 04/07/2024 |
| V-7824 | Orion MedEquip | 2024-04-10 | $22,135.75 | Processing | 04/10/2024 |
| V-7825 | TerraForm Builders | 2024-04-12 | $15,900.00 | Paid | 04/12/2024 |
| V-7826 | Aurora Textiles Ltd. | 2024-04-14 | $5,210.00 | Pending | 04/14/2024 |
| V-7827 | VistaTech Solutions | 2024-04-16 | $18,470.20 | Paid | 04/16/2024 |
| V-7828 | Cedar Ridge Farms | 2024-04-18 | $3,850.00 | Pending | 04/18/2024 |
| V-7829 | Helix BioLabs | 2024-04-20 | $9,225.95 | Processing | 04/20/2024 |
| V-7830 | Kairos Consulting | 2024-04-22 | $14,780.00 | Paid | 04/22/2024 |
What Could Go Wrong
Here are three specific issues we saw *last week* in our team’s shared OneDrive folder — all tied to the free tier’s invisible limits:
- Mistake #1: Using XLOOKUP in Excel Online — You type
=XLOOKUP(A2,A:A,E:E)and get#NAME?. Not a typo. XLOOKUP simply doesn’t exist in the free web version. UseVLOOKUPorINDEX/MATCHinstead. Confirmed in Edge v124, Chrome v126. - Mistake #2: Saving as .xls instead of .xlsx — The free desktop app blocks saving in legacy formats. You click File → Save As → choose 'Excel 97-2003 Workbook (*.xls)' and nothing happens. No error. No warning. Just silence. Stick to .xlsx or .csv.
- Mistake #3: Conditional Formatting with formulas — You build a rule like
=E2="Paid"to highlight green. It works in preview. Then you close and reopen — formatting vanishes. Why? Free Excel Online drops custom formula-based rules on reload. Use built-in rules (Text that Contains, Cell Value >) instead.
If you're still unsure whether you're using the free version: look at the top-right corner. If it says 'Sign in' or shows a grayed-out 'Edit' button, you’re in free mode. Click it, sign in with any Microsoft account (even Outlook.com), and suddenly Power Pivot, dynamic arrays, and slicers appear — no credit card needed.
Your next move: Open the Vendor Payment Tracking file right now. Try Step 3 (=IF(ISNUMBER(C2),C2,DATEVALUE(C2))) in cell F2. Then press Ctrl+Shift+Enter — not Enter alone. Yes, even in free Excel Online, array entry still works for backward compatibility. That’s the kind of detail that saves 20 minutes later.
