Microsoft Excel is free to use in a browser or on mobile with a Microsoft account — but the desktop version requires a subscription or one-time purchase. But if you think 'free Excel' means full Excel, you’re about to lose formatting, macros, and Power Query access without warning.
The Setup
You’re managing software licensing for six regional sales teams at a midsize tech distributor. Your raw data lives in Sheet1, columns A–E: Team Name, Device Type, OS, License Type, and Activation Date. No headers yet. Data starts at A1 — and yes, row 1 is blank. Row 2 contains garbage: "Q3 Licensing Dump v2 (FINAL - DO NOT USE)." You’ll need to clean this before anything else.
| A | B | C | D | E |
|---|---|---|---|---|
| Q3 Licensing Dump v2 (FINAL - DO NOT USE) | ||||
| North America | Laptop | Windows 11 | Microsoft 365 Business Standard | 2024-02-17 |
| EMEA | Desktop | Windows 10 | Office 2021 (one-time) | 2024-01-09 |
| APAC | Surface Pro | Windows 11 | Microsoft 365 Apps for enterprise | 2024-03-05 |
| LATAM | MacBook Pro | macOS Sonoma | Microsoft 365 Personal | 2024-02-22 |
| Canada | iMac | macOS Sequoia | Office LTSC 2021 | 2024-01-30 |
| UK | ThinkPad X1 | Windows 11 | Microsoft 365 Business Basic | 2024-03-12 |
| Australia | Dell XPS | Windows 11 | Microsoft 365 Apps for business | 2024-02-28 |
| South Africa | HP EliteBook | Windows 10 | Office 2019 (one-time) | 2024-01-15 |
The Challenge
You need to answer: How much is Microsoft Excel app? That sounds simple — until you realize Excel isn’t priced alone. It’s bundled, tiered, time-bound, and device-locked. Worse: the same license shows different prices depending on whether it’s assigned to a Windows laptop (included in M365) or an iPad (requires separate Apple App Store purchase).
Your job is to extract and standardize license types, map them to actual costs, flag non-desktop entitlements, and add annual cost per seat. You can’t just Google “Excel price” and paste it — because the number changes based on who owns the license, how it’s deployed, and when it expires.
This isn’t about formulas. It’s about decoding Microsoft’s licensing taxonomy — then turning ambiguity into actionable rows.
Walking Through It
We’ll go step-by-step from that messy Sheet1 to a clean cost analysis. Do this in order — skipping steps breaks the chain.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select A1:E10 → press Ctrl+G → type A2:E10 → hit Enter → press Ctrl+C | Copies only the real data (rows 2–10), skipping the header junk in row 1 | Ctrl+G, then A2:E10 |
| 2 | Paste into Sheet2 starting at A1. Then select A1:E9 → Data → Text to Columns → Delimited → Next → uncheck all → Finish | Removes hidden non-breaking spaces and stray tabs that break VLOOKUP later | Alt+A, E |
| 3 | In Sheet2, insert new column F. Label F1 = "Annual Cost". In F2, enter: =XLOOKUP(D2,LicenseTable[Name],LicenseTable[Annual],"N/A") | Pulls cost from a separate table named LicenseTable (you’ll build it in G1:H12) | Alt+N, V (to name range) |
| 4 | In G1, type "Name". In H1, type "Annual". Paste this list: Microsoft 365 Business Standard → $120 Office 2021 (one-time) → $150 Microsoft 365 Apps for enterprise → $264 Microsoft 365 Personal → $70 Office LTSC 2021 → $439 Microsoft 365 Business Basic → $60 Microsoft 365 Apps for business → $156 Office 2019 (one-time) → $249 | Creates lookup table. Critical: no extra spaces, no typos, case-insensitive match | Alt+N, C (create table) |
Here’s the counterintuitive tip: Don’t use VLOOKUP. It fails silently when D2 has trailing spaces. XLOOKUP with exact match catches mismatches — and returns “N/A” instead of a wrong number. Try it: edit D2 to say "Microsoft 365 Business Standard " (with space at end). VLOOKUP gives $120. XLOOKUP gives “N/A”. That’s the difference between a budget error and an audit pass.
The Result
This is your final output — ready for finance review. All license names standardized. Costs accurate. Non-desktop entries flagged (see column G).
| Team | Device | OS | License | Activation | Annual Cost | Notes |
|---|---|---|---|---|---|---|
| North America | Laptop | Windows 11 | Microsoft 365 Business Standard | 2024-02-17 | $120 | Full Excel desktop |
| EMEA | Desktop | Windows 10 | Office 2021 (one-time) | 2024-01-09 | $150 | No auto-updates after 2026 |
| APAC | Surface Pro | Windows 11 | Microsoft 365 Apps for enterprise | 2024-03-05 | $264 | Includes Power Query & VBA |
| LATAM | MacBook Pro | macOS Sonoma | Microsoft 365 Personal | 2024-02-22 | $70 | Limited to 1 user, no admin controls |
| Canada | iMac | macOS Sequoia | Office LTSC 2021 | 2024-01-30 | $439 | No cloud features, 5-year term |
| UK | ThinkPad X1 | Windows 11 | Microsoft 365 Business Basic | 2024-03-12 | $60 | Web-only Excel — no desktop install |
| Australia | Dell XPS | Windows 11 | Microsoft 365 Apps for business | 2024-02-28 | $156 | Same as Business Standard, minus Teams |
| South Africa | HP EliteBook | Windows 10 | Office 2019 (one-time) | 2024-01-15 | $249 | Security updates end Oct 2025 |
What Could Go Wrong
These three mistakes appear in 8 out of 10 Excel licensing reviews I’ve audited. Spot them early.
- Mistake #1: Using "Excel" as the license name in column D. Microsoft doesn’t sell "Excel" — they sell bundles. If D2 says "Excel", your XLOOKUP returns "N/A" and you’ll miss the cost entirely. Fix: Replace all standalone "Excel" entries with the *actual* product name from Microsoft’s official pricing page.
- Mistake #2: Copying license text from a PDF or email. Those often contain invisible Unicode characters (like zero-width spaces) that look identical but break matching. Test: In cell D2, press F2, then hold ← arrow — if cursor jumps twice before hitting the first letter, there’s a hidden character.
- Mistake #3: Assuming "Microsoft 365" always includes desktop Excel. It doesn’t. Business Basic includes only Excel for the web. To verify: Open Excel on the device → File → Account → check "Product Information". If it says "Microsoft 365 Business Basic", desktop Excel won’t launch.
Next step: Run this formula in a new column to auto-flag high-risk licenses:=IF(OR(ISNUMBER(SEARCH("Basic",D2)),ISNUMBER(SEARCH("Personal",D2))),"Web-only","Desktop")
Apply it to F2:F10. Then filter on "Web-only" and confirm those devices actually run Excel Online — not the desktop app.
