How to Flatten Nested JSON in Excel
An API export drops a column of nested JSON — objects inside objects, arrays of tags — that no formula can read. Here's how to flatten every level into plain columns you can filter and pivot.
Last updated: August 2026
| JSON cell | Flat columns | |
|---|---|---|
| {"user":{"name":"Ana"},"tags":["x","y"]} | → | user.name = Ana · tags = x; y |
Method 1 — Power Query
- Data > Get Data, load the table, and set the JSON column's type to record/list.
- Click the Expand (⇔) button on the column, and repeat for every nested record and list until nothing is left to expand.
- Rename the generated columns and close & load.
It works, but deep structures mean many rounds of Expand, arrays behave differently from records, and the query breaks when the shape shifts between exports.
The 1-click way — Tellsheet
Flatten Nested JSON flattens a whole column at once into dot-keyed columns (user.name, user.roles) on a new sheet. Nested objects become dotted keys; arrays of simple values collapse to one a; b; c cell (or switch to indexed keys), and arrays of objects split into items.0.name, items.1.name. Keys are discovered across all rows, and your other columns come along. Pairs with JSON to Columns and Split Text.
Frequently asked questions
How do I flatten nested JSON?
Power Query's repeated Expand, or Tellsheet's Flatten Nested JSON in one step.
How is it different from JSON to Columns?
JSON to Columns expands only the top level; this goes all the way down with dot keys.
How are arrays handled?
Simple arrays join to one cell by default; arrays of objects split into indexed keys.
Does it keep my other columns?
Yes — the non-JSON columns are carried onto the new sheet alongside the flattened keys.
Related Excel guides
Turn nested JSON into columns
Flatten Nested JSON expands every level into dot-keyed columns — right inside Excel.
Get Tellsheet free See pricing