How to Open a JSON File in Excel : From Messy Data to Clean Excel

Did You Know? Modern browsers (like Chrome and Firefox) automatically format and display .json files in a readable tree structure, making debugging much easier. This helps developers quickly inspect complex nested data without needing external tools or manually formatting the file.

JSON (JavaScript Object Notation) is a lightweight data format used to store and exchange structured information, especially in APIs and web applications. It organizes data hierarchically using key-value pairs, unlike Excel’s tabular format. 

Because of this difference, Excel cannot directly interpret JSON files, often displaying them as unstructured text. To work with JSON data in Excel, you need to import, transform, or convert it into a readable table format.

In this guide, you'll read more about how to open a JSON file in Excel, convert them into CSV, and fix common import issues.

Enhance your data science skills with upGrad’s online data science courses. You’ll find programs to fit your specific analysis needs, whether you’re managing business reports, scientific data, or everyday numbers.

Method 1: How to Open a JSON File in Excel Using Power Query (Recommended)

The best and most powerful method for opening JSON files is built directly into modern versions of Excel (Excel 2016, 2019, 2021, and Microsoft 365). It’s called Power Query (or "Get & Transform" in the Data tab).

This tool is specifically designed to import and reshape data from many sources, including JSON. It allows you to create a repeatable process that flattens your nested data into a clean, tabular format.

In 2025, professionals who can use data analysis tools to improve business operations will be in high demand. If you're looking to develop relevant data analytics skills, here are some top-rated courses to help you get there:

• Executive Post Graduate Certificate Programme in Data Science & AI
• Executive Diploma in Data Science & AI with IIIT-B
• Master’s Degree in Artificial Intelligence and Data Science

Step-by-Step Guide: How to Open JSON File in Excel

1. Open a Blank Workbook: Start in a clean Excel file.
2. Navigate to the Data Tab: At the top of the ribbon, click the Data tab.
3. Click 'Get Data': On the far left, click Get Data > From File > From JSON.
4. Select Your JSON File: Browse your computer, select the .json file you want to open, and click Import.
5. Enter the Power Query Editor: This is the most important part. Excel will not immediately show you a table. Instead, it will open a new window: the Power Query Editor. You will likely see a single word, such as "Record" or "List," which represents your JSON file.
6. Convert to Table: If your JSON file starts with an array [], you'll see a "List." Click the To Table button in the Transform tab at the top. A small dialog will appear; you can just click OK.
7. Expand the Records: Now you'll have a column (e.g., "Column1") filled with the word "Record." This represents each object in your JSON. To see the data inside, click the Expand Icon (it looks like two arrows pointing in opposite directions) in the column header.
8. Choose Your Columns: A dropdown menu will appear, showing all the keys from your JSON (e.g., "name," "age," "skills").
   • Pro-Tip: Uncheck the box that says "Use original column name as prefix." This prevents your columns from being named "Column1.name," "Column1.age," etc.
   • Click OK.
9. Expand Nested Lists (If Any): You'll now see your data! The "name" and "age" columns will be populated. However, the "skills" column will still say "List." This is because it was a nested array.
   • Click the Expand Icon on the "skills" column header.
   • This time, choose Expand to New Rows.
10. Close & Load: Your data is now perfectly flat! In the Home tab of the Power Query Editor, click the Close & Load button.

Power Query will close, and your data will be loaded into a new, perfectly formatted Excel table on your worksheet. 

Also Read: Power BI Tutorial: Learn from Experts

When to Use This Method

This is the preferred solution for how to open a json file in excel in most cases.

• Your JSON is structured but might be nested.
• Your file size is reasonable (e.g., under 100 MB).
• You want to keep an automated, refreshable link to the original file. If the data.json file changes, you can just go to Data > Refresh All to get the new data.

Method 2: Convert JSON to CSV and Open in Excel

CSV (Comma Separated Values) is a format that Excel understands perfectly. A CSV file is already a flat table of rows and columns. By converting your JSON to a CSV first, you bypass Excel's transformation challenges.

Why Convert JSON to CSV?

This process "flattens" the hierarchical data for you before it even gets to Excel. It's a great workaround if the Power Query steps for how to open json file in excel are failing or seem too complex for your specific file.

Conversion Steps

You cannot do this conversion in Excel itself. You must use an external tool.

1. Use an Online Converter: For small, non-sensitive files, you can use a free online tool (search for "JSON to CSV converter"). You simply paste your JSON code or upload your file, and it will provide a .csv file to download.
   • Warning: Do not upload sensitive or private company data to a public website.
2. Use a Python Script: For a more secure and powerful option, a simple Python script can do the conversion (see Method 3).
3. Open the CSV in Excel: Once you have your .csv file, opening it is easy:
   • Open Excel.
   • Go to File > Open.
   • Select the .csv file. Excel's Text Import Wizard may open to help you confirm the delimiter (usually a comma).
   • Once open, format the data as a table (Ctrl + T) for better filtering and sorting.

Also Read: 60 Advanced Excel Formulas – A Must Know For All Professionals

Method 3: Using Python or External Tools to Import JSON to Excel

For data professionals, developers, or anyone handling large or repetitive JSON imports, using a simple Python script is the most robust solution. The pandas library is legendary for its data-handling capabilities, including its json_normalize function, which is built to flatten complex JSON.

Sample Code

This code assumes you have Python and the pandas library installed (pip install pandas openpyxl).

Python
import pandas as pd
import json

# Define your file names
json_file_name = 'data.json'
excel_file_name = 'output.xlsx'

# Open and load the JSON file
with open(json_file_name, 'r') as f:
    data = json.load(f)

