To open a JSON file in Excel, select Data > Get Data > From File > From JSON, pick the file, and select Open. The Power Query Editor shows the contents as a table; select Home > Close & Load to place it on a worksheet.
Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021 and Excel 2019 for Windows, plus Excel for Microsoft 365 for Mac. Checked against Microsoft Support on October 7, 2026.
What a JSON file is and why Excel needs to import it
JSON (JavaScript Object Notation) is a plain-text format that apps, websites and online services use to export data. Instead of rows and columns, it stores data as named values inside curly braces (objects) and ordered groups inside square brackets (arrays). Objects can sit inside other objects, so one entry can hold several levels of detail.
A worksheet is a flat grid, so Excel has to convert that structure into rows and columns. That job belongs to Power Query, the import and clean-up tool behind the Get Data button. Here is a small example of what goes in:
[ {“id”: 101, “name”: “Ana”, “address”: {“city”: “Austin”, “state”: “TX”}}, {“id”: 102, “name”: “Ben”, “address”: {“city”: “Boise”, “state”: “ID”}} ]
After the import, each person becomes one row, and the values become columns such as id, name, city and state.
Before you start
- Check your Excel version. Microsoft’s list of Power Query data sources shows JSON for Excel 2019 and later and for Microsoft 365 on Windows. It is not listed for Excel 2016. If you can’t find Get Data at all, see how to find and enable Power Query in Excel.
- Use a computer. Microsoft states that Power Query is not supported in Excel for Android or iOS.
- Save the file locally. Put the .json file in a folder you can browse to, such as Documents or Downloads, and keep the original. Excel reads the file and does not change it.
- Know the size limit. A worksheet holds up to 1,048,576 rows and 16,384 columns. A larger file cannot be loaded in full to a single sheet.
Open a JSON file in Excel for Windows
- Open Excel and create a blank workbook, or open the workbook that should receive the data.
- Select the Data tab on the ribbon.
- Select Get Data > From File > From JSON. The Import Data dialog box appears.
- Locate the JSON file, select it, and then select Open. The Power Query Editor opens in its own window. Microsoft’s connector documentation says Power Query uses automatic table detection here, which flattens the JSON data into a table for you.
- Look at the preview. If every column shows ordinary values, skip to step 7. If a column shows the word Record, List or Table in each cell, that column still holds nested data.
- Select the expand icon in the header of the nested column, select the fields you want, clear the ones you don’t, and select OK. New columns appear for each field you kept. Repeat for any other nested column.
- Select Home > Close & Load. The editor closes and the data appears on a new worksheet as an Excel table.
Save the workbook as a normal Excel file when you are done. The table stays connected to the JSON file through a query, which is what makes refreshing possible later.
Expand nested records, lists and tables
Nested data is the part that confuses most people, because the preview shows a placeholder word instead of a value. Microsoft calls these structured columns, and each type expands a little differently.
| What the cell shows | What it means | What the expand icon offers |
|---|---|---|
| Record | One object with several named fields, like the address in the example above | A list of fields. Each field you keep becomes a new column. |
| List | An array of values, such as several phone numbers for one person | Expand to New Rows creates a row for each value. Extract Values keeps one row and joins the values as text separated by a delimiter. |
| Table | A related set of rows and columns | Expand adds the related columns and rows. Aggregate summarizes them with functions such as Sum and Count. |
Choosing between the two List options changes the shape of your result. Expand to New Rows repeats the other columns for every value, so a customer with three orders becomes three rows. That is right for counting or filtering orders, but it will inflate totals of customer-level numbers. Extract Values keeps one row per customer, which is easier to read but harder to analyze.
Deep files may need several rounds: expanding one column can reveal another Record or List inside it. Keep expanding until the columns you need show real values, and leave out fields you don’t need so the table stays manageable.
Choose where the data goes
Close & Load always puts the result on a new worksheet. For other destinations, select Close & Load To instead. The Import Data dialog box then offers Table, PivotTable Report, PivotChart and Only Create Connection, along with an Add this data to the Data Model check box.
Only Create Connection saves the query without putting rows on a sheet. It is useful when you want to keep the import steps but not display everything.
To change the destination later, select Data > Queries & Connections, right-click the query on the Queries tab, and select Load To.
Open a JSON file in Excel for Mac
Microsoft lists JSON among the sources that Excel for Microsoft 365 for Mac can import with Power Query.
- Select Data > Get Data, and then select Get Data (Power Query).
- In the Choose data source dialog box, select JSON.
- Browse to the file and follow the prompts to bring it into the workbook.
- To expand nested columns or make other changes, select Data > Get Data (Power Query), and then select Launch Power Query Editor. Microsoft says to shape the data there as you would in Excel for Windows.
- Select Home > Close & Load.
Other ways to bring JSON into Excel
JSON from a web address
If the data lives at a URL rather than in a file, select Data > Get Data > From Other Sources > From Web. In the From Web dialog box, enter the URL and select OK. Follow any credential prompts if the address requires a sign-in.
Several JSON files at once
For a folder of files with the same layout, Microsoft recommends a multi-file connector rather than importing each file separately. Select Data > Get Data > From File > From Folder, locate the folder in the Browse dialog box, and select Open. You can then combine the files into one table.
JSON text stored inside a column
Some exports are ordinary spreadsheets or CSV files where one column contains JSON text. Load the file into Power Query first (see how to import a CSV file into Excel), select that column, and then select Transform > Parse > JSON. The column turns into Record values that you can expand with the same expand icon. Microsoft notes that in some versions the command sits under Text Column rather than Parse.
Check the result, refresh it or change it
After loading, compare a few rows with the original file. Confirm that the row count looks right, that ID numbers and dates display as expected, and that no column still shows Record or List. If codes or fractions arrived as dates, see how to stop Excel from changing numbers to dates.
- Refresh: if the JSON file is replaced with a newer version at the same location, select Data > Refresh All (Ctrl+Alt+F5) to rerun every query in the workbook. To refresh only the table you have selected, select the arrow next to Refresh All and then Refresh (Alt+F5).
- Refresh automatically: select Data > Queries & Connections, open the Connections tab, right-click the query and select Properties. On the Usage tab, select Refresh data when opening the file, or select Refresh every and enter a number of minutes.
- Edit the import: select any cell in the loaded table and select Query > Edit. You can also select Data > Queries & Connections, right-click the query and select Edit. The Power Query Editor reopens with your earlier steps intact.
Nothing you do in Excel alters the source JSON file, so the import is safe to repeat. If the result is wrong, delete the worksheet and the query and start again.
Troubleshooting
From JSON is missing from the Get Data menu
Microsoft’s data source table lists JSON for Excel 2019 and later and for Microsoft 365 on Windows, but not for Excel 2016. If the command is absent, check which version of Excel you have. On a work or school computer, your organization controls which version is installed, so ask your IT team about an update.
“Unable to connect” appears after you choose the file
Microsoft’s JSON connector documentation gives four causes: the file is invalid, it is not really a JSON file, it is malformed, or it is a JSON Lines file. JSON Lines is a variation in which every line is its own separate JSON object, and the standard import does not read it. Microsoft publishes a short workaround query for JSON Lines on its Power Query JSON connector page. For an ordinary file, open it in a text editor and check that it starts with a brace or bracket and was not cut off during download.
Columns show Record, List or Table
That is nested data waiting to be expanded, not an error. Reopen the query with Query > Edit and use the expand icon in the column header as described above.
Long ID numbers lose digits or dates look wrong
Excel keeps numbers to a precision of 15 digits, so longer account numbers or tracking IDs stored as numbers are not preserved exactly. In the Power Query Editor, select the column, select Home > Data Type, and choose Text before you load. The same menu lets you set a column to Date, Date/Time, Whole Number or Decimal number. For dates written in another country’s format, choose Using locale at the bottom of the type list. Microsoft documents these data type options for Excel for Microsoft 365.
The file has too many rows
If the expanded data exceeds 1,048,576 rows, it will not fit on one worksheet. Use Close & Load To and choose Only Create Connection with Add this data to the Data Model, then analyze it with a PivotTable. You can also filter rows or remove columns in the editor before loading.
Frequently asked questions
Can I just double-click a JSON file to open it in Excel?
The dependable route is the From JSON import. JSON is structured text rather than a spreadsheet format, so it needs Power Query to turn it into rows and columns.
Does importing change my original JSON file?
No. Excel reads the file and stores the result in the workbook. The query only remembers where the file is so it can read it again when you refresh.
Does this work in Excel 2016?
Microsoft’s table of Power Query data sources does not list JSON for Excel 2016. It is listed for Excel 2019 and later and for Microsoft 365.
Can I open a JSON file in Excel on a phone or tablet?
Not with this method. Microsoft states that Power Query is not supported in Excel for Android or iOS, so do the import on a Windows PC or a Mac.
Is it safe to use an online JSON converter instead?
A converter website receives a copy of whatever you upload. For customer lists, financial exports or anything from work, the built-in import is the safer choice because the file stays on your computer.
Why did one record turn into several rows?
You expanded a List with Expand to New Rows, which creates a row for each value in the list. If you would rather keep one row per record, edit the query and use Extract Values for that column instead.
Once your JSON data is in a table, set up a refresh option so new exports flow in without repeating the import. Microsoft’s full list of import sources and steps is on its Import data from data sources (Power Query) page.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.