How to reshape json data for excel pivot table analysis
- Step 1Check your JSON's current shape — If it is an object of parallel arrays (columnar), you will convert to records. If it is already an array of record objects, you can skip straight to CSV conversion.
- Step 2
- Step 3Drop the JSON file — Drag your
.jsononto the dropzone. Provide a single top-level structure — a columnar object or a record array. - Step 4Choose Columnar → array — Select Columnar → array (
pivot-to-records) to produce the row-per-record array Excel needs. (Use Array → columnar only if a target wants columns.) - Step 5Transpose and review — Click Transpose. Confirm every record has all expected fields — short source columns appear as trailing
nullvalues, which become blank cells in Excel. - Step 6Convert to CSV and pivot — Download
<name>.transposed.json, run it through json-to-csv, open the CSV in Excel, then Insert → PivotTable on the range.
From JSON to an Excel pivot — the real pipeline
The JSON Transposer handles the orientation step only. CSV conversion and the pivot itself happen in sibling tools and Excel. There is no direct .xlsx export here.
| Step | Tool | Action | Output |
|---|---|---|---|
| 1. Flatten (if nested) | json-flattener | Nested objects → dot-notation keys | Flat record array |
| 2. Reshape orientation | json-transposer | Columnar → array for row-per-record | Array of records |
| 3. To spreadsheet | json-to-csv | Records → CSV rows + header | CSV file |
| 4. Pivot | Excel | Insert → PivotTable on the range | Pivot table |
Why orientation matters for a pivot range
Excel pivots need a record-per-row range. A columnar JSON object does not map to that — converting it first is what makes the CSV rectangular.
| Source JSON | Direct to CSV? | After Columnar → array | Pivot-ready? |
|---|---|---|---|
{ "id":[1,2], "v":[9,8] } | Awkward / one column of arrays | [{id:1,v:9},{id:2,v:8}] | Yes — clean range |
[{ "id":1,"v":9 }, ...] | Yes — already records | (no reshape needed) | Yes |
| Records with missing keys | Ragged columns | Round-trip to normalize then null-fill | Yes, with blanks |
| Records with nested objects | Struct cells / [object Object] | Flatten first, then reshape | Yes, after flatten |
Cookbook
Before/after JSON for Excel pivot prep. The transpose output here is the JSON you then convert to CSV; indentation is the tool's 2-space default.
Columnar API response → record array
ExampleAn API that returns parallel arrays isn't pivot-ready. Columnar → array produces records that convert to a clean Excel range.
Input (api.json):
{
"month": ["Jan", "Feb", "Mar"],
"revenue": [1200, 1500, 980],
"region": ["NA", "NA", "EU"]
}
Mode: Columnar → array
Output:
[
{ "month": "Jan", "revenue": 1200, "region": "NA" },
{ "month": "Feb", "revenue": 1500, "region": "NA" },
{ "month": "Mar", "revenue": 980, "region": "EU" }
]
// Next: json-to-csv → open in Excel → Insert PivotTableShort column becomes blank cells
ExampleA missing tail value fills with null, which Excel renders as an empty cell — the range stays rectangular for the pivot.
Input:
{
"id": [1, 2, 3],
"owner": ["Ana", "Ben"]
}
Mode: Columnar → array
Output:
[
{ "id": 1, "owner": "Ana" },
{ "id": 2, "owner": "Ben" },
{ "id": 3, "owner": null }
]Flatten nested fields first
ExampleExcel can't pivot on a nested object cell. Flatten before reshaping so each nested field is its own column.
Before flatten:
[
{ "id": 1, "addr": { "city": "NYC", "zip": "10001" } }
]
After json-flattener:
[
{ "id": 1, "addr.city": "NYC", "addr.zip": "10001" }
]
// Now transpose / convert to CSV with flat columnsRecords back to columns
ExampleIf a tool needs columnar JSON instead, Array → columnar reverses the orientation.
Input (records.json):
[
{ "sku": "A1", "qty": 5 },
{ "sku": "B2", "qty": 3 }
]
Mode: Array → columnar
Output:
{
"sku": ["A1", "B2"],
"qty": [5, 3]
}Numbers stay numeric for SUM/AVERAGE
ExampleBecause values aren't stringified, numeric columns arrive in the CSV as numbers Excel can aggregate directly in the pivot.
Input:
{
"item": ["pen", "pad"],
"price": [1.25, 3.5]
}
Mode: Columnar → array
Output:
[
{ "item": "pen", "price": 1.25 },
{ "item": "pad", "price": 3.5 }
]Errors and edge cases
Real errors and silent failures sourced from each platform's own documentation. Match the wording to the row, fix what the row says to fix.
Expecting an .xlsx or .csv download
Wrong toolThe JSON Transposer only outputs JSON. To get into Excel, convert the reshaped array with json-to-csv and open that CSV — Excel's PivotTable works on the CSV range.
Columnar → array given an array
RejectedIt throws pivot-to-records requires a columnar object input. The input must be an object of arrays. If you already have records, go straight to CSV conversion.
Nested objects in records
PreservedNested objects are not flattened, so a CSV built from them shows [object Object]-style cells and can't be pivoted on the nested field. Run json-flattener first.
Unequal column lengths
Null-filledColumnar → array uses the longest column and pads shorter ones with null, which become blank Excel cells — keeping the range rectangular for the pivot. Confirm blanks are expected.
A field's value isn't an array
Coerced to nullIn Columnar → array, a top-level field that is not an array contributes null to every record. Ensure each column is genuinely an array of values.
Array → columnar with ragged records
MisalignedRecords with different keys produce columns of different lengths. Normalize keys before reshaping so the eventual CSV range is rectangular.
Empty object in records mode
ExpectedAn empty columnar object {} yields an empty array [] — no columns, no rows. No error.
Invalid JSON
Parse errorMalformed JSON fails to parse and nothing is produced. Fix with json-format-fixer before retrying.
File over the tier limit
BlockedAbove 2 MB on Free / 100 MB on Pro the file is blocked. Excel itself also has practical pivot-source limits, so very large datasets should be pre-aggregated.
Date strings
PreservedDates stored as strings pass through as strings; Excel may or may not auto-detect them as dates on CSV import. Format dates consistently (ISO 8601) upstream for reliable pivot grouping.
Frequently asked questions
Can this tool create an Excel file directly?
No. It outputs JSON. Convert the reshaped array with json-to-csv and open the CSV in Excel, then Insert → PivotTable on the range.
Which mode makes a pivot-ready row layout?
Columnar → array (pivot-to-records) turns a columns-object into one record per row — the rectangular layout Excel pivots need. If your JSON is already a record array, just convert it to CSV.
Does it flatten nested objects into columns?
No. Flatten first with json-flattener so each nested field becomes a dot-notation column; otherwise the CSV will contain object cells Excel can't pivot on.
What happens to columns of different lengths?
Columnar → array pads shorter columns with null, which become blank cells in Excel — keeping the pivot source rectangular.
Will numbers import as numbers?
Yes. Values aren't stringified, so numeric columns reach the CSV as numbers and Excel can SUM/AVERAGE them in the pivot.
My JSON is already a record array — do I need this tool?
Not for reshaping. Skip straight to json-to-csv. Use the transposer only when you must change between record and columnar orientation.
Why was my file rejected in Columnar → array?
That mode requires a top-level object of arrays. An array input throws pivot-to-records requires a columnar object input — switch modes or skip the reshape.
Is there an option to set indentation?
No — the UI is just the three mode radios, and output uses 2-space indentation. Minify with json-minifier if needed.
How big a file can I reshape?
Free up to 2 MB, Pro up to 100 MB. This is a Pro tool requiring a Pro plan to run.
Is my data uploaded?
No — reshaping happens entirely in your browser; the file never leaves the tab.
How do dates behave?
Date strings pass through unchanged. Use ISO 8601 consistently upstream so Excel recognizes them as dates for pivot grouping.
Can I combine several JSON files before pivoting?
Not in this tool. Merge them with json-object-merger, then reshape and convert to CSV.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.