What Most People Miss About Excel Reading JSON

Yes, Excel can read JSON — but only if you treat it like a foreign language with no built-in translator.

Quick Answer

Excel doesn’t natively parse JSON in formulas or cell entries; it relies entirely on external engines like Power Query (Get & Transform) or third-party tools. You’ll never type =JSON.PARSE(A1) and get results — that function doesn’t exist, and won’t for years (trust me, I checked with Microsoft support in 2023).

All the Methods

Method	Steps	Best For	Limitations
Power Query (native)	Data → Get Data → From File → From JSON → Select file → Expand arrows	Structured arrays, nested objects, repeated records (e.g., API responses)	Fails silently on malformed JSON; no error line numbers; requires refresh to update
VBA + ScriptControl (legacy)	Insert module → Paste JSON parser → Call ParseJSON() → Output to Range	One-off parsing inside macros, offline environments	ScriptControl removed from Windows 10/11 by default; security warnings; 32-bit only
IMPORTJSON custom add-in	Install add-in → =IMPORTJSON("https://api.example.com/data") → Auto-expand	Live web APIs, dashboards needing auto-refresh	Not Microsoft-signed; blocked by corporate GPOs; fails on CORS or auth headers
REST API + Power Query Web.Contents	Advanced Editor → Use Web.Contents with headers → Json.FromBinary → Expand	Authenticated endpoints (OAuth, Bearer tokens), paginated JSON	No GUI — pure M code; credentials stored in query (not secure unless encrypted)

Method 1 Deep Dive

Let’s walk through Power Query — the only method Microsoft fully supports. Say you’ve got a JSON file named sales_report_2024.json saved locally. It looks like this:

{
  "meta": {"generated": "2024-03-15T08:22:11Z", "version": 2},
  "data": [
    {"id": 101, "name": "Sarah Chen", "region": "APAC", "revenue": 45200},
    {"id": 102, "name": "Diego Morales", "region": "EMEA", "revenue": 62100},
    {"id": 103, "name": "Priya Nair", "region": "APAC", "revenue": 38900}
  ]
}

Open Excel. Go to the Data tab → Get Data → From File → From JSON. Browse to your file. Click Import. You’ll land in Power Query Editor. Here’s what most people miss: the meta object sits *beside* data, not inside it. So when you click the expand icon (➡️) next to data, you get three rows — perfect. But if you expand meta, you’ll get two columns (generated, version) repeating across all three rows. That’s fine — just right-click meta → Remove Columns before closing and loading. Now — the counterintuitive part: if your JSON has deeply nested fields like "customer": {"address": {"city": "Shanghai", "zip": "200001"}}, don’t try to drill down manually. Instead, select the entire customer column → right-click → Expand to New Rows → then expand address → then expand city. Why? Because doing it all at once collapses duplicates and loses context. I learned this the hard way debugging a client’s order export where 40% of addresses vanished. Your final table lands in Sheet1 starting at A1. Column headers appear as data.id, data.name, etc. To clean those up: select row 1 → Ctrl+H → find "data." → replace with blank → OK. Done.

Method 2 Deep Dive

VBA is risky but sometimes unavoidable — especially when your IT department blocks Power Query or external connections. Here’s a working snippet (tested in Excel 365, 64-bit):

Sub ParseLocalJSON()
    Dim jsonText As String
    Dim fso As Object, file As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file = fso.OpenTextFile("C:\temp\inventory.json", 1)
    jsonText = file.ReadAll
    file.Close
    
    ' Requires reference to Microsoft Script Control 1.0 (scrrun.dll)
    Dim sc As Object
    Set sc = CreateObject("MSScriptControl.ScriptControl")
    sc.Language = "JScript"
    sc.AddCode "function parse(j){return JSON.parse(j);}" 
    
    Dim parsed As Object
    Set parsed = sc.Run("parse", jsonText)
    
    ' Output first 5 items to Sheet2, A1:E5
    Dim i As Long
    For i = 0 To Application.Min(4, parsed.data.length - 1)
        Sheet2.Cells(i + 1, 1) = parsed.data(i).sku
        Sheet2.Cells(i + 1, 2) = parsed.data(i).desc
        Sheet2.Cells(i + 1, 3) = parsed.data(i).qty
        Sheet2.Cells(i + 1, 4) = parsed.data(i).warehouse
        Sheet2.Cells(i + 1, 5) = parsed.data(i).last_updated
    Next i
End Sub

⚠️ Warning: ScriptControl doesn’t install by default on Windows 10/11. You’ll get “ActiveX component can’t create object” unless you run this PowerShell command first (as Admin): regsvr32 scrobj.dll Even then, 64-bit Excel won’t load it — so you must use 32-bit Office. Yes, really. That’s why we avoid VBA unless the firewall says “no” to everything else. Sample inventory.json contents:

{"data":[
  {"sku":"INV-8812","desc":"Wireless Headset Pro","qty":142,"warehouse":"SZX1","last_updated":"2024-03-14"},
  {"sku":"INV-8813","desc":"USB-C Dock v3","qty":87,"warehouse":"SZX1","last_updated":"2024-03-14"},
  {"sku":"INV-8814","desc":"Ergo Keyboard","qty":211,"warehouse":"SH12","last_updated":"2024-03-13"}
]}

This drops cleanly into Sheet2!A1:E3. No refresh button. No ribbon clutter. Just raw control — and zero portability.

Cheat Sheet

Task	Shortcut / Action	Notes
Open Power Query JSON import	Alt+A → T → J	Alt+A = Data tab, T = Get Data, J = From JSON
Expand nested column	Click ➡️ → check boxes → OK	Hold Ctrl to select non-adjacent fields
Refresh all queries	Alt+F5	Also works in Power Query Editor
Delete unwanted column in PQ	Right-click column header → Remove Columns	Don’t use Delete key — it clears values only
Convert JSON date string to Excel date	=DATEVALUE(LEFT(A2,10))	Assumes ISO format like "2024-03-15T08:22:11Z"
Test JSON validity before import	Paste into jsonlint.com	Fix trailing commas, single quotes, or unescaped quotes first