How JSON maps to rows and columns
[
{ "id": 1, "name": "Alice", "address": { "city": "London" }, "tags": ["vip", "newsletter"] },
{ "id": 2, "name": "Bob", "address": { "city": "Austin" }, "tags": [] }
]becomes this sheet:
| id | name | address.city | tags |
|---|---|---|---|
| 1 | Alice | London | vip, newsletter |
| 2 | Bob | Austin |
- Columns appear in the order keys are first seen; a key missing from some objects leaves those cells empty.
- Nested objects flatten to dotted names at any depth (
user.address.city). - Arrays of objects are kept as JSON text in a single cell so nothing is silently dropped.
JSON to Excel vs JSON to CSV
CSV is plain text: every value becomes a string, leading zeros can be lost when Excel guesses types, and non-English characters sometimes open garbled. An .xlsx file stores real numbers, booleans and Unicode text, so it opens correctly in Excel with no import wizard. If you need CSV for a database import or a script, use the JSON to CSV converter instead.
Convert JSON to Excel in Python or Excel itself
# Python — pandas flattens nested keys the same way
import json, pandas as pd
data = json.load(open("data.json"))
pd.json_normalize(data).to_excel("data.xlsx", index=False)In Excel 2016 and later: Data → Get Data → From File → From JSON, then expand the records in Power Query. That works well for recurring imports; for a one-off file, pasting it here is faster.