How to Open a JSON File in Excel (3 Methods That Actually Work)

You received a .json file. You need it in Excel. The problem: double-clicking a JSON file opens a text editor, not a spreadsheet. Even dragging it into Excel produces a blank workbook.

JSON and Excel don't speak the same language natively. JSON is hierarchical — objects inside objects, arrays inside arrays. Excel is flat — rows and columns. Getting from one to the other takes an extra step, but it's straightforward once you know which method to use.

This guide covers three approaches:

1. Power Query — built into Excel, repeatable, handles updates
2. Online converter — fastest for a one-off file, no software needed
3. Python (pandas) — for automation and large files

Skip to whichever fits your situation.

---

Method 1: Power Query (Built Into Excel)

Power Query is Excel's built-in data import tool. It's been available since Excel 2016 and is included in all current versions of Microsoft 365. No add-ins, no installs — it's already there.

Best for: Files you'll refresh regularly, or when you want Excel to pull from a URL.

Step-by-step

1. Open Excel and go to Data → Get Data → From File → From JSON

2. Navigate to your .json file and click Import

3. Power Query Editor opens. You'll see the JSON structure represented as a list. Click Into Table (top left of the toolbar) to convert it.

4. A dialog asks about delimiters — leave the defaults and click OK. You now have a table with one column called Column1, where each row contains a Record.

5. Click the expand icon (two arrows pointing outward) at the top right of the Column1 header. Excel shows all the field names it found in the JSON. Uncheck any you don't need, then click OK.

6. Your JSON fields are now columns. Click Close & Load (top left) to send the data to your spreadsheet.

What it looks like

Starting with a JSON file like this:

[
  {"id": 1, "name": "Alice Chen", "department": "Engineering", "salary": 95000},
  {"id": 2, "name": "Bob Kumar", "department": "Design", "salary": 78000},
  {"id": 3, "name": "Sara Mills", "department": "Management", "salary": 110000}
]

After Power Query, you get a proper Excel table:

| id | name | department | salary | |----|------|------------|--------| | 1 | Alice Chen | Engineering | 95000 | | 2 | Bob Kumar | Design | 78000 | | 3 | Sara Mills | Management | 110000 |

Refreshing the data

If your JSON file updates regularly (a nightly export, a synced file), Power Query remembers the source. Go to Data → Refresh All and Excel re-reads the file with the latest data. No re-importing required.

Handling nested JSON in Power Query

Flat JSON (like the example above) imports cleanly. Nested JSON — objects inside objects — requires an extra step.

If your JSON has a nested object like "address": {"city": "Austin", "state": "TX"}, the address column will contain [Record] values after the initial expand. Click the expand icon on that column too and select which sub-fields to include. Repeat for each nested level.

For deeply nested JSON (3+ levels), Power Query becomes tedious. In that case, Method 2 or Method 3 below is faster.

---

Method 2: Online Converter (Fastest for One-Off Files)

If you have a JSON file and just need an Excel file right now — no recurring imports, no scripting — the JSON to Excel converter is the fastest path.

Best for: One-off conversions, files from colleagues, API exports you won't need to refresh.

How to use it

1. Go to the JSON to Excel converter
2. Paste your JSON or upload the file
3. Click convert — the tool flattens nested objects automatically using dot notation (address.city, address.state)
4. Download the .xlsx file and open it in Excel

The whole process takes about 30 seconds. The converter handles:

• Flat arrays of objects
• Nested objects (flattened to dot-notation column names)
• Arrays of objects within each record (expanded to multiple rows)
• Mixed schemas where different records have different fields
• Files of any size — it runs entirely in your browser, nothing is uploaded

No account, no sign-up, no file size limit.

When the online converter beats Power Query

• You need a result in under a minute
• The JSON came from someone else and you won't need to refresh it
• The JSON is deeply nested and Power Query's multi-click expand process would take too long
• You're on a computer where you don't want to install anything or change Excel settings

---

Method 3: Python with pandas

If you're importing JSON files regularly, working with large files, or building an automated pipeline, Python gives you full control.

Best for: Scheduled imports, files over 100MB, when you need to transform data before importing, or when you're already writing code.

Install pandas if you haven't

pip install pandas openpyxl

openpyxl is the engine pandas uses to write .xlsx files.

Flat JSON — two lines

import pandas as pd

df = pd.read_json("employees.json")
df.to_excel("employees.xlsx", index=False)

index=False prevents pandas from writing its row numbers as an extra column. The output opens directly in Excel.

Nested JSON — use json_normalize

import pandas as pd
import json

with open("employees.json") as f:
    data = json.load(f)

# Flatten nested objects into dot-notation columns
df = pd.json_normalize(data, sep=".")
df.to_excel("employees_flat.xlsx", index=False)

If your JSON looks like this:

[
  {
    "id": 1,
    "name": "Alice Chen",
    "address": {"city": "Austin", "state": "TX"},
    "scores": [95, 87, 92]
  }
]

