To open a JSON file in Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From JSON, choose the file, shape the data in Power Query, then select Home > Close & Load. This imports JSON as structured data you can review and refresh, rather than opening the file as an ordinary worksheet.
What You’ll Need
- A JSON file saved on your computer or somewhere you can access through a file path. JSON is a text format, but Excel needs to interpret its records and lists before it can lay them out in columns.
- Microsoft Excel for Microsoft 365 on Windows. The steps below use the Power Query tools built into Excel. If your organization manages Office updates, the available connectors may depend on its settings.
- A general idea of which fields you want as columns and what one row should represent. For example, an order file might need one row per order, while a file of customers might need one row per customer.
- Permission to access the file. If it is stored on a company network, cloud location, or behind a sign-in, you may need to connect to that location or authenticate before importing.
Step-by-Step
- Check the JSON file before importing. Find its location and, if you are unsure what it contains, open a copy in a plain-text editor. JSON commonly consists of a list of objects, with each object representing one item and its fields. A small example might contain several product objects, each with a name, price, and category. You do not need to edit the file; this quick look helps you recognize the data when Power Query displays it. Avoid changing punctuation or braces unless you know the JSON format, because a small syntax error can make the entire file unreadable.
- Start the JSON import. In Excel, open a blank workbook or the workbook where you want the imported data. Select the Data tab, then choose Get Data > From File > From JSON. In the file picker, browse to the JSON file, select it, and choose Import or Open, depending on the dialog. Selecting the file this way starts a Power Query connection. It does not simply paste the original text into a cell.
- Inspect the Power Query preview. Power Query Editor opens and shows a preview of the JSON structure. The first screen may show a List, a Record, or a table, depending on how the file is organized. A list usually represents multiple items; a record usually represents one item with named fields. Click a List or Record value to explore it. Use the navigation controls or the displayed breadcrumb to move back if you drill into the wrong level. The aim is to reach the collection of repeated items that should become your worksheet rows.
- Convert a list into a table when needed. If the preview shows the list of items you want as rows, select To Table in the Power Query Editor. Review the conversion dialog and keep the default delimiter unless the data calls for a different choice, then select OK. If the top level is a single record, inspect its fields first and select the field that contains the repeated list. A record by itself is one object, not a set of repeated rows; it may be more useful to keep its fields as columns or combine it with the list it describes.
- Expand records into columns. A table may have a column whose cells are labeled Record. Select the expand icon, shown as two arrows, in that column’s header. Choose the fields you want to turn into columns, and clear fields you do not need. If the option to use the original column name as a prefix is selected, Excel includes that name in the expanded column headings; clear it for shorter headings when that makes the result easier to read. Select OK to apply the expansion.
- Expand nested lists or records as needed. Some fields contain another list or record, such as an address, a set of tags, or line items belonging to an order. Expand the relevant column again and decide how to represent the nested information. Expanding a list can create multiple rows for one original item. That is useful when each list entry needs its own row, but it can repeat the other fields from the parent record. If you need one row per order, for instance, consider whether order line items should stay nested or become separate rows before expanding them.
- Set column names and data types. Rename headings by double-clicking a column name or using the column’s context menu. Check the type icon beside each heading and set fields appropriately, such as Date, Whole Number, Decimal Number, or Text. Identifiers such as postal codes and account numbers are often safer as text, even if they contain only digits, because Excel may remove leading zeros or interpret long numbers in an unwanted way. Dates can also be ambiguous when a file uses a different regional format, so verify that the preview matches the source.
- Load the result into the workbook. When the preview is ready, select Home > Close & Load to send the query to Excel. To choose a destination or load type, open the Close & Load menu and select Close & Load To. Choose Table and either a new worksheet or an existing worksheet with enough clear space, then confirm. Excel creates a table from the imported data. If you choose an existing worksheet, make sure the destination will not overwrite other content.
- Check the worksheet and save. Confirm that the headers, rows, and values match the JSON structure you intended to import. Look for unexpected blank fields, repeated rows, or values that have been interpreted as dates or numbers. Save the workbook as an Excel workbook, such as an .xlsx file. The original JSON file remains separate; importing it does not convert or replace that source file.
After the initial import, Excel stores the steps as a Power Query. If the JSON source changes in the same location and retains a compatible structure, select a cell in the imported table and use Data > Refresh All to update the query. Refreshing reruns the saved steps; it is not the same as opening the JSON file again as a new import.
Common Mistakes
Opening JSON with the ordinary Open command. Excel may treat the file as text or show a confusing wall of braces and punctuation. Use Data > Get Data > From File > From JSON instead, so Power Query can interpret the structure.
Stopping at the first preview. A preview that displays List or Record is not necessarily an error. It often means the file has nested structure and needs to be drilled into or converted before it becomes a useful table. Find the repeated collection that should define the rows.
Expanding every field without considering the row meaning. Expanding a nested list can multiply rows. If one customer has three addresses, expanding addresses may create three rows for that customer. Decide whether that is the intended layout before loading, and keep a stable identifier so repeated parent records remain recognizable.
Letting Excel guess types without checking. Automatic type detection can turn text that looks numeric into a number, strip leading zeros, or misread dates. Set sensitive fields to Text and confirm dates and decimals against the source. Correcting types in Power Query is often better than trying to repair the display after the data is loaded.
Assuming every JSON file has the same shape. One file may contain a top-level list, while another has a record with a list inside it. A file can also have optional fields that are missing from some items. Do not assume that a click sequence or column set will fit every file; inspect the preview and adapt the query.
Tips
Plan the table around the question you need to answer. A flat table works well for sorting, filtering, charts, and PivotTables, but it cannot always preserve every relationship in a deeply nested JSON document in a single neat layout. If the file contains customers and a separate list of transactions, separate queries or tables may represent those structures more clearly than expanding everything into one large table.
Keep the source file in a stable folder if you expect to refresh the import. Moving or renaming it can break the saved source path. If you need to move the workbook or share it with someone, also consider how that person will access the JSON file. A query that points to a path on your computer may not work on another person’s computer without updating the source.
Give the query and worksheet clear names. In the Power Query Editor, the query name appears in the query settings pane; use a description that identifies the contents rather than leaving several queries named generically. In Excel, rename the worksheet tab and table to match the data. Clear names make refreshes and later edits easier to manage.
Keep a copy of the original JSON file. Power Query transforms the imported data for the workbook, but it does not edit the source. Keeping the source lets you compare results, retry the import, or rebuild the query if you later change the desired layout. If the file contains private information, store both files in locations approved for that data.
For repeated imports, test the query with a representative file that includes optional and nested fields. Then refresh it using another real file before relying on the output. If the source structure changes, such as a field being renamed, Power Query may report an error or produce a different set of columns. A quick review after refresh helps catch changes before you use the table in reports.
Troubleshooting
From JSON is missing from the menu. Confirm that you are using desktop Excel for Microsoft 365 on Windows and that the Data tab is visible. The command is under Data > Get Data > From File. If your version uses a different ribbon layout or your organization has restricted connectors, check Office updates or ask your administrator. Excel for the web does not provide the same desktop JSON-import workflow. On a Mac, Power Query features and connectors can differ by Excel version; if From JSON is not available in your Data or Get Data menus, use the Windows desktop workflow rather than assuming the menus are identical.
Excel reports that the JSON is invalid. The file may be incomplete, contain a syntax error, or have been saved in a format Excel cannot parse. Check that the file extension and contents really indicate JSON, and reopen a fresh copy from the system that created it. Common problems include a missing closing brace, an extra comma, or a file that contains an error message instead of JSON. If the file was downloaded, make sure the download finished. Do not try to fix a large file by deleting punctuation at random; ask the source system to export a valid copy if needed.
The preview shows only one value or an unexpected structure. Look for a field containing the list of repeated items and click that List or Record value. Some services wrap the actual results inside a property such as data, results, or items. The correct name varies by source, so inspect the available fields rather than assuming a particular one. If the source is a single object, it may legitimately produce only one row or a few fields.
Columns or values are missing. Reopen the query from Data > Queries & Connections, then edit it and inspect the applied steps in Power Query Editor. A field may be nested and not yet expanded, absent in some records, or excluded during expansion. If the source has inconsistent records, compare several objects in the preview. You may need to expand a different level or adjust the query after the source schema changes.
Refresh fails after moving the file. The saved query may still point to the old path. Open Data > Queries & Connections, right-click the relevant query, and choose Edit. In the Power Query Editor, use Home > Data source settings to review or change the source path when that option is available for the connection. If necessary, use the source step in the query to select the file again, then apply the changes and refresh.
The loaded table has too many rows or strange types. Revisit the expansion steps and check whether a nested list produced multiple rows for each parent item. Also inspect the Changed Type step in the query, which may have automatically assigned a type that does not fit the values. Change the type in Power Query, then close and load again. If one row per parent record is essential, do not expand a child list into rows unless you have a plan for representing that one-to-many relationship.
Frequently Asked Questions
Can I open a JSON file directly in Excel?
Yes, but for structured columns and rows, import it through Data > Get Data > From File > From JSON. The ordinary File > Open route may display the file as text and will not provide the same Power Query shaping steps.
Does Excel change the original JSON file?
No. Power Query reads the source and loads a transformed result into the workbook. Save the workbook separately as an .xlsx file; the JSON remains a separate source file unless you deliberately edit it in another program.
Can Excel open a large JSON file?
It may, but performance depends on file size, nesting, available memory, and the complexity of the transformations. If the import is slow, try a smaller export, remove fields you do not need in Power Query, or ask the data provider for a more focused extract. Excel worksheets also have a limit of 1,048,576 rows, so a larger result cannot fit on one worksheet.
Can I refresh the data after the JSON changes?
Usually, yes. Keep the source accessible at the same path and preserve a compatible structure, then select Data > Refresh All. If the file has moved or its field names have changed, update the query or fix the source before refreshing.
Can I import JSON in Excel for the web or on a Mac?
Excel for the web does not offer the same full desktop Power Query workflow for importing a local JSON file. On Mac, available Power Query connectors depend on the Excel version, so check whether From JSON appears in the Data or Get Data menu. If it does not, use Excel for Microsoft 365 on Windows for these steps.
Related
- How to Insert a Line in a Word Table: Step by Step
- How to Insert a Line Between Columns in Microsoft Word
- How to Insert a Line Between Rows in Microsoft Word
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Set Vacation Out of Office in Outlook: Setup and TipsTo set a vacation out-of-office reply in Outlook, open Automatic Replies, turn them on, choose your dates, write…
- How to Freeze the Top Row in Excel While ScrollingTo keep the first row visible while you scroll in Excel for Microsoft 365 on Windows, open the…
- How to Add a Side Header in Microsoft Word DocumentsTo add a vertical side header in Word, open the document’s header, insert a text box, rotate it…
- How to Put a Filter in Google Sheets and Sort DataTo put a filter in Google Sheets, select your data range, including its header row, then choose Data…
- Google Sheets FILTER: Array Formulas ExplainedIn Google Sheets, use FILTER to return rows or columns that meet conditions; it expands its results automatically,…
- Animating Text and Objects in PowerPoint: A Practical GuideTo animate text or an object in PowerPoint, select it, open the Animations tab, choose an effect, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.