Why does Excel show a blank sheet when you double-click a .json file? Why does Power Query say 'Unable to parse' even after clicking 'From File'? Why does the same JSON work in Google Sheets but not Excel?
The answer is simple: Excel doesn’t read JSON — it imports and transforms it. And that transformation fails silently unless you control the structure first.
The Setup
You’re handed a JSON export from a CRM — 9 records of sales leads. No schema documentation. Just raw text:
| id | name | company | value_usd | created_at | status |
|---|---|---|---|---|---|
| 1042 | Sarah Chen | Acme Corp | $45,200 | 2024-03-15 | Qualified |
| 1043 | Diego Mora | Nexus Labs | $78,900 | 2024-03-16 | Proposal Sent |
| 1044 | Priya Kapoor | Veridian Group | $32,500 | 2024-03-16 | Contacted |
| 1045 | Marcus Bell | Stratos Inc | $112,400 | 2024-03-17 | Qualified |
| 1046 | Aisha Rahman | Lumen Dynamics | $64,800 | 2024-03-18 | Proposal Sent |
| 1047 | Takeshi Sato | Kyoto Solutions | $29,100 | 2024-03-18 | Contacted |
| 1048 | Elena Petrova | Baltic Tech | $51,600 | 2024-03-19 | Qualified |
| 1049 | Jamal Wright | Summit Analytics | $87,300 | 2024-03-20 | Proposal Sent |
| 1050 | Fatima Al-Mansoori | Qatar DataHub | $93,500 | 2024-03-21 | Qualified |
The Challenge
You need this JSON in Excel — not as text, but as real columns with formulas, sorting, pivot-ready data.
But here’s what breaks most people: they try to open the JSON file directly (File > Open), or paste raw JSON into A1, or use 'Get Data > From Text/CSV' and expect Excel to auto-detect structure. It won’t.
JSON arrays must be wrapped in square brackets [...]. Objects inside must be comma-separated. If your file starts with {"data":[{...}]}, Excel sees one root object — not an array — and imports just the top-level key names. You’ll get one row. Nine records vanish.
Also: dates like "2024-03-15" come in as text. Currency strings like "$45,200" won’t convert to numbers unless you clean them first.
Walking Through It
Do this — no deviations.
Step 1: Save your JSON file somewhere accessible. Don’t rename it. Don’t open it in Notepad++ first. Just know its location.
Step 2: In Excel, go to Data > Get Data > From File > From JSON. Or use the keyboard shortcut: Alt → A → T → J.
That opens the JSON import dialog. Navigate to your file and click Import.
Step 3 (critical): In Power Query Editor, you’ll see a single cell labeled Value containing a record. Click the expand icon (two arrows) next to it. Select all columns. Click OK.
If you see List instead of Record, your JSON is correctly structured as an array. Click the list, then Convert to Table (Ctrl+T), then expand.
Before expansion:
| Value |
|---|
| [Record] |
After expansion (with proper selection):
| id | name | company | value_usd | created_at | status |
|---|---|---|---|---|---|
| 1042 | Sarah Chen | Acme Corp | $45,200 | 2024-03-15 | Qualified |
| 1043 | Diego Mora | Nexus Labs | $78,900 | 2024-03-16 | Proposal Sent |
Step 4: Clean the currency column. Select column value_usd (C2:C10). Right-click → Transform > Replace Values. Find: $, Replace with: blank. Then do the same for commas. Finally, right-click → Change Type > Decimal Number.
Step 5: Fix dates. Select created_at (E2:E10). Right-click → Change Type > Date. Excel will auto-parse ISO format.
The Result
This is what lands in your worksheet — ready for filtering, charts, SUMIFS, or VLOOKUP:
| id | name | company | value_usd | created_at | status |
|---|---|---|---|---|---|
| 1042 | Sarah Chen | Acme Corp | 45200 | 2024-03-15 | Qualified |
| 1043 | Diego Mora | Nexus Labs | 78900 | 2024-03-16 | Proposal Sent |
| 1044 | Priya Kapoor | Veridian Group | 32500 | 2024-03-16 | Contacted |
| 1045 | Marcus Bell | Stratos Inc | 112400 | 2024-03-17 | Qualified |
| 1046 | Aisha Rahman | Lumen Dynamics | 64800 | 2024-03-18 | Proposal Sent |
| 1047 | Takeshi Sato | Kyoto Solutions | 29100 | 2024-03-18 | Contacted |
| 1048 | Elena Petrova | Baltic Tech | 51600 | 2024-03-19 | Qualified |
| 1049 | Jamal Wright | Summit Analytics | 87300 | 2024-03-20 | Proposal Sent |
| 1050 | Fatima Al-Mansoori | Qatar DataHub | 93500 | 2024-03-21 | Qualified |
What Could Go Wrong
Mistake #1: Opening JSON via File > Open
Excel treats it as plain text. You get one cell with thousands of characters. No structure. No recovery without manual reformatting.
Mistake #2: Skipping the 'Expand' step in Power Query
You’ll get a table with one column named Value, full of [Record] labels. That’s not data — it’s metadata about where data *could be*.
Mistake #3: Using 'From Text/CSV' instead of 'From JSON'
Excel tries to split on commas or tabs. JSON has nested commas inside strings and objects. You’ll get misaligned columns, broken names ("Sarah Chen" splits into two cells), and corrupted dates.
One surprising tip: If your JSON contains nested objects (e.g., "address":{"city":"Shanghai","country":"CN"}), don’t expand everything at once. Expand address separately — then expand city and country from that new column. Otherwise you get duplicate prefixes like address.city and address.country mashed into one header.
Now you know: Excel *can* read JSON files — but only if you treat them like a foreign language that needs translation, not a native document.
Next step: Try this with your next JSON file. Then run this check:
| Check | Pass? | How to Verify |
|---|---|---|
JSON starts with [{ (array) | ✓ | Open in Notepad. First 3 chars = [{ |
| All numeric fields are clean (no $, commas) | ✓ | Select column → Home > Number Format = Number |
| Dates sort chronologically (not alphabetically) | ✓ | Sort column A-Z. 2024-03-15 must come before 2024-03-16 |
No [Record] or [List] in final sheet | ✓ | Scan column A. Values should be numbers or names — never bracketed labels |
