How to How to Import JSON Data in Excel in Excel - Practical Guide

Learn how to import JSON data directly into Excel to automate data workflows and eliminate manual entry. This tutorial covers native import methods and Power Query solutions, enabling you to work with modern API data and structured files seamlessly.

Why This Matters

JSON is the standard format for web APIs and modern data exchanges; importing it directly saves time and reduces errors compared to manual copying.

Prerequisites

•Excel 2016 or later (Excel 365 recommended)
•Basic understanding of JSON structure
•Access to JSON file or URL

Step-by-Step Instructions

Open Power Query Editor

Click Data > Get & Transform Data > Get Data > From File > From JSON (Excel 365) or use Data > New Query > From File > JSON for older versions.

Select Your JSON Source

Browse to your JSON file or paste the file path/URL, then click Open to preview the data structure.

Convert to Table Format

In Power Query, click the expand icon next to array columns to flatten nested JSON objects into rows and columns.

Adjust Column Data Types

Review each column header, right-click and select Change Type (e.g., Text, Number, Date) to ensure proper formatting.

Load Data into Excel

Click Home > Close & Load to import the transformed data into a new worksheet as an Excel table.

Alternative Methods

Manual Method: Copy-Paste from Browser

Open JSON file in a web browser, copy the data, and use Paste Special with Text-to-Columns wizard to parse it manually. Suitable for small files only.

Third-Party Tools: Online Converters

Use online JSON-to-Excel converters (like CloudConvert or Convertio), download the .xlsx file, and open in Excel. Faster for one-time conversions but less secure for sensitive data.

Power BI Desktop Method

Import JSON via Power BI Desktop and export as Excel file, offering advanced transformation options for complex nested structures.

Tips & Tricks

✓Always validate your JSON file syntax using JSONLint (jsonlint.com) before importing to avoid parser errors.
✓Use Power Query's filter options to exclude unnecessary columns and reduce file size before loading.
✓Keep the source JSON file updated and refresh the Excel query periodically for live data sync.
✓Create a separate staging sheet for imported data before performing calculations or pivot table analysis.

Pro Tips

★Use M language in Power Query Advanced Editor to write custom transformations for highly nested JSON structures without manual expansion.
★Create a Power Query parameter to make your JSON import reusable by allowing URL/file path swaps without recreating the query.
★Enable Data Refresh on Open (File > Options > Data) to automatically pull fresh JSON data each time you open the workbook.
★Combine JSON imports with Excel's Data Model to create relationships between multiple imported datasets for advanced analysis.

Troubleshooting

Power Query shows encrypted or unreadable data after import

This occurs with binary JSON or corrupted files. Re-download the JSON file from the source and ensure it's in plain text format (.json). Check file encoding in Notepad++.

Import takes too long or Excel freezes

Your JSON file is too large; split it into smaller chunks or use Power Query's filter options to import only needed fields. Consider using Power BI for very large datasets.

Nested objects aren't flattening properly

Manually expand each nested column by clicking the expand icon in Power Query, or use the M language to write a custom recursive expansion formula.

Refresh fails after moving or renaming source file

Edit the query (Data > Edit Queries) and update the file path, or use a cloud-based JSON URL to prevent path-related refresh failures.

Related Excel Formulas

FILTERXML TEXTJOIN TRANSPOSE

Frequently Asked Questions

Can I import JSON directly without Power Query?

No, Excel requires Power Query or manual conversion for JSON import. Excel 365 has the easiest native integration. Older versions need the Power Query add-in (free download from Microsoft).

Will imported JSON data automatically refresh?

Yes, if you load from a file or URL via Power Query, you can refresh by clicking Data > Refresh All. Set up automatic refresh intervals in Data > Query Options if needed.

What's the file size limit for JSON imports?

Excel can handle JSON files up to ~1GB, but performance degrades significantly above 100MB. For larger files, use Power BI or split the JSON into smaller chunks.

How do I handle JSON arrays vs. objects during import?

Power Query automatically detects arrays (imported as rows) and objects (imported as columns). If columns are missing, manually expand array columns using the expand icon in the preview.

Can I merge multiple JSON imports into one Excel table?

Yes, create separate queries for each JSON file, then use Append in Power Query's Home tab to combine them into a single table before loading.