How to Open JSON File in Excel

What this does

JSON stores records as objects with named fields, while a worksheet stores them as rows under header cells. Converting between the two means deciding which row holds the field names and how empty cells should appear (empty string vs. missing key). Excel itself has no one-click "save as JSON"; Power Query imports JSON, but exporting requires a tool or script. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “open json file in excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the outcome you expected before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a data step that keeps your analysis trustworthy, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

A sheet has headers name and qty in row 1, with Bolt M8 / 42 in row 2 and Nut M8 / 120 in row 3. Converted with the first row as headers, the output is [{"name":"Bolt M8","qty":"42"},{"name":"Nut M8","qty":"120"}] — two records, keys taken from row 1. APIs, config files, and most web tooling speak JSON. Converting a worksheet to JSON lets spreadsheet data feed scripts and apps without manual re-typing. A practical tip: try it on a copy of the sheet or a handful of sample rows first and check the result before you apply it to the real data. That one habit catches almost every surprise while it is still cheap to reverse.

In Google Sheets

This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “open json file in excel” as a routine rather than a one-off. Once you know which setting or command controls it, the same few steps handle every workbook where it comes up in Excel, and you can explain them to a colleague in a sentence.

Common mistakes

• Merged header cells, which leave some columns without a key.
• Expecting Excel's Save As dialog to offer JSON — it does not; you need a converter or script.
• Forgetting that all cell values arrive as text; cast numbers in the consuming code.
• Using a sheet where the real headers start in row 3 — move them to row 1 first.

Frequently asked questions