Yes, Excel can open JSON files — but only if you treat them as structured data sources, not plain text. But if you double-click a .json file hoping it'll just 'load', you’ll get garbage in column A and zero nested fields.
Quick Answer
Excel doesn’t "open" JSON like .xlsx or .csv. Instead, it imports JSON using Power Query (Get & Transform), and that’s the only reliable way to preserve arrays, objects, and hierarchies. Dragging a JSON file into Excel or using Data > From Text/CSV will fail silently — it reads the entire file as one string in cell A1.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Power Query (Data > Get Data) | Data > Get Data > From File > From JSON > Browse → Load | Nested objects, arrays, mixed types, large files (up to ~1M rows) | Requires Excel 2016+ (Windows/macOS); won’t auto-refresh unless you set up parameters |
| Paste as JSON (Power Query Editor) | Open PQ Editor > Advanced Editor > paste raw JSON → Done | Testing small samples, debugging malformed JSON, no file access | No syntax validation — invalid JSON crashes the editor |
| TEXTJOIN + SUBSTITUTE (formula hack) | Import as text → clean quotes/braces → parse with SUBSTITUTE/TEXTSPLIT | Flat, single-level JSON with consistent keys (e.g., {"name":"Alex","role":"PM"}) | Breaks on commas inside values, fails on arrays, max 8,192 chars per cell |
| Python + xlwings (add-in) | Install xlwings → run Python script → push parsed dict to Sheet1!A1 | Teams with Python access, dynamic APIs, real-time JSON ingestion | Requires admin rights, Python setup, not portable across machines |
Method 1 Deep Dive
Let’s walk through Power Query — the only method that respects JSON structure. Say you have sales_report.json containing:
{
"report_date": "2024-03-15",
"region": "APAC",
"sales": [
{"rep": "Sarah Chen", "product": "Cloud Suite", "revenue": 45200},
{"rep": "James Lee", "product": "DataShield", "revenue": 31750},
{"rep": "Maya Rao", "product": "Cloud Suite", "revenue": 52100}
]
}
Open Excel → go to the Data tab → click Get Data → From File → From JSON. Browse and select the file. Click Import. You’ll land in the Power Query Editor — not your worksheet yet. This is critical: don’t skip this step.
Here’s what you’ll see: a table with three columns — report_date, region, and sales. But sales shows List in every row. That’s Excel’s shorthand for “this is an array.” Right-click that column → Expand → check all boxes → OK. Now you get three new columns: sales.rep, sales.product, sales.revenue. Your final output lands cleanly in Sheet1 starting at A1 — with proper headers and numeric formatting on revenue.
Pro tip: If your JSON has inconsistent keys (e.g., some objects include "discount": 0.15, others omit it), Power Query will insert null — not #N/A. That’s safer than formula-based parsing. And yes, you *can* refresh this later: right-click the output table → Refresh. Just remember — if the source JSON file moves, you’ll get an error. Fix it in Power Query Editor → Home → Advanced Editor → update the file path.
Method 2 Deep Dive
The paste-as-JSON trick saves time when you’re debugging or building templates. Let’s say your API returns this snippet:
{"id":107,"client":"Acme Corp","status":"active","tags":["premium","beta"]}
Don’t save it to disk. Instead: In Excel, press Alt + A + M + O to open Power Query Editor directly. Click Blank Query → go to Home → Advanced Editor. Delete everything. Paste the JSON. Click Done. It loads instantly as a record. Click the expand icon (⇥) next to the first column. Check all four fields — including tags. When expanding tags, choose Extract Values and use comma as delimiter. Result? One row: A1 = 107, B1 = "Acme Corp", C1 = "active", D1 = "premium, beta".
Now here’s what most people miss: If you paste malformed JSON (e.g., missing quote before "beta"), Power Query won’t warn you. It just hangs or closes silently. So always validate first — I keep jsonlint.com open in a browser tab. Also: Power Query treats true/false as Boolean — not text. That means you can filter column C for status = true without quotes. Try that with formulas — good luck.
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Open Power Query Editor | Blank environment ready for JSON paste or file import | Alt + A + M + O |
| 2 | Import local JSON file | Structured preview with expandable lists/records | Alt + A + G + J |
| 3 | Expand nested array | New columns created (e.g., sales.rep, sales.revenue) | Click ⇥ icon → select fields → OK |
| 4 | Load to worksheet | Table appears starting at A1 (or your chosen location) | Ctrl + L or Home → Close & Load |
| 5 | Refresh after source change | Updates all transformed data — including expanded arrays | Right-click table → Refresh |