json_normalize produces columns: id, name, address.city, address.state, scores. The array column (scores) is kept as a Python list — you'd need to join it to a string if you want a clean cell value:

df["scores"] = df["scores"].apply(lambda x: "|".join(str(v) for v in x) if isinstance(x, list) else x)

Real API data — a Stripe export

API responses have more nesting. Here's how to handle a Stripe payment export:

import pandas as pd
import json

with open("stripe_payments.json") as f:
    payments = json.load(f)

df = pd.json_normalize(payments, sep=".", errors="ignore", max_level=2)

# Rename the long dot-notation columns to something readable
df = df.rename(columns={
    "customer.email": "customer_email",
    "customer.name": "customer_name",
    "payment_method.card.brand": "card_brand",
    "payment_method.card.last4": "card_last4",
})

# Keep only the columns you need
columns = ["id", "status", "amount", "currency", "customer_email", "customer_name", "card_brand", "card_last4", "created"]
df = df[[c for c in columns if c in df.columns]]

# Convert unix timestamp to readable date
df["created"] = pd.to_datetime(df["created"], unit="s").dt.strftime("%Y-%m-%d %H:%M")

# Export — utf-8-sig encoding so Excel opens it correctly
df.to_excel("stripe_payments.xlsx", index=False, engine="openpyxl")
print(f"Exported {len(df)} rows")

Writing multiple sheets

If your JSON has multiple datasets — or you want to split data into separate sheets — use ExcelWriter:

import pandas as pd
import json

with open("report.json") as f:
    data = json.load(f)

# Assume the JSON has top-level keys for each dataset
summary_df = pd.json_normalize(data["summary"])
detail_df  = pd.json_normalize(data["transactions"], sep=".")

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    summary_df.to_excel(writer, sheet_name="Summary", index=False)
    detail_df.to_excel(writer, sheet_name="Transactions", index=False)

print("Wrote report.xlsx with 2 sheets")

---

Which method should you use?

| Situation | Best method | |-----------|-------------| | One-off file, need it now | Online converter | | File updates regularly, want to refresh in Excel | Power Query | | Deeply nested JSON | Online converter or Python | | Large files (> 50MB) | Python with pandas | | Building an automated pipeline | Python | | Non-technical user, no coding | Online converter | | Need multiple sheets or custom formatting | Python |

---

Common problems and fixes

"Excel opens JSON as a wall of text in one cell" You double-clicked the file. Excel tries to open it as a text file and puts everything in cell A1. Use Data → Get Data → From JSON instead, or use the online converter.

Power Query shows [List] or [Record] in cells instead of values This means there's another level of nesting that hasn't been expanded yet. Click the expand icon on those columns to drill down another level.

Column names in Power Query have Column1 everywhere This usually happens when the JSON root is an object, not an array. Power Query wraps the root in a list. Click Into Table, then expand Column1 to see the actual fields.

Dates show as large numbers in Excel (like 1706400000) These are Unix timestamps — seconds since January 1, 1970. In Python, fix them before export: pd.to_datetime(df["created"], unit="s"). In Power Query: right-click the column → Change Type → Date/Time, or add a custom column with = #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[created]).

JSON has records with different fields and some columns are blank This is expected behavior. Power Query and pandas both handle sparse data by filling missing fields with null/blank. All field names across all records appear as columns; records that don't have a field get an empty cell.

---

Frequently Asked Questions

Can Excel open a JSON file directly?

Not by double-clicking. Excel doesn't recognize .json as a native format. You need to import it via Data → Get Data → From JSON (Power Query), use an online converter, or convert it with Python first.

What version of Excel supports JSON import with Power Query?

Power Query's JSON import is available in Excel 2016, Excel 2019, Excel 2021, and all Microsoft 365 versions. It's not available in Excel 2013 or earlier. On Mac, Power Query arrived in Microsoft 365 for Mac in 2019.

My JSON is from an API — will it work?

Yes. Copy the API response and save it as a .json file, then import it. Most API responses are either a JSON array (a list of objects) or a JSON object with a data key containing the array. Power Query and the online converter handle both. For the object case, expand the data key first in Power Query.

Why does Power Query show fewer rows than I expect?

Check if your JSON root is a nested object rather than a plain array. If it's {"count": 150, "data": [...]}, Power Query sees two top-level records (count and data). Expand data to get the actual rows.

Can I convert JSON to Excel without Microsoft Office?

Yes. The online converter produces a .xlsx file that opens in Google Sheets, LibreOffice Calc, and any other application that supports the Excel format. No Microsoft Office required.

How do I keep leading zeros in Excel after converting from JSON?

Excel auto-formats cells as numbers, dropping leading zeros from values like "01234" or "007". Fix it in Power Query by right-clicking the column and setting the type to Text before loading. In Python, pandas will write the value as a string if it's stored as one in the JSON — but Excel may still reformat it. Format the column as Text in Excel, or use a custom number format @ to preserve the leading zeros.