How to Import JSON into Excel with Power Query (and When Not To)

Excel Cannot Open JSON. It Can Import It.

Double-clicking a .json file will not give you a spreadsheet. Excel has no JSON viewer, and the format has no rows or columns for it to display.

What Excel does have is Power Query, a data import and transformation layer that understands JSON structure and can reshape it into a table. It is genuinely good at this. It is also more work than most people expect, because you have to unfold every nested level by hand.

This guide covers the import path end to end, and is honest about when it is the wrong choice.

Before You Start: Check Your Version

Power Query availability is not uniform, and this trips people up more often than any step in the process:

Menu wording also shifts between releases. If the exact path below does not match your ribbon, look for "Get Data", "Get & Transform", or "New Query" — they are the same feature under different names across versions.

Step 1: Load the File

On the Data tab: Get Data → From File → From JSON. Pick your file.

Excel does not put anything in the grid. It opens the Power Query Editor, which is a separate window, and shows you the top level of the document.

That is the first surprise for most people: nothing has been imported yet. Power Query is a staging area. Data reaches the worksheet only when you finish and choose Close & Load.

Step 2: Understand What You Are Looking At

Power Query displays JSON structure using its own vocabulary:

• Record — a JSON object, {...}
• List — a JSON array, [...]
• Anything else — a plain value

A typical API response opens as a single Record with fields like data, page, and total. Your rows live inside data, which shows as a List. Nothing is readable yet, and that is expected.

Two actions do the actual work, and you will use them repeatedly:

Convert To Table turns a List into rows. Use it on the list that holds your records.

The expand control — the small icon with two opposing arrows in a column header — turns a Record's fields, or a List's items, into real data.

Step 3: Get to Your Records

If the editor shows a Record with data, page, and total, click the data field to drill into it. You now have a List of records.

Click Convert To Table. Accept the defaults in the dialog. You get a single column, usually named Column1, where every cell reads Record.

That single opaque column is normal. Each cell is one of your JSON objects, still folded up.

Step 4: Expand, Repeatedly

Click the expand icon on Column1. Power Query lists the fields it found and lets you choose which to bring out.

Uncheck "Use original column name as prefix" unless you want every column named Column1.id, Column1.name, and so on. Leaving it checked once is a minor annoyance; leaving it checked through four levels of nesting produces column names nobody can read.

Click OK. The Record column is replaced by one column per field.

Now look at what came out. Any field that was itself an object shows as Record, and any field that was an array shows as List. Each of those needs its own expand step. A structure four levels deep needs four rounds of this.

Expanding a Record Column

Same control, same dialog: pick the fields, uncheck the prefix, click OK. Nested objects are straightforward — they always have the same field names, so they always produce the same columns.

Expanding a List Column

Lists give you a choice, and the choice matters:

Expand to New Rows duplicates the parent row once per item. A customer with three tags becomes three rows, identical except for the tag. This is correct when you want one row per item and plan to pivot afterwards. It is also how you get a child table out of an array of objects: expand to new rows, then expand the resulting Record column.

Extract Values keeps one row and joins the items into a single cell with a delimiter you pick. This is correct when the array holds a few labels and you want the sheet to stay one row per record.

There is no third option that puts an array into a cell as JSON text — if you want that, Power Query is not the tool.

Be careful with Expand to New Rows on multiple list columns in the same query. Expanding two independent arrays produces a cross product: three tags and four orders yields twelve rows, most of them meaningless. Expand one array per query, or split into separate queries and relate them afterwards.

Step 5: Fix the Types

Power Query guesses column types, and it guesses wrong often enough to check every time. The type indicator sits in each column header.

Two problems to look for specifically:

Numbers imported as text will not sum. Set the column type to Decimal Number or Whole Number.

Long IDs losing their tails. An identifier with 16 or more digits cannot be held exactly as a number. If Power Query types such a column as a number, the last digits become zeros — silently, with no error. Set the column type to Text before loading. This applies to order IDs, snowflake IDs, and account numbers, and it is the single most damaging default in the whole process.

Also check dates. JSON usually carries ISO 8601 strings, which Power Query normally recognizes, but Unix timestamps arrive as plain integers and need converting.

Step 6: Close & Load

Home → Close & Load puts the result in a worksheet as a proper Excel table.

The query is saved with the workbook. Data → Refresh All re-runs every step against the current file or URL. That is the whole reason to use Power Query: the transformation is repeatable, not a one-time paste.

Importing Straight from an API

Get Data → From Other Sources → From Web, then paste the endpoint URL. Everything after that is the same expand-and-type process.

This works well for public endpoints. It gets awkward fast when authentication is involved: custom headers and POST bodies require hand-writing M code in the advanced editor, and credentials stored in a workbook are a real security consideration if the file gets shared. For a one-off authenticated call, fetching the response with your own client and converting the saved file is usually less work and safer.

