Two people convert the same JSON file to Excel. One gets a spreadsheet with 500 rows. The other gets 2,400. Neither tool is broken — they answered different questions.
The difference comes from a single checkbox that most converters label something like "flatten", with no explanation of what it changes. Tick it and every array item gets its own row. Leave it and every record stays on one row, with arrays squeezed into single cells.
Getting this wrong is the reason so many JSON exports end up unusable in Excel — the data is all there, just in a shape that will not pivot, will not sum, and will not filter the way you need. This guide shows both layouts on the same input so you can pick deliberately.
The Choice Nobody Explains
JSON nests. A spreadsheet does not. Every converter has to resolve that, and where it gets interesting is arrays — because an array has no fixed size, so there is no obvious number of columns to reserve for it.
There are exactly two sane answers:
- Keep the record intact. One row per record, and the array is collapsed into a single cell.
- Give each item a row. The record repeats once per array item, with the parent fields copied down.
The second is what databases call denormalisation — the same thing a SQL CROSS APPLY or a Power Query expand step does to a nested list. Neither answer is more correct; they suit different work.
One Row per Record
Take a single record with a nested object and an array:
{
"name": "John",
"skills": ["JS", "Python"],
"address": { "city": "NY", "zip": "10001" }
}With flattening off, that becomes exactly one row. The nested object spreads into its own columns, and the array is joined into one cell:
| name | skills | address_city | address_zip |
|---|---|---|---|
| John | JS; Python | NY | 10001 |
Your row count matches your record count, which makes the sheet easy to reconcile against the source. The cost is that JS; Python is now plain text. You cannot filter for "everyone who knows Python" without splitting that cell first.
It gets more noticeable with an array of objects. Two orders under one customer collapse into parallel lists:
| customer | orders_id | orders_item |
|---|---|---|
| Acme | 1; 2 | pen; book |
Readable, but not analysable. You cannot total the orders or count them per customer while they sit inside a cell as text.
One Row per Array Item
Turn flattening on and the same record produces one row per array item, with everything else repeated:
| name | address_city | address_zip | skills |
|---|---|---|---|
| John | NY | 10001 | JS |
| John | NY | 10001 | Python |
And the orders example becomes one row per order — the shape Excel actually wants:
| customer | orders_id | orders_item |
|---|---|---|
| Acme | 1 | pen |
| Acme | 2 | book |
Now every order is a row you can count, filter, and pivot on. The trade-off is that the customer name is duplicated, and your row count no longer matches your record count.
Which One Should You Pick?
Ask what the spreadsheet is for. The answer falls out immediately.
| You want to… | Layout | Why |
|---|---|---|
| Pivot, sum, or count array items | One row per item | Pivot tables need one fact per row |
| Check the export against the source | One row per record | Row count matches record count |
| Hand the file to a non-technical reader | One row per record | No confusing repeated rows |
| Filter on a value inside an array | One row per item | Values in a joined cell cannot be filtered |
| Import into a database or BI tool | One row per item | Matches how relational tables expect rows |
A useful rule: if the thing you want to count lives inside an array, it needs its own row. If you only care about the parent records, keep them whole.
What Lands in the Cells
A real .xlsx file is not a CSV with a different extension — every cell carries a type. That matters more than it sounds, because a number stored as text will not sum, will not sort numerically, and shows the little green warning triangle in the corner.
Here is how each JSON type arrives:
- Numbers stay numbers.
1250.5becomes a numeric cell, soSUMand numeric sorting work straight away. - Numeric strings stay text.
"007"was a string in your JSON and remains one, which is what preserves the leading zero. Product codes and zip codes survive. - Booleans become the words
trueandfalse. - Nulls and empty arrays become empty cells.
- Column widths are sized to the content, so you are not dragging borders before you can read anything.
That distinction between 1250.5 and "007" is the whole reason to export Excel rather than CSV. A CSV has no types at all — everything is text and Excel guesses on import, which is where leading zeros die and long numbers turn into scientific notation.
Converting JSON to Excel
- Open the JSON to Excel converter.
- Add your JSON — paste it in, or upload the file.
- Set the flatten option using the table above. On for one row per array item, off to keep each record on a single row.
- Convert, then download the
.xlsx. The success message reports the row count, and tells you how many duplicate rows were removed during expansion. - Open it and check one thing first: does the row count match what you expected? If it is higher than your record count, an array was expanded — which is fine if that is what you chose.
The whole conversion runs in your browser, so a customer export never leaves your machine.