JSON Arrays to Excel: Export Nested Records as Related Worksheets

The Core Problem

A worksheet is a rectangle. JSON is a tree. Converting one to the other means answering a question that has no universally correct answer: what should a branch become?

There are only two kinds of branches in JSON, and they behave completely differently.

An object is a fixed set of named fields. {"city": "Berlin", "zip": "10115"} always has exactly those two keys for that record. A fixed set of names maps cleanly onto a fixed set of columns.

An array is an unknown number of items. ["pro", "beta"] has two items in this record and might have nine in the next. There is no fixed column count that fits it.

That distinction drives everything else in this guide. Objects flatten. Arrays do not.

How Objects Become Columns

Flattening walks the tree and collects a path for every leaf value:

{
  "id": 1024,
  "name": "Alice Chen",
  "address": {
    "city": "Berlin",
    "zip": "10115"
  }
}

Paths found: id, name, address.city, address.zip. Each becomes one column.

The header is where tools differ. Using the full dot path is precise but hostile to read:

Using the last segment is readable, and it is what most spreadsheets actually want:

The last-segment approach also normalizes the name: underscores and hyphens become spaces, camelCase is split into words, and the first letter is capitalized. So created_at becomes Created at, and orderId becomes Order Id.

The full path does not disappear — it stays as the column's source path, which is what you edit when you want a different value in that column. You get the readable header and the precise origin at the same time.

The One Case Where This Bites

Two different branches can end in the same word:

{
  "user": { "name": "Alice" },
  "company": { "name": "Northwind" }
}

Paths: user.name and company.name. Both headers: Name.

The columns hold the right values and the source paths are still distinct, but a spreadsheet with two identically named columns will break lookups and confuse anyone who opens it. Rename one header before exporting. This is worth checking every time you convert an unfamiliar structure — it is the single most common flattening surprise.

Depth and Column Explosion

Flattening is lossless for objects, but width grows fast. A record with four nested objects of five fields each produces twenty columns before you count the top-level fields.

Excel caps a worksheet at 16,384 columns, which sounds generous until you convert deeply nested configuration data. Readability fails much earlier. If flattening produces more than a few dozen columns, that is a signal: the JSON is describing several different things at once, and it should probably become several worksheets rather than one very wide table.

How Arrays Become Cells

Arrays of primitives — strings, numbers, booleans — have three reasonable representations in a single cell. Using tags: ["pro", "beta"] as the example:

Edge cases are worth knowing before you pick one. An empty array [] becomes the text [] when keeping JSON, an empty cell when joining, and the number 0 when counting. Only the counting mode gives you a value you can do arithmetic on.

Joining is the mode that most often disappoints, because it looks right until the array contains objects rather than labels:

{"sku":"A-1","qty":2}, {"sku":"B-7","qty":1}

That is a correct join. It is also unusable. Which brings us to the actual solution.

How Arrays of Objects Become a Second Worksheet

An array of objects is a one-to-many relationship. In relational terms it is a child table, and the right spreadsheet representation is a second worksheet — not a crowded cell.

Take two customers with orders:

{
  "data": [
    {
      "id": 1024,
      "name": "Alice Chen",
      "orders": [
        { "sku": "A-1", "qty": 2 },
        { "sku": "B-7", "qty": 1 }
      ]
    },
    {
      "id": 1025,
      "name": "Bob Miller",
      "orders": [{ "sku": "C-3", "qty": 5 }]
    }
  ]
}

The main sheet holds one row per customer. The orders array additionally becomes a worksheet named after its path:

Sheet: Orders

Three columns exist purely to preserve the relationship that the tree structure used to encode:

• Parent row — which row of the main sheet this item came from.
• Item index — the item's position inside its array, so the original order survives.
• Parent id / Parent name — identifying fields copied down from the parent record.

Those identity columns are picked up from recognizable field names on the parent: id, uuid, uid, key, code, name, email, orderId, order_id, createdAt, created_at. Whichever of those exist get copied into the child rows.

With that sheet, SUM(Qty) per customer is a two-minute pivot table. With the same data crammed into one cell as JSON text, it is a manual re-entry job.

Try it directly: our JSON to Excel converter does this in the browser. Turn on nested array sheet export, and every array of objects in your JSON becomes its own worksheet with the parent columns already filled in — no upload, no signup.

Values That Need Special Handling

Flattening decides shape. A few individual values also need attention, because the spreadsheet will otherwise change them.

null becomes an empty cell, not the text null. This matters for aggregation: AVERAGE ignores empty cells but would happily include a zero, so representing missing data as empty is the difference between a correct average and a wrong one.

Booleans stay boolean, displaying as TRUE and FALSE. If a converter writes them as the strings "true" and "false", filters and conditional formatting stop working.

Numbers stay numeric, including decimals like 91.5.