When Power Query Is the Wrong Tool

Power Query earns its setup cost through repetition. If the task is not repeating, the cost is pure overhead.

Use Power Query when:

• The source file or URL changes and you want to hit Refresh.
• The workbook is a recurring report other people open.
• You need to join the JSON against tables already in the workbook.
• The transformation itself needs to be auditable and repeatable.

Convert to .xlsx first when:

• You need the data once.
• The nesting is deep, and manual expanding means dozens of clicks.
• You want to see the resulting table before it lands in a workbook.
• You are on a platform where Power Query's JSON support is limited.

The honest comparison for a single nested file: a converter that detects the array root and flattens every level automatically finishes in about the time it takes to complete two expand dialogs in Power Query.

Faster path for a one-off file: our JSON to Excel converter detects the record array, flattens all nested objects in one pass, exports arrays of objects to their own worksheets, and downloads a ready .xlsx. It runs entirely in your browser, so the file is never uploaded — which also makes it usable on data you would not paste into a cloud service.

Common Power Query Problems

Every cell says "Record" or "List". Structure that has not been expanded yet. Use the expand control on that column.

Column names look like Column1.data.address.city. The prefix option was left checked during expand steps. Rename the columns, or redo the expand with the box unchecked.

Row count multiplied unexpectedly. Two list columns were both expanded to new rows, producing a cross product. Expand one array per query.

A column will not sum. It is typed as text. Change the column type.

IDs end in zeros. Numeric typing on an integer past 15–16 digits. Retype the column as Text and refresh.

Refresh fails after the file moved. The query stores an absolute path. Update the source in Query Settings, or use a stable location.

Some rows have blank columns. The JSON records are not uniform. That is your data, not a bug — Power Query builds the column set from the fields it encountered.

Which Method Should You Use?

Frequently Asked Questions

Can Excel open a JSON file directly?

Not as a spreadsheet. Double-clicking a .json file does not produce rows and columns, and opening it through File → Open usually gives you raw text or an import prompt. The supported path is Data → Get Data → From File → From JSON, which loads the file into Power Query rather than the grid. From there you convert the structure to a table and expand each nested level before loading it into a worksheet.

Where is Get Data > From JSON in Excel?

On the Data tab, under Get Data → From File → From JSON in recent Windows versions of Excel. Availability differs by version and platform: Power Query is built into Excel 2016 and later on Windows, was an add-in for Excel 2010 and 2013, and arrived later and with fewer connectors on Excel for Mac. If you cannot find the entry, check your version before assuming the file is the problem.

Why is my JSON showing as List or Record instead of data?

That is Power Query representing structure it has not expanded yet. Record means a JSON object, and List means a JSON array. Use Convert To Table on a top-level list, then use the expand control on each Record column to turn its fields into real columns. Every nesting level needs its own expand step, which is why deeply nested JSON takes many clicks.

How do I expand nested JSON in Power Query?

Click the expand icon in the column header. For a Record column, pick which fields to bring out as new columns. For a List column, you choose between Expand to New Rows, which duplicates the parent row once per item, and Extract Values, which joins the items into one cell with a delimiter you choose. Repeat for each level, and uncheck the option that prefixes column names if you do not want long names like data.address.city.

Should I use Power Query or convert the JSON to xlsx first?

Use Power Query when the import needs to refresh later from the same file or URL, when the workbook is a recurring report, or when you need to join the JSON against other tables already in the workbook. Convert to .xlsx first when you need the data once, when the nesting is deep enough that manual expanding becomes tedious, or when you want to check the resulting table before it lands in a workbook.

Can Power Query pull JSON straight from an API URL?

Yes, through Get Data → From Other Sources → From Web, entering the endpoint URL. Anything requiring authentication headers or a POST body is significantly harder and often needs a custom M query, so for one-off authenticated calls it is usually faster to fetch the response with your own client and convert the saved file.

Related Reading

• How to Convert JSON to Excel — the full decision set, including array handling and large-number precision.
• JSON Arrays to Excel — how flattening works in detail, and why arrays of objects belong in their own worksheet.
• JSON to CSV Conversion Guide — when a flat text format is the better target.

Summary

Excel imports JSON through Power Query, not by opening the file. The path is Get Data → From File → From JSON, then Convert To Table, then one expand step per nesting level, then a type check before Close & Load. Watch two things closely: uncheck the column-name prefix during expands, and force long ID columns to Text so their last digits survive.

Power Query is the right answer when the import will repeat. For a single nested file, converting to .xlsx first is faster and lets you inspect the table before it reaches your workbook.

Convert JSON to Excel in your browser → — automatic flattening, nested arrays to separate sheets, no upload.