How to Open Nested JSON in Excel Without Losing Data
If you have ever double-clicked a .json file expecting Microsoft Excel to display neat rows and columns, you already know what happens: Windows opens a code editor, or Excel launches the Power Query editor showing opaque [Record] and [List] placeholders instead of values. Figuring out how to open nested JSON in Excel without losing child fields, stripping leading zeros, or manually clicking twenty expand arrows is a daily bottleneck for developers, QA analysts, and finance teams.
Below, we compare the two reliable ways to turn deeply nested JSON responses into clean Excel workbooks—an instant browser-local dot-flattening workflow and Excel's built-in Get & Transform (Power Query) wizard—plus how to handle arrays of line items without corrupting row totals.
Why Nested JSON Breaks Naive Spreadsheet Imports
A spreadsheet worksheet is a strict two-dimensional matrix (rows × columns), whereas REST API payloads, webhook logs, and MongoDB exports are hierarchical trees. Real-world JSON payloads typically introduce three structural hurdles:
- Envelope Wrapper Objects: Production APIs rarely return a bare array at the root. Instead, records sit inside
{ "status": "ok", "pagination": { "page": 1 }, "data": [...] }. - Multi-Level Child Objects: Customer addresses, billing metadata, or hardware specs are nested two or three levels deep (
customer.billing.city). Naive scripts stringify child objects as[object Object]. - Sparse & Heterogeneous Schemas: Record
#1might omit optional fields likediscount_codeortax_idthat appear only on record#47. Any converter that inspects only the first object silently drops columns from later rows.
{
"meta": { "region": "us-east" },
"data": [
{
"invoice_id": "00841",
"customer": { "name": "Apex Labs", "address": { "city": "Boston", "zip": "02110" } },
"items": [
{ "sku": "PRO-10", "qty": 2 },
{ "sku": "SUP-01", "qty": 1 }
],
"total_usd": 640.00
}
]
}
Method 1: Instant Client-Side Dot-Path Flattening (Fastest)
When you want an .xls or .xlsx file ready to open in one click—especially when working with confidential API data that must stay on your machine—flattening JSON into a native workbook before opening Excel saves minutes of repetitive UI clicks.
Using our client-side JSON to XLS workbench, the entire transformation runs in local browser memory (zero server uploads, as detailed in our privacy policy):
- Paste or Drop Your JSON File: Drag your
.jsonexport into the left pane. If your records live inside.dataor.results, the Record list source dropdown isolates that array automatically. - Keep Dot-Path Flattening Enabled: Nested keys like
customer.address.cityandcustomer.address.zipbecome explicit column headers, scanning all records so sparse keys on later rows are never lost. - Choose How Nested Arrays Behave: This is the critical modeling decision whenever you decide how to open nested JSON in Excel:
- Join with semicolon (
;): Best when each JSON object represents one summary entity (one row per invoice) andtagsorrolesshould stay inside a single cell. - Expand into separate rows: Best for
orders[].items[]line-item analysis, where each array element becomes its own Excel row while parent columns (invoice_id,customer.name) repeat cleanly for PivotTables.
- Join with semicolon (
- Filter Columns & Export Native Excel: Uncheck internal debug keys (
__v,trace_id), then click Download .XLSX (for modern Excel up to 1,048,576 rows) or Download .XLS (BIFF8) for legacy ERP systems. Because strings like"00841"and"02110"are written as explicit Excel text cells, Excel preserves every leading zero automatically.
If your downstream tool requires plain-text delimited files rather than binary workbooks, you can also run the same dot-flattening logic in our JSON to CSV converter.
Method 2: Opening Nested JSON Inside Excel Power Query
If you need a refreshable connection pointed at a local file path on Windows or macOS (Excel 2016+ / Microsoft 365), Power Query can parse nested JSON directly—though deeply nested schemas require manual expansion at each level:
- Open a blank workbook and navigate to Data → Get Data → From File → From JSON.
- In the Power Query Editor, click into the wrapper list link (for example, click
Listnext todata). - In the top ribbon, click To Table and accept the default settings.
- Click the two-way Expand icon (
↔) in theColumn1header, uncheck Use original column name as prefix if desired, and click OK. - Repeat the expand step for every nested
[Record]column (customer, thenaddress). For[List]columns, choose Expand to New Rows (explodes rows) or Extract Values (joins primitive strings with a delimiter). - Explicitly set column data types (Text for IDs and ZIP codes so Power Query's automatic type detection does not strip leading zeros), then click Close & Load.
Which Workflow Should You Use?
| Requirement | Client-Side JSON to XLS Workbench | Excel Power Query (Get Data) |
|---|---|---|
| Setup Time for 4-Level Nesting | Automatic in < 1 second | Manual expand per nested level |
| Leading-Zero ID Preservation | Preserved from JSON string type | Often coerced to Whole Number unless overridden |
Classic .xls (BIFF8) Output |
Direct binary BIFF8 export | Requires manual Save As 97-2003 |
| Live File Refresh Connection | One-off clean workbook export | Refreshable local file query |
Whenever colleagues send structured schedules inside Word documents alongside API exports, you can also pair your flattened JSON sheets with tables extracted via our Word to Excel converter.