It is a familiar handoff: a developer or an analytics platform gives you data "from the API," and what lands in your inbox is a .json file full of curly braces. Your manager wants it as an Excel report by end of day. Double-clicking the file opens a wall of text — because JSON is built for machines and apps, not for the grid-based world of spreadsheets.
The gap is easy to bridge once you understand the shape of the data. In this guide you will learn how JSON maps onto rows and columns, how to flatten nested objects, and two practical routes to a finished .xlsx: Excel's Power Query and the faster free Toolyfied JSON to Excel converter.
Understanding the Shape: JSON Arrays Become Excel Rows
Most API responses are, at their core, an array of objects — a list of records where each object is one item (an order, a user, a transaction) and each key is a field. That structure maps perfectly onto a spreadsheet: every object becomes a row, every unique key becomes a column header, and the values fill in the grid. A response listing 500 orders converts into a 500-row sheet with columns like id, customer, total, and status.
Two wrinkles complicate real-world responses. First, the array is often wrapped inside an envelope — something like a data or results property alongside pagination metadata — and it is the inner array you actually want as rows. Second, values are frequently not simple text or numbers but nested objects and arrays, which is where flattening comes in.
Flattening Nested JSON Into Excel Columns
Spreadsheets are two-dimensional; JSON is not. When an order object contains a customer object with its own name and email, there is no single cell that can hold "an object." The standard solution is flattening: nested keys get joined into compound column names, so customer.name and customer.email become two ordinary columns. Good converters do this automatically, walking every level of nesting and generating a column per leaf value.
Nested arrays are the harder case. If each order contains an items array with three products, you must choose: expand each item into numbered columns (items.0.sku, items.1.sku), or split items into their own sheet keyed by order ID. For reporting summaries, the numbered-column expansion is usually fine; for serious line-item analysis, ask for (or extract) the items as their own array and convert it separately. Either way, know your response's shape before converting — paste it into the JSON formatter first to pretty-print, validate, and visually inspect the nesting.
Route 1: Power Query (Built Into Excel, but Fiddly)
Excel's own answer is Power Query: Data, then Get Data, then From File, then From JSON. It imports the file as a query, then you manually convert the list to a table and click the expand icon on each column that contains nested records — once per nesting level. Power Query is genuinely powerful when you need a refreshable connection that re-pulls data on a schedule.
For a one-off report, though, it is a lot of clicking, the interface intimidates non-technical users, and it is only available in Excel for Windows and (partially) Mac. If your goal is simply "turn this JSON file into a spreadsheet now," there is a faster way.
Route 2: Convert JSON to Excel Online in Under a Minute
For the instant version of the workflow, use the free online converter:
- Step 1 — Get the raw JSON: download the API response or save it from your browser or API client as a .json file.
- Step 2 — Optional but smart: run it through the JSON formatter to confirm it is valid JSON and see the structure at a glance.
- Step 3 — Upload the file to the JSON to Excel converter — free, no sign-up, no watermark, files up to 50 MB.
- Step 4 — Convert. Objects become rows, keys become column headers, and nested fields are flattened into compound columns automatically.
- Step 5 — Download the .xlsx and polish it in Excel: add filters, a pivot table, or charts for the final report.
- Step 6 — If the data later needs to go back to the developers, the Excel to JSON converter reverses the trip.
Tips for a Clean, Report-Ready Spreadsheet
A few habits save cleanup time. Convert one array per file: if the response wraps your records in an envelope with metadata, extract just the inner array so you do not get pagination fields mixed into your rows. Watch date fields — APIs usually return ISO 8601 strings (2026-04-11T09:30:00Z), which Excel imports as text; use Data, then Text to Columns or a DATEVALUE formula to make them sortable. And check numeric identifiers like order numbers: format those columns as Text if you need to preserve leading zeros. With the shape understood and the right converter, "API to spreadsheet" stops being a developer request and becomes a two-minute task you own.
Frequently Asked Questions
How do I open a JSON file in Excel?
Excel cannot open JSON directly as a normal spreadsheet — you either import it through Power Query (Data > Get Data > From JSON) or convert it first. The quickest route is uploading the file to a free JSON to Excel converter and downloading a ready-made .xlsx.
How do I flatten nested JSON into Excel columns?
Nested keys are joined into compound column names, so a customer object with a name field becomes a customer.name column. Toolyfied's JSON to Excel converter flattens nested objects automatically; deeply nested arrays are best split into their own sheet or file.
Can I convert JSON to Excel without Power Query?
Yes. Upload your .json file to the free Toolyfied JSON to Excel converter, click convert, and download the .xlsx — no Power Query steps, no sign-up, no watermark, with support for files up to 50 MB.
