How JSON becomes rows and columns
A sheet is a grid, so the converter has to decide what a row is. A list of objects at the top gives one row per object, and a list of lists is read as a grid whose first list holds the column names. A single object becomes one row. An object that holds a list of records, such as {"status": "ok", "data": {"items": [...]}}, is searched for that list. The keys outside it are left out of the sheet, and a note lists them. If you want a different part, give its path, for example data.items, in the Rows come from box under More options.
Not every record needs the same keys. The sheet gets one column for each key found anywhere in the list, in the order they first show up. Where a record has no value, the cell is empty, and so it is for a null.
Nested objects, lists and the 16,384 column limit
A cell holds one value, so nested data is spread out. {"id": 1, "address": {"city": "Lisbon"}, "tags": ["a", "b"]} gives the columns id, address.city, tags.0 and tags.1.
- Separate columns puts each list item in a cell of its own. Only this choice can be converted back into the same JSON.
- Joined into one cell writes a;b, which suits short lists of plain values.
- JSON text in one cell stores the list unchanged, as ["a","b"].
- With Flatten nested values into columns switched off, each nested object or list stays whole as JSON text, so every top level key is one column.
An Excel sheet has at most 16,384 columns. A record with a very long list, or records with thousands of different keys, can pass that limit with separate columns. The converter then refuses and says how many columns it needs, and the fix is to join the lists into one cell or keep them as JSON text.
What the cells in the workbook are
- A JSON number is an Excel number, and true and false are Excel booleans, shown as TRUE and FALSE. A number too large for a 64 bit number, such as 1e999, becomes Infinity, which Excel cannot hold, so it is written as text with a note.
- A JSON string is text, also when it looks like a number: "00123" keeps its zeros, and a long ID in quotes is not rounded. Only a JSON number can be an Excel number, and JSON numbers above 15 digits already lose digits when JSON is parsed, so quote long IDs at the source.
- A string that starts with = + - or @ is text. It is never run as a formula when the file is opened.
- A date such as "2024-01-15" is a string in JSON, so it stays text in Excel. Convert it there with Data, Text to Columns, or with a formula like
=DATEVALUE(A2). - Text longer than 32,767 characters, the most an Excel cell holds, is cut with a note.
The workbook has one sheet, called Sheet1 unless you choose a name (31 characters at most, and characters Excel does not allow, such as / and ?, become an underscore). The header row is bold and frozen, and column widths follow the longest value in the first 200 rows. Number formats, colors and formulas are not added.
Compared with opening JSON in Excel
Excel can import JSON itself with Data, Get Data, From JSON. That opens Power Query, where each nested record or list shows up as a cell you expand yourself, one level at a time. This converter does the flattening for you with fixed rules you can see and change, and it works in a browser tab without Excel installed.
If your JSON is large, the Excel limits apply: 1,048,576 rows including the header. In our tests on a laptop, 50,000 records of 10 fields became a workbook in about 1.2 seconds, and 200,000 records took about 7 seconds. A very large JSON file needs a lot of memory to parse, and phones and older computers will manage less.