To open a JSON file in Microsoft Excel for Microsoft 365 on Windows, go to Data > Get Data > From File > From JSON, choose the file, and select Import. Use Power Query to turn the imported records into columns and rows, then choose Close & Load to put the result in a worksheet. This works best when you want to analyze or filter JSON data in Excel; you do not need to rename the file or edit its contents first.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows. The steps below use its Power Query JSON connector.
- A JSON file saved somewhere you can browse to, such as your computer or an accessible folder. If the data is in a web page, download the actual .json file first; a web page that displays JSON is not necessarily a downloadable JSON file.
- Permission to read the file and enough access to save a new Excel workbook. If the JSON comes from a work system, follow your organization’s rules for handling its data.
Step-by-Step
- Check that you have the data file. Locate the file in File Explorer and check that its name ends in .json. Do not open it in Excel by double-clicking and expect Excel to interpret it as a table: JSON is a structured text format, and Excel’s import connector is the more reliable route. If you only have JSON copied as text, paste it into a plain-text editor and save it with a .json extension, taking care not to save it as .json.txt.
- Start the JSON import. Open Excel and either create a blank workbook or open the workbook where you want the imported data. On the ribbon, select Data > Get Data > From File > From JSON. If the Get Data menu is collapsed, open the Data tab and look for the Get Data command in the Get & Transform Data group. Select the JSON file in the file browser and choose Import.
- Review the preview in Power Query Editor. Excel opens Power Query Editor, which shows the structure it detected. JSON may begin with a list, a record, or a value containing other records and lists. A list is a sequence of items, often written between square brackets. A record is a set of named properties, often written between curly braces. The preview is a transformation workspace, not yet a finished worksheet, so seeing a word such as List or Record at this stage is normal.
- Convert a top-level list into a table. If the preview is a list of items, choose To Table on the Power Query Editor ribbon. Review the dialog options and keep the defaults unless you have a specific reason to change them, then select OK. You should now see a table with a column containing the list items. If the list contains simple values such as dates or names, that may already be close to the result you need. If it contains records, continue to expand the record column.
- Turn records into useful columns. When a table column contains Record values, select the expand icon in that column’s header. It looks like two arrows pointing away from one another. In the menu, select the fields you want as columns and choose OK. Clear the option to use the original column name as a prefix if you prefer shorter headers; leaving it selected can help distinguish fields that have similar names at different levels. Inspect the preview after expanding to confirm that each field appears under the expected heading.
- Expand nested lists or records as needed. A JSON field can contain another record or list rather than a single value. If a cell displays Record, you can expand it using that column’s expand icon. If it displays List, first decide how the items should relate to the surrounding rows. Expanding a list can create multiple rows for one original record, which is appropriate when each list item is a separate observation. For example, one order with three products may become three rows with repeated order details. If you want one row per order, consider keeping the list as a nested value or transforming it differently rather than expanding it blindly.
- Choose what to load. When the preview has the columns and rows you need, select Home > Close & Load to load the result as a table in a new worksheet. To choose a destination or create a connection without immediately placing a table, use the arrow beside Close & Load and select Close & Load To…, then choose the available destination and output options. For a first import, loading to a table in a new worksheet is usually the easiest choice to inspect.
- Save the workbook and verify the result. Save the workbook as an .xlsx file using File > Save As. Check several rows against the source data, especially fields that contain dates, numbers, or nested information. If you later replace or update the JSON file at the same location, use Data > Refresh All to update the query. Excel may ask you to confirm permissions or credentials if the source location requires them.
Power Query steps are saved with the workbook, so you can revise the transformation later. To reopen them, select a cell in the loaded table and choose Data > Queries & Connections. In the pane, double-click the relevant query. This is useful if you initially expanded too many fields, want to rename columns, or need to adjust how nested data is arranged.
Common Mistakes
- Using File > Open for the JSON file. Excel’s ordinary workbook-opening route is not the same as importing structured JSON. Start from the Data tab and use the JSON connector instead.
- Assuming every JSON file is already a flat table. A JSON document can contain nested records, lists, or a single object. You may need to convert a list and expand one or more columns before it resembles a spreadsheet.
- Expanding every list without checking the result. Expanding a list can multiply rows. Check whether that matches the meaning of the data; otherwise, totals or counts may appear larger because a parent record is repeated.
- Removing fields too early. A field that looks unimportant in the initial view may hold an identifier needed to match records later. Keep a copy of the original file and confirm which fields you need before removing columns.
- Saving over the source or losing the query connection. Save the finished workbook as an Excel file, not in place of the JSON source. Keep the JSON at a stable location if you expect to refresh the query.
Tips
- Begin with a small sample when the file is large. If you control the export, create a smaller JSON file with representative records first. Confirm that the fields expand correctly, then repeat the steps with the full file.
- Keep meaningful identifiers. Fields such as an order ID, customer ID, or timestamp help you trace a row back to its source and are useful when you combine data later.
- Use clear column names. In Power Query, double-click a column heading to rename it. Prefer names that explain the values, and avoid ambiguous headings when several nested records have similarly named fields.
- Check data types after importing. Power Query may detect numbers, dates, or text automatically. Inspect columns for unexpected errors or values that became dates or decimals incorrectly. Use the type icon beside a column heading to choose a more suitable type when needed.
- Keep the source path stable. Queries usually refer to the file path used during import. Moving or renaming the JSON file can prevent a refresh. If you reorganize folders, edit the query’s source or import the file again from its new location.
- Protect sensitive information. JSON files can contain personal or confidential fields that are not obvious in a brief preview. Review the columns before sharing the workbook, and remove data you do not need.
Troubleshooting
There is no From JSON option. Confirm that you are using the desktop version of Excel and that you are looking under Data > Get Data > From File. If your ribbon is customized or narrow, expand the Get Data menu. Excel for the web and some older or restricted installations may not show the same Power Query connectors. Update Microsoft 365 or use Excel for Microsoft 365 on Windows if the connector is unavailable.
The file will not import or produces an error. Check that the file is valid JSON rather than an HTML error page, a spreadsheet renamed with a .json extension, or a partial download. JSON syntax requires matching braces and brackets, quoted property names, and valid commas; a trailing comma can make a file invalid. If you can, open a copy in a plain-text editor or a JSON validator approved by your organization to locate malformed text. Do not change the original until you have a backup.
The preview shows only List, Record, or Value. That usually describes the outer structure rather than an import failure. For a list, select To Table and expand any record columns. For a record, open or convert the record using the options available in the editor, then expand the fields you need. If a particular field contains a list, decide whether its items should become separate rows before expanding it.
Some rows or fields are missing. The source may use inconsistent structures, such as records that do not all include the same properties, or a nested list may have been left unexpanded. Check the source and the steps listed in Power Query’s Applied Steps pane. Select earlier steps one at a time to see where a field disappears, and adjust the expansion choices there. A field absent from some records may produce blank cells for those rows.
Refresh says the source cannot be found. The file may have moved, been renamed, or become unavailable on a network or cloud location. Put it back at the original path, reconnect to the accessible location, or edit the source step in Power Query. Also check that you still have permission to access it. If the source is a download that changes names each time, save it under a consistent name and location before refreshing.
Frequently Asked Questions
Can Excel open JSON directly as a worksheet?
Not as a neatly arranged table through the normal File > Open route. Use the JSON connector under Data > Get Data > From File > From JSON, then shape the data in Power Query and load it.
Does importing JSON change the original file?
No. Power Query reads the source and saves the import steps in the workbook. The loaded worksheet is an Excel result; editing it does not rewrite the original JSON file.
Can I open a JSON file on a Mac?
Power Query and connector availability depend on the Excel for Mac version and build. Check the Data tab for a Get Data or Power Query option that includes JSON. If it is not available, use Excel for Microsoft 365 on Windows or convert the data to a supported format such as CSV before importing. Menu labels and capabilities can differ on Mac.
Can I open JSON in Excel for the web?
The desktop Windows steps in this guide use Power Query’s JSON connector. Excel for the web may not offer the same direct import workflow. Import the file in desktop Excel, save the workbook to a location you can access, and then open the workbook online if you need to view it there.
Why does one JSON file create several worksheet rows for a single item?
A nested list may have been expanded. Power Query creates a row for each list item and repeats the parent fields where necessary. This is helpful when list items need to be analyzed individually, but keep the list nested or change the transformation if you need one row per parent item.
Will Excel update the table when the JSON changes?
The table does not update continuously. If the source file is still accessible at the expected location, select Data > Refresh All to run the saved query again. Changes to the file’s structure may require you to revisit the query and adjust its expansion or data type steps.
Related
- How to Open a CSV File in Excel Without Losing Data
- How to Open a Text File in Excel Without Losing Data
- How to Open an AIS JSON File in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Dropdown Equals: Formulas and Rules ExplainedTo check whether a Google Sheets dropdown equals a particular choice, compare the dropdown cell with that value…
- Google Sheets Filter: Show Only the Rows You NeedTo filter data in Google Sheets, select a cell in your table and choose Data > Create a…
- Excel Text Hover: How to Show Notes and Tooltips on CellsExcel does not have a general setting that turns any cell’s text into a hover tooltip. In Excel…
- Excel Heatmap Table: How to Create and Read OneIn Excel for Microsoft 365 on Windows, select the numeric cells in your table, then choose Home >…
- Out of Office in Outlook 365: Set an Automatic ReplyTo set an out-of-office email in Outlook 365, open Automatic Replies for your account, turn on automatic replies,…
- Excel Data Validation: Create a Yes-or-No DropdownYes—Excel can use Data Validation to limit a cell to Yes or No, usually with a dropdown list.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.