Integers of 16 digits or more are written as text on purpose. Beyond roughly 15–16 significant digits, neither a JavaScript number nor Excel's display can hold an integer exactly, so 9007199254740993123 would come back as 9007199254740993000. Writing it as text keeps every digit. Shorter numbers stay numeric, so ordinary arithmetic is unaffected.

That last one is worth checking on any dataset containing order IDs, snowflake IDs, or account numbers. The corruption is silent — no error, no warning, just wrong digits at the end.

Limits Worth Knowing Before You Start

The cell character limit is the one nested JSON actually hits. Keeping a large array as raw JSON text in a single cell can exceed 32,767 characters, at which point the value is truncated. If you have big arrays, export them to a sheet rather than a cell.

Browser-based conversion is additionally bounded by available memory, which depends on the device and browser and cannot be quoted as a single number. Large datasets also trigger a practical warning threshold well before the hard limits — around 50,000 rows or 500 columns, export starts to take noticeable time.

Deciding What Your Structure Needs

Run through this in order:

1. Where is the array of records? Everything else is metadata. Set the root path to it.
2. Do any fields hold nested objects? They will flatten automatically. Check for duplicate headers afterwards.
3. Do any fields hold arrays of primitives? Pick keep-JSON, join, or count based on who reads the file.
4. Do any fields hold arrays of objects? Export those to their own worksheets.
5. Are there IDs longer than 15 digits? Confirm they came through as text with every digit intact.
6. How wide is the result? More than a few dozen columns usually means the data should be split.

Doing It in Python

For repeatable pipelines, pandas covers the same ground:

import json
import pandas as pd

with open("response.json", encoding="utf-8") as f:
    payload = json.load(f)

# Objects flatten into dot-notation columns
main = pd.json_normalize(payload["data"], sep=".")

# Arrays of objects become their own frame, carrying parent identity
orders = pd.json_normalize(
    payload["data"],
    record_path="orders",
    meta=["id", "name"],
    meta_prefix="parent_",
)

with pd.ExcelWriter("output.xlsx") as writer:
    main.drop(columns=["orders"]).to_excel(writer, sheet_name="Customers", index=False)
    orders.to_excel(writer, sheet_name="Orders", index=False)

Two differences from the browser tool worth noting. json_normalize keeps the full dot path as the column name, so you get address.city rather than City — rename with df.rename(columns=...) if a person will read the file. And pandas reads long integers as floats and rounds them, so pass dtype=str for ID columns or convert them before writing.

Frequently Asked Questions

How do I export a JSON array of objects to Excel?

Give it its own worksheet. An array of objects is a one-to-many relationship, and the correct spreadsheet representation is a second table with one row per item plus columns that identify which parent row each item belongs to. Squeezing it into a single cell as JSON text or joined values technically works but produces something nobody can filter or pivot.

What are the differences between the array export modes?

Keeping the raw JSON text loses nothing and suits a spreadsheet that another program will parse again. Joining produces readable text such as pro, beta and suits arrays of simple labels read by a person. Counting replaces the array with the number of items, which is the only mode that yields a value you can sum or sort. Empty arrays behave differently in each mode: they become the text brackets, an empty cell, and zero respectively.

What happens to nested objects in the same record?

They flatten into separate columns, which is lossless. Each leaf value keeps its own path, so a value at address.city becomes one column. The default header is the humanized last segment of that path, so address.city produces a header reading City, while the full dot path stays available as the column's source path.

What if two nested fields have the same name?

You get two columns with the same header. Because default headers use the last segment of the path, user.name and company.name both produce a column headed Name. The full source paths remain distinct and visible in the mapping panel, so rename one of the headers before sharing the file. Duplicate headers break VLOOKUP and pivot tables in confusing ways.

What are Excel's row, column, and cell limits?

A single worksheet holds at most 1,048,576 rows and 16,384 columns, and one cell holds at most 32,767 characters. The cell limit is the one that arrays actually hit, because a large array kept as raw JSON text in a single cell can exceed it and be truncated. Column count also grows fast with nesting depth, and a sheet stops being readable well before the 16,384 ceiling.

How are exported array rows linked back to their parent records?

Through identifier columns written into the child worksheet. Each child row carries the parent's row number and its position within the array, plus any recognizable identifying fields from the parent record such as id, name, email, or an order ID. Those columns are what let you rebuild the relationship with a lookup or a pivot after export.

Related Reading

• How to Convert JSON to Excel — the full workflow, and when to choose an online tool over Power Query or Python.
• JSON to CSV Conversion Guide — why CSV cannot represent multiple sheets, and what that costs with nested data.

Summary

Objects flatten into columns and lose nothing. Arrays are the real decision: primitives can live in a cell as JSON, joined text, or a count, but arrays of objects belong in their own worksheet with identifier columns linking back to the parent row. Check for duplicate headers, verify long IDs survived as text, and if the result is dozens of columns wide, the data is telling you it wants to be more than one table.

Open the JSON to Excel converter → — per-array export modes and nested array worksheets, all in your browser.