How to Get JSON into Excel: 3 Reliable Ways

Last updated on

Excel can’t open a .json file and make sense of it — you get one cell of raw text. Here are three reliable ways to get JSON into a proper Excel grid, from quickest to most powerful.

Option 1: JSON → CSV → Excel (fastest)

For a flat array of objects — the shape most API responses take — convert to CSV, then open in Excel.

[
  {"id": 1, "name": "Ada", "role": "admin"},
  {"id": 2, "name": "Alan", "role": "editor"}
]

becomes

id,name,role
1,Ada,admin
2,Alan,editor

Save as .csv, double-click, done. The JSON to CSV converter does the conversion in your browser, or JSON to Excel hands you the sheet directly.

Option 2: Power Query (for nested JSON)

If your JSON has nested objects or arrays, Excel’s built-in Power Query handles it:

  1. Data → Get Data → From File → From JSON
  2. Pick your file; the Power Query editor opens.
  3. Click To Table, then use the expand button on any column to flatten nested fields.
  4. Close & Load.

This keeps a live connection — refresh re-imports the file. Great for recurring reports, overkill for a one-off.

Option 3: A script (for automation)

If it’s a pipeline, do it in code:

import pandas as pd
pd.read_json("data.json").to_excel("data.xlsx", index=False)

pandas flattens a flat array cleanly; use pd.json_normalize() for nested structures.

Which to use

  • One-off, flat data → JSON → CSV, open in Excel. Seconds.
  • Nested data, recurring → Power Query.
  • Automated pipeline → pandas.

The trap with all three: Excel loves to “helpfully” reformat values — long numbers become scientific notation, leading zeros vanish, and date-like strings become dates. Import ID and code columns as Text to keep them intact.

Related articles