If you want to learn how to open a JSON file in Microsoft Excel, follow the methods outlined in this effortless guide.

JSON files use the nested format to store structured data. Though machines can easily read such files, it becomes challenging for humans to make any sense of them. Now, many business applications, APIs, and databases generate JSON data. extracting meaningful insights from such a file is challenging if you don’t have the right tools. Thankfully, Microsoft Excel is the preferred tool in many cases when it comes to opening and accessing JSON files. Let me introduce you to some of the effortless techniques below.

Using Text to Columns

You can use Excel’s Open command to open any JSON file in an Excel worksheet and transform it into a table using the Text to Columns tool. Below, I will show you the steps using real images.

Open the Excel desktop app and click on the File menu in the Excel ribbon.

Click Open in the left-side navigation panel and select Browse from the right-side menu.

The Open dialog box will show up. Use the Windows File System tool in the Open dialog to import the JSON file.

If it doesn’t show the file instantly, click on the All Excel Files drop-down menu near the File name field and choose the All Files option from the context menu.

You should now see the JSON file when you visit the appropriate directory on your PC. Select it and hit the Open button in the file browser dialog box.

The contents of the JSON file will show up in the active worksheet, from which you’ve initiated opening the JSON file.

The worksheet should show a dataset as shown above.

You can delete the redundant curly brackets from the newly created dataset.

Add a blank row above to create a header row for the structured dataset.

Now, select the remaining dataset and click on the Text to Columns command in the Data Tools block of the Data tab.

You’ll see the Convert Text to Columns Wizard. There, you need to select the Delimited option. Click Next.

In the dialog box that follows, select an appropriate symbol under the Delimiters column. If the necessary symbol isn’t available in the default list, checkmark the Other checkbox and type in the character used as the delimiter in the input dataset. Click Next.

Click Finish on the next screen to finalize the process.

This step will separate the values field of the JSON data into a separate column.

Now, click on the top row in the adjacent cell of the newly created data column and enter the following formula into it:

 =TRANSPOSE(A2:A4)

The cell range used is according to the keys of the JSON input dataset. For example, Product, Price, and Units are the keys of the source JSON file. In the above formula, customize the cell range reference according to your dataset.

Hit Enter to convert the key entries into column headers.

Select the next row down the newly created header columns and enter the following formula into the cell:

=TRANSPOSE(B2:B4)

Don’t forget to customize the cell ranges in the above formula according to the JSON values column.

Hit Enter to calculate the cell and transpose data from columns to rows.

Copy the row you created and paste it down the column by matching the keys in the left side column or according to the column headers on the top.

Hide all unnecessary columns.

Select the rest of the dataset, and press Ctrl + C to copy it. Select another destination and press Ctrl + Alt + V to bring up the Paste Special dialog box.

Click on the Values option in the Paste column and click OK.

You can now hide or delete all columns except the newly pasted dataset.

Delete the blank rows in this data table to complete restructuring data inserted from a JSON file.

This method is only suitable for a small dataset you can edit manually.

Using the From JSON Command

If you wish to automate the process and try out the Power Query tool, you can use the From JSON command in the Data tab to import and open a JSON file in Excel. Let me walk you through the entire process step-by-step.

Go to the destination worksheet and click on the Data tab.

Find and click on the Get Data command in the Get & Transform Data block.

Hover the cursor over the From File menu and choose From JSON in the overflow menu that opens to the right of the existing context menu.

The Import Data dialog box will open. Use it to browse the PC storage or network storage to locate the intended JSON file. Select it, and hit the Import button.

If you see the Unable to Connect dialog boxes, try fixing the JSON file format and try to upload it again using the steps mentioned so far.

If it doesn’t work at all and you know that the JSON file has a standard structure, click on the Edit button in the Unable to Connect pop-up.

Click on the Open file as drop-down menu and select CSV Document from the list that drops down. Click OK to export the JSON file’s content as a CSV file into the File Preview wizard.

Select an appropriate symbol as the delimiter from the Delimiter drop-down menu.

All of the content of the JSON file should show in one column. If the column looks good, click on the Transform Data button in the bottom right corner.

Your JSON dataset will open in the Power Query Editor interface.

If the input JSON file is in its standard structure, it should open directly in the Power Query Editor.

Once your data is in the Power Query app interface, the way to transform data and export that to an Excel worksheet becomes pretty easy.

Click on the column header, right-click, and select To Table from the context menu.

On the To Table dialog box, select None as the delimiter. Click OK to open the JSON dataset in Power Query.

Now, depending on the number of columns you want to extract from the given JSON file, you can add as many columns as you like in Power Query.

Right-click on the column header and choose Duplicate Column. Repeat the process as many times as needed.

Click on the expansion icon in the top right corner of the first column.

Unselect everything from the filter menu and select the one you want to keep in the first column, say Product.

Similarly, click on the column filter icon and choose another column header, like Price and Units.

You’ll get a tabulated dataset.

Click on the File menu and choose Close & Load To from the context menu.

The Import Data dialog box will show up. Click on the Existing worksheet option and select a cell in the active worksheet to import the table from Power Query. Click OK to confirm.

That’s it! You’ve successfully opened and extracted the JSON dataset to an Excel worksheet.

Using Online JSON to Excel Converters

You can use any JSON to XLSX or CSV converters to transform a JSON file into an Excel worksheet or a CSV file. If you convert the JSON file into a CSV file, go through the article below to convert the CSV file to an Excel worksheet:

📒 Read More: 8 Easy Ways To Convert CSV to Excel

Visit Convert JSON to CSV website and click on the Upload JSON File button to attach the input file for processing.

Once the processing is complete, click on the Excel File (XLSX) button to get a copy of the uploaded JSON file in an Excel spreadsheet format.

Go to the Downloads folder and open the downloaded XLSX file to access your JSON file in an Excel worksheet format.

This method is only suitable for you if the JSON file you’re uploading doesn’t contain sensitive information. If your JSON input file contains sensitive data, use the other two methods explained earlier in this Microsoft Excel guide.

Conclusions

You have learned three different ways to open a JSON file in Microsoft Excel. You can use the method you like the most and that suits your data analysis style on Microsoft Excel.

If the Excel guide helped you learn a new skill, use the comment box to share your acknowledgment. If you’ve got feedback or suggestions, you can also use the comment box.