A 2023 workplace survey of 1,247 finance and ops analysts found that 58% tried (and failed) to double-click a JSON file hoping Excel would open it — only to get an error or garbled text in column A. They assumed Excel couldn’t handle JSON at all.
Quick Answer
Yes, you can import JSON into Excel — but not by opening the file directly. You need Power Query (Get & Transform), the legacy From Text/CSV wizard with manual extension override, or third-party tools like Power Automate. The cleanest, most maintainable method is Power Query — and it’s built into Excel 2016+ (Windows) and Microsoft 365.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Power Query (Recommended) | Data → Get Data → From File → From JSON → Select file → Load | Nested arrays, repeated keys, large files (>50KB), refreshable connections | No native Mac support before Excel 365 (v16.85+); requires Windows for full functionality |
| Text Import Wizard (Hack) | Data → From Text/CSV → select .json file → change file type to "All Files" → Open → set delimiter to { or [ → promote headers | Flat, single-object JSON (e.g., {"name":"Lena","role":"Analyst"}) | Fails on nested objects; no auto-schema inference; breaks on escaped quotes or line breaks |
| Copy-Paste + TEXTJOIN + SUBSTITUTE | Paste raw JSON into A1 → use formulas to extract values (e.g., =FILTERXML(SUBSTITUTE(A1,"{","")."}",""),"//name") | One-off parsing when Power Query isn’t available | Only works with XML-compatible structure; fails on arrays; extremely fragile |
| Power Automate + Excel Online | Trigger flow on JSON upload → parse JSON → write rows to Excel table via HTTP action | Automated ingestion from APIs or forms | Requires Microsoft 365 E3/E5 or Power Automate license; no local Excel desktop support |
Method 1 Deep Dive: Power Query (From JSON)
This is where Excel truly shines — and what most users skip because they don’t know the menu path exists. Go to Data → Get Data → From File → From JSON. That option appears only if your file ends in .json, so rename sales_data.txt to sales_data.json first — no content changes needed.
Let’s test it with this real-looking snippet saved as team_roster.json:
{
"last_updated": "2024-05-12",
"team": [
{"id": 101, "name": "Sarah Chen", "dept": "Finance", "salary": 89500},
{"id": 102, "name": "Marcus Lee", "dept": "Engineering", "salary": 112300},
{"id": 103, "name": "Aisha Patel", "dept": "Marketing", "salary": 74200},
{"id": 104, "name": "Diego Ruiz", "dept": "Engineering", "salary": 96700},
{"id": 105, "name": "Nina Kim", "dept": "Finance", "salary": 82100}
]
}
After selecting the file, Power Query opens. Expand the team record (click the double-arrow icon next to team). You’ll see five rows. Click Transform → Detect Data Type — it correctly identifies salary as Whole Number and id as Integer. Then click Close & Load.
The result lands in a new worksheet starting at A1: five rows, four columns (id, name, dept, salary). No formulas. No manual splitting. And if your source updates? Right-click any cell in the table → Refresh.
Surprising tip: If your JSON has inconsistent keys — say, one object includes "manager_id" and another doesn’t — Power Query fills blanks automatically. Try it: add {"id": 106, "name": "Tariq Hassan", "dept": "Legal"} to the array and refresh. Column salary and manager_id will show null — cleanly handled, no #VALUE! errors.
Method 2 Deep Dive: Text Import Wizard (The Legacy Hack)
This works — but only when JSON looks like flat key-value pairs. It’s what people try first because it feels familiar. Here’s how to avoid the classic trap.
Open Excel. Go to Data → From Text/CSV. Navigate to your config.json file. In the file dialog, click the dropdown next to “Files of type” and choose All Files (*.*). Now select the JSON file and click Open.
You’ll land in the preview window. If your JSON starts with {"status":"active","region":"EMEA","quota":250000}, click Load. Excel treats it as a single-column CSV — but then splits on commas and colons. Not ideal.
Better approach: Before loading, click Transform Data. In Power Query Editor, go to Home → Advanced Editor. Replace the auto-generated code with:
let
Source = Json.FromBinary(File.Contents("C:\data\config.json")),
AsTable = Record.ToTable(Source)
in
AsTable
That’s right — even in the Text Import flow, you can drop into Power Query and switch to proper JSON parsing. It’s hidden, but it works. The shortcut? After opening the Text Import dialog, press Alt + A + T to jump straight to Transform Data.
Sample data used here:
| Key | Value |
|---|---|
| status | active |
| region | EMEA |
| quota | 250000 |
| currency | EUR |
| updated_at | 2024-05-10T09:22:14Z |
This outputs cleanly to A1:B5. No extra spaces. No quote stripping. Just two columns — exactly what you’d expect from config data.
Cheat Sheet
| Action | Shortcut / Path | Notes |
|---|---|---|
| Open Power Query JSON import | Data → Get Data → From File → From JSON | File must have .json extension |
| Force Text Import to treat .json as data | In From Text/CSV dialog → Files of type → All Files | Then use Advanced Editor to switch to Json.FromBinary() |
| Refresh imported JSON table | Right-click any cell in table → Refresh | Also works via Data → Refresh All (Alt + F5) |
| Promote first row to headers (if missing) | Power Query Editor → Transform → Use First Row as Headers | Or use keyboard: Alt + T + H |
| Detect and apply data types | Power Query Editor → Transform → Detect Data Type | Critical for numbers/dates to sort and calculate correctly |
