Open a JSON File in Excel and Convert It to XLSX

How-to basics · Updated November 20, 2026

To open a JSON file as spreadsheet data in Excel for Microsoft 365 on Windows, go to Data > Get Data > From File > From JSON, select the file, shape the data in Power Query, then choose Home > Close & Load. Review the resulting table and save the workbook as an .xlsx file. This imports the JSON’s data; it does not simply rename the file or make the original JSON itself an Excel workbook.

What You’ll Need

  • Excel for Microsoft 365 desktop on Windows, with the Power Query JSON connector available.
  • A JSON file saved on your computer or somewhere you can browse to, such as a local folder.
  • Permission to access the file. If it is large or contains sensitive information, consider where the workbook will be saved and who can access it.

You do not need to install a separate converter for the Windows steps below. JSON files store structured data, often as objects and arrays, rather than as rows and columns. Power Query reads that structure and lets you choose which fields to turn into worksheet columns. If Excel is installed on a Mac, the available Power Query connectors and menu options can vary by Microsoft 365 build. Check whether Data > Get Data includes a From JSON option; if not, use Excel for Windows to create the import.

Step-by-Step

  1. Check the file and make a working copy if needed. Find the file ending in .json and note its folder and name. If someone supplied it, keep the original unchanged and work from a copy. A JSON file is usually plain text, but opening and editing it in a text editor can introduce accidental changes. If you want to inspect it first, use a text editor that displays plain text and check that it contains recognizable braces, brackets, quoted field names, and values. Do not change the extension to .xlsx: changing a filename does not convert the contents.
  2. Start the JSON import in Excel. Open a blank workbook or the workbook where you want the imported table. On the ribbon, select Data > Get Data > From File > From JSON. Browse to the file, select it, and choose Import. If you do not see From JSON directly, check under the From File menu rather than choosing From Text/CSV. Menu layouts can differ slightly as Microsoft updates Excel, but the Windows Power Query route is the JSON connector under Get Data.
  3. Review the Power Query preview. Excel opens the data in Power Query Editor, or displays an initial view from which you can open the editor. A simple JSON file may appear as a table right away. Other files begin as a List or Record because JSON can contain nested structures. A list is an ordered collection, commonly used for a set of repeated items. A record is a group of named fields, commonly used for one item. The preview is not necessarily the final worksheet layout; inspect the values and structure before loading.
  4. Turn a top-level list into a table when required. If the preview says List, select To Table in the Power Query Editor. In the dialog, the default options are usually appropriate for a first import; select OK to create a table with one column of list values. If the list contains records, use the expand button in the column header to select the fields you want as columns. If the file instead starts with a single Record, select or drill into the relevant record or use the available conversion options to expose its fields. The exact view depends on how the JSON was organized.
  5. Expand the fields you want to use. For a column containing Record values, select the double-arrow expand icon in that column’s header and tick the fields to include. Clear any option to prefix column names if you prefer shorter headings; keeping prefixes can help distinguish fields that have the same name at different levels. For a column containing List values, expand or convert it as appropriate. You may need to repeat these actions for nested records or lists. Check the preview after each expansion: a nested list can produce multiple rows for one original item, while expanding a record generally exposes its fields as columns.
  6. Keep, rename, and check columns. Remove fields you do not need with the column menu, and rename useful headings by double-clicking a header or using its context menu. Look for blank rows, repeated headings, unexpected null values, and columns that contain a mixture of data types. If a number or date is being interpreted incorrectly, select the column and use Transform > Data Type to choose the appropriate type. Check dates carefully, especially when the JSON was created in a different region or uses timestamps with a time-zone offset. Avoid changing types blindly: an identifier such as 00127 may need to remain text so its leading zeros are preserved.
  7. Load the result into a worksheet. When the preview is usable, select Home > Close & Load to load the query result to a new worksheet as a table. If you want to choose where it goes, use the Close & Load dropdown and select Close & Load To…, then choose Table and either a new worksheet or a location in an existing worksheet. Loading to a new sheet is a safe starting point because it avoids overwriting existing cells. Wait for Excel to finish; a large file may take a while.
  8. Save the workbook in Excel format. Select File > Save As, choose a folder, and set the file type to Excel Workbook (*.xlsx). Give the workbook a name that distinguishes it from the source JSON, then select Save. The worksheet table is now available in an Excel workbook. The workbook may also retain the Power Query connection, which can be useful for refreshing from the source file later. The original JSON remains a separate file; the .xlsx does not replace it.

Common Mistakes

Opening JSON as if it were already a workbook: using File > Open on a JSON file may show raw text, a dialog, or an unhelpful single-column view, depending on the setup. Use Data > Get Data > From File > From JSON to parse the structure. Likewise, renaming .json to .xlsx does not convert it.

