Here are the general steps:
- Go to the Data tab in the Excel ribbon.
- Click on Get Data and then select From JSON.
- Choose the JSON file you want to parse and click Import.
- Excel will open the Query Editor window. Here, you can navigate through the JSON structure and apply transformations as needed. Click on the expand icon to select the attributes you want to include in your Excel sheet.
- Clicking Into Table you can transform your JSON file to a Table in Excel. This gives you many possibilities by unlocking Home, Transform and Add Column tabs. There are plenty of options available for you such at this point.
- Once you’re satisfied with the data, click Close & Load to import it into your Excel sheet.
Once you have the table, you can use Excel’s features to sort, filter, analyze, or visualize your data. For example, you can create a pivot table, a chart, or a conditional formatting rule based on your JSON data. You can also refresh your query to get the latest data from the JSON file.