# This is the magic command to flatten nested JSON
# If your JSON is a list of objects, it will handle it.
df = pd.json_normalize(data)

# Save the flattened data to an Excel file
# index=False prevents pandas from adding an extra index column
df.to_excel(excel_file_name, index=False)

print(f"Successfully converted {json_file_name} to {excel_file_name}!")

Output (in your console):

Successfully converted data.json to output.xlsx!

This script will create a new Excel file named output.xlsx in the same folder. This file will contain your flattened data, with nested keys automatically turned into new columns (e.g., user.name, user.address.city).

Also Read: Pandas Cheat Sheet in Python for Data Science: Complete List for 2025

Benefits & Limitations

• Benefits: This method handles massive files (gigabytes) that would crash Excel. It gives you full control over automation and formatting.
• Limitations: It requires you to have Python set up and be comfortable running a basic script. It is not an "in-Excel" solution.

This table compares the direct import vs. the conversion method.

Method	Best For	Tools Used
Power Query	Direct import, refreshable data	Excel (built-in)
Convert to CSV	Very complex or malformed files	Online converter or Python script
Script Method	Large files, automation	Python (pandas)

Also Read: What Is Data Science? Courses, Basics, Frameworks & Careers

Pro-Tips: Preparing, Troubleshooting, and Optimizing Your Data

Simply getting the data into Excel is half the battle. Here’s how to open a json file in excel and ensure the data is clean, correct, and ready for analysis.

Before You Import: Preparing Your JSON File for Excel

Garbage in, garbage out. A broken JSON file won't import, no matter what method you use.

• Confirm .json Extension: Ensure your file is saved as .json, not .txt.
• Validate Syntax: A single missing comma or bracket will break the entire import. Use an online "JSON validator" to paste your code and check for errors.
• Remove Extra Spaces or Incomplete Entries: Ensure the file is properly "closed" (e.g., the last curly brace } or bracket ] is present).
• Check Encoding: Ensure the file is saved in UTF-8 format, which is the standard for JSON and well-understood by Excel.

Also Read: Data Validation in Excel

Troubleshooting Common Import Issues

If you're stuck, one of these common issues is likely the culprit.

1. Missing “From JSON” Option: If you go to Data > Get Data and don't see "From JSON," you are using an older version of Excel (like 2007, 2010, or 2013). You must either upgrade to Office 365/2016+ or find and install the "Power Query" add-in for your specific version.
2. JSON Shows as “List” or “Record”: This is not an error! This is a normal part of the Power Query process. It means Power Query has recognized the data structure. You must click the Expand icon (the two arrows) on the column header to drill down and see the data.
3. Excel Crashes with Large Files: The file is too big for your computer's RAM. Your only reliable option is Method 3: Using Python, which processes the file without loading it all into memory at once.
4. Missing Columns or Data: You likely forgot to expand a nested "Record." Go back into the Power Query Editor (click Query > Edit), find the column that still says "Record," and click the Expand icon to reveal the sub-fields.
5. Formatting Issues After Import: The data is in, but it looks ugly. Select your entire data range and press Ctrl + T to convert it into a formal Excel Table. This adds filtering, sorting, and better formatting automatically.

Also Read: How to Become a Data Scientist – Answer in 9 Easy Steps

Best Practices After You Import JSON to Excel

Your data is loaded. Now make it usable.

• Rename Columns: Power Query often creates long names like data.user.name. Rename these headers to something simple like "User Name."
• Check Data Types: In Power Query, you can right-click any column and select Change Type to ensure numbers are numbers (not text) and dates are dates.
• Use Pivot Tables: The whole point of getting data into Excel is to analyze it. Immediately insert a Pivot Table to summarize your newly imported data.
• Save as .xlsx: Your original file was a .json. Save your new workbook as an .xlsx file to preserve all your formatting and the data connection.
• Refresh Your Data: Remember, if you used Power Query, your data is linked. If the original JSON file is updated, just go to the Data tab and click Refresh All.

Also Read: Excel Worksheet for Practice

Advanced Tips: Importing from a Web URL (API)

What if your JSON isn't a file, but a live URL from an API? Power Query can handle this just as easily.

1. Go to the Data tab.
2. Click Get Data > From Other Sources > From Web.
3. Paste the API endpoint URL (e.g., https://api.example.com/data.json).
4. Click OK.
5. Excel will open the Power Query Editor, and you can follow the exact same steps (To Table, Expand Columns) as if it were a local file. This is an incredibly powerful way to import json to excel from live web sources.

Upskilling with upGrad to Reach Excel Mastery!

Who knew that learning how to open a JSON file in Excel could be this simple? With tools like Power Query and manual CSV conversion, converting JSON to Excel is easier than ever. Whether you're working with simple data or complex nested objects, Excel provides the flexibility and features needed to handle all kinds of JSON data effortlessly.

If you’re eager to become an Excel pro, why not take it a step further? upGrad’s exclusive course on Excel goes beyond the basics, equipping you with advanced data management techniques and practical skills that can boost your career opportunities in data analytics, finance, and beyond.

Here are some of the additional upGrad courses  (including free ones)  to support your Excel learning journey:

• Introduction to Data Analysis using Excel
• Analyzing Patterns in Data and Storytelling
• Basics of Inferential Statistics
• Introduction to Tableau
• Case Study using Tableau, Python and SQL

For personalized career guidance, contact upGrad’s counselors or visit a nearby upGrad career center. With expert support and an industry-focused curriculum, you'll be prepared to tackle data analysis challenges and advance your career.