Loading before expanding: Power Query may load a column containing the words Record or List rather than the individual fields you expected. Return to the query, expand or convert the relevant column, and load again. Those labels indicate nested data, not necessarily missing information.

Expanding every field without checking the result: some JSON files contain large nested lists, optional fields, or repeated child records. Expanding everything can create a very wide table or many rows. Choose fields based on the task and verify that the resulting row count still makes sense.

Letting Excel guess every data type: automatic type detection can turn codes into numbers, alter date interpretations, or treat inconsistent values as errors. Check columns that contain IDs, postal codes, dates, or values with leading zeros before relying on them.

Saving over the source: the imported workbook and the original JSON are different files. Use a distinct .xlsx name and keep the source if you may need to repeat the import or compare the output.

Tips

Think about the shape of the JSON before importing. If it contains an array of similar objects, each object can often become a row and each named field a column. For example, a list of objects with fields such as product, quantity, and date is a natural table. If it contains a parent object with a nested array of line items, decide whether the useful output is one row per parent or one row per line item; expanding the array can change the number of rows.

Use descriptive column names and remove fields that do not help with analysis. If you keep similarly named fields from different levels, retain the name prefixes during expansion or rename the columns to make their origins clear. This makes the worksheet easier to filter, sort, and share.

If you expect the JSON source to change, keep the query rather than copying and pasting values over the table. Save the workbook and leave the source file in a stable location. When the source is updated, use Data > Refresh All to update the query. If the file is moved or renamed, Excel may not find it; edit the query’s source step or reconnect it to the new path. Review the refreshed output, because a changed JSON structure may add, remove, or rename fields.

For a one-time import, a loaded worksheet table is often all you need. For repeated imports, consider testing the query with a small representative file first, then confirm that refresh works with the real source. Keep a copy of the untouched JSON and the saved workbook until you have checked the results.

Troubleshooting

There is no From JSON option. Confirm that you are using Excel desktop on Windows and opening the Data tab’s Get Data menu, not the regular File > Open dialog. If the connector is missing, update Microsoft 365 and check your organization’s Office settings. On Mac, connector support and Power Query authoring options depend on the version; use a Windows desktop copy of Excel if From JSON is unavailable. Excel for the web is not a dependable substitute for creating this import from scratch.

Excel reports an error or cannot read the file. Confirm that the selected file is the actual JSON file, not a web page saved with a .json extension or a compressed archive. If you can safely inspect it in a text editor, check for incomplete content, comments, trailing commas, or unescaped quotation marks. These can make a file invalid JSON. If the file was downloaded, download it again from its source. Do not manually remove characters unless you understand the data and have preserved an untouched copy.

The preview shows a List, Record, or only one column. That can be normal for nested JSON. Convert a top-level List to a table, then expand its values. For a Record column, use the expand icon; for a nested List, decide whether converting or expanding it will produce the rows you need. If you load the query and see the words Record or List in cells, reopen the query from Data > Queries & Connections, edit it, and expand the relevant column before loading again.

The query loads but values look wrong. Return to Power Query and review data types, regional date interpretation, leading zeros, and null values. For numbers stored as text, select the column and set its type deliberately. For codes that must retain zeros, set the column to Text. If the issue is a timestamp, compare its displayed date and time with the source format and account for any time-zone information before using it.

Refresh fails after moving the file. Queries often refer to the source’s original file path. Put the JSON back in that location, or edit the query’s Source step to point to the new location, then refresh. If the file’s field names or nesting changed, you may also need to revise the expansion steps. Check that the new file has the expected structure before sharing the refreshed workbook.

Frequently Asked Questions

Can Excel open a JSON file directly? Excel can import and parse JSON through Power Query, but a JSON file is not itself an Excel workbook. Use the JSON connector, shape the data, and load it to a worksheet.

How do I save the imported data as an Excel file? After loading the query to a worksheet, select File > Save As and choose Excel Workbook (*.xlsx). Save under a different name from the original JSON file.

Will Excel preserve nested JSON exactly? Not as a worksheet table. Power Query lets you expand or select nested content, and your choices determine the rows and columns loaded. Keep the original JSON if you need the complete source structure unchanged.

Can I refresh the Excel table when the JSON changes? Yes, if the workbook still points to an accessible source file and its structure remains compatible with the query. Use Data > Refresh All, then check for errors or new fields.

Why does the imported table have more rows than the JSON’s top-level list? Expanding a nested list can create several worksheet rows for a single parent record. Review which list you expanded and decide whether the extra detail is useful or whether you need a different table shape.

Can I do this in Excel for Mac? Some Microsoft 365 for Mac builds provide Power Query and JSON import options, but availability and menus can differ. Look under Data > Get Data for From JSON. If it is not available, create the query in Excel for Windows and open the resulting workbook on the Mac; refresh capabilities may vary.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.