JSON to Excel: A Step-by-Step Guide

Excel cannot open a .json file as a table directly. Power Query can, but it takes several dialogs, and the result depends on the shape of your data. This guide covers three ways to get JSON into Excel, from quickest to most flexible, and when each one is the right choice.

Option 1: Convert in the browser (fastest)

For a one-off conversion this is the shortest path.

1. Open JSON to Table and paste your JSON, or drop a .json file onto the input.
2. Confirm the rows. If your JSON has an envelope like {"data": [...]}, check that Table rows from points at the array you want.
3. Tidy the view: hide columns, sort, filter.
4. Click Excel.

The download is a real .xlsx workbook, not a renamed CSV. The header row is bold and frozen so it stays visible as you scroll, auto-filter is switched on, and numbers are stored as numbers so SUM and pivot tables work immediately. Because the file is built in your browser, nothing is uploaded.

What to do with nested arrays

Excel sheets are flat, so arrays inside records need a strategy. When you export, you can choose how they are written:

• Keep as JSON text. The array stays in one cell as text. Good when you rarely need the inner detail.
• Repeat the parent row. One row per array item, with the parent’s values repeated. Good for pivot tables, for example one row per order line.
• One sheet per array. Each array becomes its own sheet, with an ID column linking back to the parent. Good for relational data you will join later.

The reasoning behind each is in handling arrays when converting JSON to a table.

Option 2: Power Query inside Excel

If you need to refresh the workbook when the source file changes, Power Query is the better tool.

1. In Excel, choose Data > Get Data > From File > From JSON.
2. Select your file. The Power Query editor opens with a list or record.
3. For a list, click To Table, then use the expand icon on the column header to turn records into columns.
4. Repeat the expand step for each nested record. Nested lists need Expand to New Rows.
5. Click Close & Load.

This is powerful but manual: you expand every level by hand, and column types are guessed from the first rows. It is worth it for a recurring report. It is overkill for a quick look at an API response.

Option 3: Convert to CSV first

If you already have a CSV workflow, convert there and open the result. The trade-offs are described in how to convert Postman JSON to CSV. CSV is simple and universal but carries no types or formatting, so Excel guesses, and its guesses are the source of most spreadsheet data problems.

Why .xlsx beats CSV for Excel

The single biggest reason is accuracy. Double-clicking a CSV makes Excel decide each column’s type. It turns 00123 into 123, 1-2 into a date, and a 19-digit ID into a rounded number with trailing zeros. An .xlsx file declares its types, so none of that happens.

Excel’s limits

• Rows: a worksheet holds 1,048,576 rows including the header, so 1,048,575 data rows. If your JSON has more, split it or use the one-sheet-per-array option.
• Columns: 16,384. Deeply nested JSON can generate many dotted columns, so hide those you do not need before exporting.
• Cell length: 32,767 characters. A very long string, or a big nested array kept as JSON text, is cut off at that point.
• Number precision: 15 significant digits. IDs longer than that should be stored as text.

Troubleshooting

Columns appear in a strange order. Columns follow the order keys first appear in the data. If the first record is missing a field that later records have, that column appears later. You can reorder or hide columns before exporting.

Some cells are empty. JSON records need not share the same keys. A missing key becomes an empty cell, which is correct; it does not mean data was lost.

The file will not open. Almost always the source JSON was truncated or invalid. Run it through the JSON Formatter & Validator first; the error message gives the exact line and column. Our list of common JSON errors covers the usual causes.

A quick checklist

1. Validate the JSON.
2. Select the correct array as the table’s rows.
3. Decide how to treat nested arrays.
4. Hide columns you do not need.
5. Export to .xlsx and spot-check the first and last rows against the source.

That last step takes ten seconds and catches nearly every mistake.