How to Open a JSON File in Excel: 5 Methods That Actually Work

If you have landed here, you have probably already tried the obvious thing: you found a .json file, double-clicked it, and Windows opened it in Notepad or your browser instead of Excel. Or you tried File → Open in Excel, pointed it at the JSON file, and got a wall of unreadable text in column A.

Neither of those is a mistake on your part. Excel has no file association for JSON and no "just open it" path, because JSON and spreadsheets are structurally different. A spreadsheet is a grid: rows and columns, fixed width. JSON is a tree: objects inside objects, arrays of different lengths, values that may be missing entirely. Something has to decide how that tree becomes a grid, and Excel will not guess for you.

Below are the five methods that work, ordered by how most people should approach the problem.

Quick comparison

Method 1: Power Query (built into Excel)

Power Query is Excel's own answer to this problem, and it is the right first choice on Windows. It ships with Excel 2016 and later, and with Microsoft 365.

1. Open a blank workbook.
2. Go to the Data tab.
3. Click Get Data → From File → From JSON.
4. Select your .json file and click Import.
5. The Power Query Editor opens showing your data as a list or a record.
6. Click To Table if you see a list, then use the expand icon (⇔) in the column header to turn fields into columns.
7. Click Close & Load.

Step 6 is where people get stuck, and it is worth understanding why. Power Query does not flatten your data automatically. It gives you one column containing Record or List values, and you expand them one level at a time. For a flat array of objects that is a single click. For data nested three levels deep, it is a click per level, per branch.

We have a step-by-step walkthrough with screenshots of each expand step in our Power Query JSON import guide.

Why Power Query sometimes says "We couldn't find any data formatted as a table"

This message usually means your JSON has an object at the root rather than an array. Power Query can read it, but it presents it as a single record instead of a table. For example, this is a record:

{
  "generated": "2026-08-18",
  "users": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ]
}

The table you actually want is inside users. In the Power Query Editor, click into the users field first, then convert that to a table. The wrapper object is metadata, not rows.

Method 2: Use an online converter

If you have one file and you want a spreadsheet in the next thirty seconds, a converter is the faster path — and it is the only practical option on Excel for Mac.

The important thing to check before pasting your data anywhere is whether the conversion happens in your browser or on someone else's server. If a tool uploads your file, your data has left your machine, which matters a great deal when the file is a customer export or an internal API response.

Our JSON to Excel converter runs entirely in your browser. The file is read with the JavaScript File API, parsed in memory, and written to XLSX locally. No network request carries your data. It also flattens nested structures automatically using dot notation, which is the step Power Query makes you do by hand.

Method 3: Google Sheets, then download as Excel

If your JSON comes from a URL rather than a file on disk, Google Sheets is a reasonable route. You write a small Apps Script function that fetches the endpoint and writes rows, then export the sheet as .xlsx.

This is more setup than the other methods, but it has one advantage none of them share: the sheet can refresh itself on a schedule. If you need the same report every Monday, this is the method that stops being manual. The full script is in our guide on using JSON data in Google Sheets.

Method 4: Python (for automation or very large files)

If this is going to happen more than a few times, twelve lines of Python will do it repeatably:

import json
import pandas as pd

with open("data.json", encoding="utf-8") as f:
    data = json.load(f)

# json_normalize flattens nested objects into dotted columns
df = pd.json_normalize(data)
df.to_excel("output.xlsx", index=False)

pd.json_normalize() is the function that matters here. Plain pd.DataFrame(data) will leave nested objects as Python dicts inside cells, which exports to Excel as unreadable text like {'city': 'London'}. json_normalize turns them into address.city columns instead.

We cover the full range of options, including nested arrays and record paths, in converting JSON to Excel in Python.

Method 5: The Text Import Wizard (and why it usually disappoints)

A common suggestion online is to rename data.json to data.txt and open it with Excel's Text Import Wizard, using commas as delimiters. It is worth explaining why this mostly does not work, because it wastes a lot of people's time.

The wizard splits on a delimiter character. It has no concept of structure. Given this input:

[{"name":"Alice","tags":["admin","billing"]}]

splitting on commas produces fragments like [{"name":"Alice" and "tags":["admin". Every brace, bracket and quote survives into the cells, and any comma inside a string value or an array breaks the alignment. You end up with a grid of debris that takes longer to clean than converting properly would have taken.

The one case where the wizard is genuinely useful is JSON Lines — a file where each line is its own JSON object. Even then a purpose-built approach is better; see converting JSONL to Excel.

What happens to nested data

Whichever method you choose, you eventually face the same question: what should a nested object become in a flat grid? The standard answer is dot notation. This JSON:

{
  "name": "Alice",
  "address": { "city": "London", "country": "UK" }
}

becomes three columns:

Arrays are harder, because their length varies per row. Arrays of simple values are usually joined into one cell (admin; billing), while arrays of objects are expanded into indexed columns (items.0.sku, items.1.sku). Our guide to handling nested JSON in Excel goes through the trade-offs of each choice.

Troubleshooting

"The file format is not valid"

You are trying to open the JSON directly through File → Open. Use Data → Get Data instead, or convert the file first.

Everything lands in column A

Excel imported the file as plain text. This is the Text Import Wizard outcome described above. Undo and use Power Query or a converter.

Numbers become dates, or leading zeros disappear

This is Excel's autoformatting, not a conversion error. A value like "01234" is a string in JSON, but Excel sees digits and stores a number, dropping the zero. Format the column as Text before pasting, or import via Power Query where you can set the column type explicitly. Product codes, postcodes and phone numbers are the usual casualties.

The file will not load at all

Check that the JSON is valid first — a single trailing comma will stop every tool here. Paste it into a validator, or see our list of common JSON errors and how to fix them. If the file is very large, the limits are covered in working with large JSON files.

Which method should you use?

• One file, want it now: online converter.
• Same import every week, on Windows: Power Query — it saves the steps and refreshes.
• On a Mac: online converter or Python; the Power Query JSON connector is not there.
• Data lives at a URL: Google Sheets with a fetch script.
• Hundreds of files, or files above ~50 MB: Python.