Why open JSON in a spreadsheet?
JSON is how most software stores and exchanges data. API responses, exports from web apps, analytics events and records from databases such as MongoDB or Firebase usually arrive as JSON. It is easy for a program to read and hard for a person to scan: a few hundred records can run to thousands of lines of brackets and quotes.
A spreadsheet turns the same data into rows you can sort, filter, total and share. Analysts, account managers and finance teams can work with it straight away, without asking a developer to write a script first.
How to import JSON into Excel with Power Query
Excel 2016 and later, including Microsoft 365, can read JSON through Power Query. It works, but it takes several steps:
- Choose Data › Get Data › From File › From JSON and select the file.
- The Power Query Editor opens. If the top level is an object, click the List next to the field that holds your records to drill into it.
- On the List Tools › Transform tab, click To Table, then OK.
- The table has one column of Record values. Click the expand button (two arrows) in its header, choose the fields you want, clear Use original column name as prefix and click OK.
- Repeat the expand step for every nested record. For a list inside a record, the expand button offers Expand to New Rows, which repeats the whole record once per item, or Extract Values, which joins the items with a separator you choose.
- Check each column’s data type, then click Close & Load.
Where Power Query gets fiddly
Nested data multiplies the work. Each level of nesting needs its own expand step, and the column list is taken from a sample of records, so a field that appears only in later records can be missed.
Lists can duplicate rows. Expanding a list to new rows means an order with three items appears three times, which breaks totals unless you are careful.
Types need attention. Codes with leading zeros, long IDs and dates may be converted in ways you did not intend, so check every column type before loading.
The result is a query. The table stays linked to the original file. That is useful for refreshing, but it is more than you need for a one-off look at the data.
Where this converter helps
Checking an API response. Save the response to a file and see every record and field in a grid, which makes missing or odd values easy to spot.
Exports from web apps and databases. Many SaaS tools, Firebase and MongoDB export collections as JSON. Converting them gives you a sheet for reporting or reconciliation.
Analytics and event logs. Event data with nested properties becomes flat columns you can filter and pivot.
Sharing data with people who do not code. Send a colleague an .xlsx file instead of a JSON file they cannot open comfortably.
Private files. Everything runs in your browser. Customer records and API data are never uploaded to a server.
How the conversion works
- Finding the records. A list of objects is used as it is. If the file is an object that wraps a list, such as
{"orders": [...], "count": 3}, the first list of records becomes the rows. - Columns from every record. Each field found in any record becomes a column, in the order it first appears.
- Nested objects are flattened.
{"customer": {"name": "Priya Shah", "city": "Ahmedabad"}}becomes the columns customer.name and customer.city. - Lists stay in one cell.
"items": ["Tea", "Sugar"]is written as the text [“Tea”,“Sugar”], so one record is always one row. - Types are respected. Numbers become numbers, true/false become Excel’s TRUE and FALSE, and strings stay text, so “000123” keeps its zeros. Dates in JSON are already text and stay exactly as written.
- Every character survives. Names in Hindi, Gujarati or any other script come through intact.
The download is an .xlsx file named after your original, for example orders-cleaned.xlsx. With the sample file, the summary reads “Converted 3 rows and 7 columns from JSON to Excel (.xlsx).”
After converting
Every SheetTidy tool accepts JSON files directly, so you can also open the .json file in another tool, such as Remove duplicates or Remove extra spaces, and clean the data on the way to Excel. To turn date text such as “2024-03-15” into real Excel dates, use the Fix dates tool on the workbook. To go back the other way, the Excel to JSON converter writes a sheet out as an array of records.