How to reshape json data for excel pivot table analysis
- Step 1Flatten the JSON array first — Use the JSON Flattener to convert nested objects to flat keys. { address: { city: 'NY' } } becomes { address_city: 'NY' }. This is necessary before transposing for Excel compatibility.
- Step 2Normalize the flattened array — Ensure all records have the same keys by using JSON Key Filter in keep-mode to select the columns you want in Excel. Remove or null any records with dramatically different shapes.
- Step 3Export as CSV — Use the JSON to CSV tool on the flattened, normalized array. The resulting CSV is directly importable into Excel via Data →’ Get Data →’ From Text/CSV.
- Step 4Build the pivot table in Excel — After importing the CSV, select the data range and Insert →’ PivotTable. The flat column names become pivot fields. Drag revenue to Values, month to Rows, and category to Columns for a summary table.
Frequently asked questions
What causes the '(blank)' values in my Excel pivot table?+
(blank) appears when a pivot field value is empty or null in the source data. Before importing, fill in missing values — use a default value like 'Unknown' for categorical fields and 0 for numeric fields — so pivot groupings show meaningful labels instead of (blank).
Can I use Power Query instead of this tool for JSON import into Excel?+
Yes. Excel's Power Query (Data →’ Get Data →’ From JSON) can import and flatten JSON directly. For simple nested JSON, Power Query's automatic column expansion works well. This tool is useful for preprocessing complex or irregularly shaped JSON that Power Query's automatic expansion handles poorly.
Is the business data transmitted to JAD Apps?+
No. All processing runs entirely in your browser. Financial data, sales records, and business metrics are never transmitted to JAD Apps servers.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.