How to Handle Nested JSON Data in Excel Spreadsheets

If you have ever tried to import a JSON API response into Excel, you have probably hit a wall: nested objects and arrays do not map cleanly to flat spreadsheet rows. A user record with an address object, a list of orders, and nested payment details can feel impossible to tabulate.

The good news is that there are well-established techniques for "flattening" nested JSON into tabular data. This article covers each approach, its trade-offs, and when to use it.

What Makes JSON "Nested"?

JSON supports two types of nesting:

• Nested objects — An object value contains another object (e.g., "address": {"city": "NYC"})
• Nested arrays — A value contains a list, potentially of objects (e.g., "orders": [{"id": 1}, {"id": 2}])

Excel spreadsheets are inherently flat — each row has the same set of columns. So any nested structure must be "unrolled" before it can fit into a table.

Technique 1: Dot Notation Flattening

The most common approach for nested objects is to create column headers using dot-separated paths. Each level of nesting adds another segment to the column name.

Example

Input JSON:

{
  "name": "Sarah Chen",
  "contact": {
    "email": "sarah@example.com",
    "phone": {
      "work": "+1-555-0100",
      "mobile": "+1-555-0101"
    }
  }
}

Flattened output:

When to Use Dot Notation

• Objects nested 2-4 levels deep
• Each nested object has a small, fixed number of keys
• The nesting represents a "has-one" relationship (user has one address)

Technique 2: Array Expansion with Indexing

When a JSON field contains an array of objects, dot notation alone is not enough. Each array element needs its own set of columns, differentiated by an index.

Example

Input JSON:

{
  "project": "Website Redesign",
  "team": [
    { "name": "Alice", "role": "Frontend" },
    { "name": "Bob", "role": "Backend" }
  ]
}

Flattened output:

Technique 3: Value Joining for Simple Arrays

When an array contains primitive values (strings or numbers rather than objects), the simplest approach is to join them into a single cell using a delimiter.

Example

Input:"tags": ["urgent", "frontend", "bug"]

Output cell:urgent; frontend; bug

The semicolon separator is preferred over commas because commas could conflict with CSV formatting or existing data values.

Technique 4: Row Explosion

Sometimes the most useful approach is to create one row per array element, duplicating the parent data. This is common in database normalization and works well when the nested array represents the primary data of interest.

Example

Input JSON:

{
  "customer": "Acme Corp",
  "orders": [
    { "id": "ORD-001", "total": 150 },
    { "id": "ORD-002", "total": 300 }
  ]
}

Exploded output:

Notice that "Acme Corp" appears in both rows. This denormalization makes analysis easier — you can use Excel pivot tables, SUMIF formulas, and filters directly on the data.

Choosing the Right Technique

Practical Tips for Clean Results

1. Flatten before converting — If you control the JSON source, consider flattening the structure before exporting. An API that returns {"city": "NYC"} at the top level is easier to convert than one that nests it three levels deep.
2. Set a maximum depth — For very deeply nested JSON (5+ levels), limit the flattening depth and serialize remaining nested data as a JSON string in the cell.
3. Handle null values explicitly — Decide whether null should appear as "null", an empty string, or "N/A" in your spreadsheet.
4. Consistent array lengths help — When possible, pad arrays to the same length across records to avoid sparse columns.
5. Test with real data — Edge cases always appear in production data. Test your conversion with actual records, not just synthetic examples.