How to Import JSON File to Excel: A Complete Guide

Sharing is caring!

Working with JSON data has become increasingly common as applications and APIs use it to exchange structured data. If you’re handling information from a web API or software system and need to analyze it in Microsoft Excel, you’ll want to know how to import JSON file to Excel quickly and accurately.

In this guide, we will explain multiple methods to bring JSON format data into Excel, whether you’re using Excel 2016, Excel 2019, Excel 2021, or Microsoft 365. It includes step-by-step instructions, useful tips, and essential tools like Power Query to help make the process easy.

Why Import JSON into Excel?

Excel spreadsheets are excellent for data manipulation, visualization, and reporting. Importing a JSON file into Excel allows you to:

• Filter and sort large data sets.
• Create pivot tables and charts.
• Use Excel formulas for deeper analysis.
• Share data insights in a familiar format.

Whether you’re analyzing API data, managing business reports, or converting web-based content, knowing how to convert JSON to Excel helps streamline your workflow.

Method 1: Import JSON Using Power Query (Excel 2016 and later)

Power Query is the most powerful and convenient way to import and transform JSON files in Excel.

Step-by-Step Guide

1. Open Excel and go to a new worksheet.
2. Click on the Data tab in the ribbon.
3. Select Get Data > From File > From JSON.
4. Browse and select your JSON file from your computer.
5. Excel will load Power Query Editor and preview the JSON structure.
6. Click the small icon next to each record to expand the nested data.
7. Keep expanding until all relevant fields appear in table form.
8. Once done, click Close & Load to import the data into Excel.

Example Table Output

The table shown above reflects how Power Query transforms nested JSON objects into rows and columns.

Method 2: Import JSON from a URL Using Power Query

If your JSON data is hosted online (like a public API), you can import it directly using a web URL.

1. Go to the Data tab.
2. Click Get Data > From Other Sources > From Web.
3. Paste the URL pointing to the JSON data (e.g., https://api.example.com/data.json).
4. Power Query will recognize the JSON structure and open it in the editor.
5. Follow the same steps to expand the records and load the data.

This is useful when dealing with real-time or frequently updated online data.

Method 3: Convert JSON to CSV and Open in Excel

If you don’t want to use Power Query or you’re using an older version of Excel, you can convert the JSON to CSV using online tools or scripts, then open the CSV in Excel.

Tools You Can Use

Steps

1. Open any of the online converter tools.
2. Copy and paste your JSON data into the tool.
3. Download the resulting CSV file.
4. Open Excel and click File > Open or drag the CSV file into Excel.
5. Your data will now appear in tabular format.

While this method lacks dynamic updates, it’s useful for one-time conversions.

Method 4: Import JSON Using VBA (Advanced Users)

If you’re comfortable using Excel VBA (Visual Basic for Applications), you can write a macro to read and parse JSON files.

Pros

• Useful for automation.
• Ideal for repetitive imports.
• Good for integrating into existing Excel macros.

Sample VBA Code

You’ll need to reference the Microsoft Scripting Runtime and use a JSON parser like JsonConverter.bas.

Here’s a basic outline of how it works:

Dim json As Object
Set json = JsonConverter.ParseJson(jsonText)

After parsing, you can loop through the JSON object and write values to Excel cells.

This method is best suited for users with programming experience.

Common JSON Structures and How Excel Handles Them

Excel handles different JSON formats differently. Here are a few common examples:

1. Flat JSON

{
  "name": "Emma",
  "age": 40,
  "location": "Chicago"
}

2. Nested JSON Array

{
  "products": [
    {"id": 1, "name": "Phone"},
    {"id": 2, "name": "Laptop"}
  ]
}

After expanding:

Power Query makes it easy to flatten nested arrays and turn them into a structured table.

Best Practices When Importing JSON to Excel

• Validate your JSON before importing. Use tools like JSONLint to check for errors.
• Use Power Query transformations like Remove Columns, Rename Columns, and Change Type for clean results.
• For frequent imports, use Queries & Connections to refresh the data easily.
• Save your Excel file as a macro-enabled workbook if using VBA.
• Avoid editing raw JSON directly in Excel—always use proper import methods.

When to Use Each Method

Troubleshooting Tips

If your data is not loading correctly:

• Check for nested levels in JSON. Use the expand icon in Power Query.
• If some fields are missing, expand all records properly in the editor.
• For Excel 2013 or earlier, Power Query needs to be installed separately.
• If you’re getting an error like “We can’t convert the data,” the JSON structure may be too complex or malformed.

Final Thoughts

Knowing how to import JSON file to Excel is essential when working with modern data. Whether you’re accessing data from APIs, converting system logs, or organizing structured records, Excel offers powerful tools like Power Query and VBA to get the job done.

Use Power Query for most situations—it’s built into Excel 2016 and later, easy to use, and very flexible. For simpler cases, converting to CSV works too. If you’re tech-savvy, writing VBA scripts gives you full control and automation capability.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.