The following post gives step-by-step instructions for how to open a JSON file in Excel. For information on JSON files continue reading or use the table of contents to skip to specific sections. 
Table of Contents
- What does JSON stand for and what are JSON files?
- What do JSON files look like?
- Why is it difficult to work with JSON files?
- How to open JSON files in Excel
- Conclusion
What does JSON stand for and what are JSON files?
JSON stands for JavaScript Object Notation, which is a data format based on a portion of the JavaScript programming language. JSON files are compatible with all programming languages and as a lightweight way to format and store data. JSON files end in the .json file extension are often used when transporting data within applications, which is why the JSON file format is common when data is exported from software applications. JSON files are a great format for storing hierarchical data, with multiple layers of categories and subcategories. This often leads to JSON files storing large amounts of data. You can read more about JSON files at JSON.org
What do JSON files look like?
JSON files all end in the .json file extension. Data in JSON files is written as name/value pairs in the following format: "data name":"data". An example might be "dogName":"Rover". A JSON file typically contains many of these pairs for the following data types:
- Strings:
{"name":"Brett"} - Numbers:
{"weight":"178"} - Objects:
{"player":{"name":"Brett", "age":"41", "school":"West Virginia"}} - Arrays:
{"players":["Mark", 41, "Brett"]} - Booleans:
{"active":true} - Null values:
{"injuries":null}
JSON files also typically store ordered lists of values containing all the key/value pairs. An example of a JSON file with fantasy football data is included here. As you can see, the format isn't too complicated but has distinct differences from file types like CSV and Parquet.
[{"player_id": 1809, "name": "Robert Blackmon", "position": "DB", "height"
: "6-0", "weight": "208", "current_team": null, "birth_date": "1967-05-12"
, "birth_place": "Bay City, TX", "death_date": null, "college": "Baylor",
"high_school": "Van Vleck, TX", "draft_team": "Seattle Seahawks",
"draft_round": "2", "draft_position": "34", "draft_year": "1990",
"current_salary": null, "hof_induction_year": null}, {"player_id": 23586,
"name": "Dean Wells", "position": "LB", "height": "6-3", "weight": "248",
"current_team": null, "birth_date": "1970-07-20", "birth_place": "Louisville,
KY", "death_date": null, "college": "Kentucky", "high_school": "Holy Cross, KY",
"draft_team": "Seattle Seahawks", "draft_round": "4", "draft_position": "85",
"draft_year": "1993", "current_salary": null, "hof_induction_year": null},
{"player_id": 355, "name": "Kiko Alonso", "position": "ILB", "height": "6-3",
"weight": "238", "current_team": "Miami Dolphins", "birth_date": "1990-08-14",
"birth_place": "Newton, MA", "death_date": null, "college": "Oregon",
"high_school": "Los Gatos, CA", "draft_team": "Buffalo Bills", "draft_round":
"2", "draft_position": "46", "draft_year": "2013", "current_salary": "1,075,000"
, "hof_induction_year": null},
Why is it difficult to work with JSON files?
There are two reasons JSON files are hard to work with, the format and the file size.
File Format
JSON files themselves are easy to work with when writing JavaScript but when a human exports data from a software application and saves a .json file on their computer, it's often difficult to analyze. JSON files often store hierarchical data, which doesn't easily map to a spreadsheet type viewer. When opened in a raw format, like a text editor, JSON files are not as easily human readable as CSV files and don't align data into columns, like we are used to seeing in spreadsheets. When it comes to spreadsheets, Excel and Google Sheets don't easily open .json files, making it difficult for folks without programming experience to open, analyze, or manipulate JSON files. If you are one of these people, continue reading out instructions to open JSON files in Excel, Google Sheets, and Row Zero.
File Size
Due to JSON files being produced by software applications, they can often grow quite large, which prevents popular spreadsheets from opening them. Excel has a 1,048,576 row limit and Google Sheets has a 10,000,000 cell limit. Because both applications use the compute power on your laptop, they are not able to open big JSON files and will often freeze or crash when trying to do so.
How to Open JSON Files in Excel
Excel is the most popular data analysis tool and nearly every business user has Excel on their computer. In this section we provide step-by-step instructions for opening a JSON file with Excel. If you try to open a JSON file in Excel like you would a normal CSV, Excel will likely go into a not responding state and will crash. To avoid this, begin with step 1 below. 
1. Get Data
Open an Excel file and click on Data in the top menu. Then select the Get Data. From the options in the drop down, select From File > From JSON. 
2. Select JSON File
Use the open menu to navigate to your file (look for the .json file extension), select the file, and click Import. 
3. Power Query Editor
Excel will then open the file using the Power Query Editor, which is a feature that creates a database out of the dataset. Excel will initially show you a view of your file like the image below. Don't panic, there are some additional steps needed before opening the file. 
Errors might occur at this step. JSON files can contain formats or additional meta-data that Excel has trouble handling. You can see two of the error messages below. If you encounter one of these, you may need to open the file in a notepad editor, modify the error prone data or formatting and retry this process. 
4. Into Table
Select Into Table in the upper left-hand corner. 
5. Expand to New Rows
Then click on the double arrow icon at the top of the second column and select 'Expand to new rows.' 
6. Deselect 'use original column name as prefix'
Then click the same double arrow again and deselect the 'use original column name as prefix.' Then click ok. 
7. Repeat Expand to New Rows
If your JSON file contains many subsets of data layered within the file, you may need to repeat steps 5 and 6 many times if you continue to see columns with the same values in brackets listed all the way down the column. See the images below for an example. 
8. Close and Load
Then finish by clicking 'Close and Load in the upper left-hand corner. 
9. Imported to Data Table
Excel will then load your data into Excel in a data table, which allows you to make column level transformations to the data.
Conclusion
Importing a JSON into Excel is not as straightforward as other file types, like CSV; however, it can be done. If you follow the steps outlined in this post, you can import a JSON file into an Excel data table where you can manipulate various columns. Excel is not great at handling non-standard JSON file types and you may get errors. Remember that Excel has a 1,048,576 row limit and even in the data table format in which Excel is able to open higher row counts, the program is memory intensive and may crash. If you are looking for a more powerful spreadsheet to handle giant datasets, check out Row Zero, a spreadsheet that can open billion row data sets.