How to Convert JSON to Excel (XLSX) with Formulas Intact

July 9, 2026

Converting flat JSON to a spreadsheet is usually trivial — the hard part is when you need the result to also include working Excel formulas, not just static values. A CSV can't hold a formula at all; a proper XLSX file can. Here's how to do the conversion both ways.

Step 1: Generate or Export Your JSON

Start with a flat or lightly-nested JSON array — one object per row, matching what you'd expect to see as a spreadsheet row. If you're building test data from scratch rather than pulling it from a real API, Dummy JSON Generator can export directly to CSV or SQL alongside JSON, which covers the simple case without any extra conversion step.

[
  { "product": "Keyboard", "quantity": 3, "unitPrice": 49.99 },
  { "product": "Mouse", "quantity": 5, "unitPrice": 19.99 }
]

Simple Case: JSON to XLSX with Static Values (SheetJS)

For most conversions, you just need the JSON values written into cells — no formulas required. The SheetJS library (xlsx on npm) is the standard tool for this in JavaScript.

import * as XLSX from 'xlsx'

const data = [
  { product: 'Keyboard', quantity: 3, unitPrice: 49.99 },
  { product: 'Mouse', quantity: 5, unitPrice: 19.99 },
]

const worksheet = XLSX.utils.json_to_sheet(data)
const workbook = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(workbook, worksheet, 'Orders')
XLSX.writeFile(workbook, 'orders.xlsx')

Adding Real Formulas (Not Just Values)

This is the part json_to_sheet alone won't do — it writes static values, not formula strings. To get a working Excel formula in a cell (e.g., a "total" column computed as quantity * unitPrice), you set the cell's .f property manually after the sheet is created.

const worksheet = XLSX.utils.json_to_sheet(data)

// Add a header for the new column
XLSX.utils.sheet_add_aoa(worksheet, [['Total']], { origin: 'D1' })

// Add a formula for each row (row 2 onward, since row 1 is headers)
data.forEach((_, i) => {
  const row = i + 2
  worksheet[`D${row}`] = { f: `B${row}*C${row}` } // quantity * unitPrice
})

XLSX.utils.book_append_sheet(workbook, worksheet, 'Orders')
XLSX.writeFile(workbook, 'orders-with-formulas.xlsx')

When opened in Excel or Google Sheets, column D will show a live formula (=B2*C2) rather than a static number — recalculating automatically if the underlying quantity or price changes.

Adding a SUM Row at the Bottom

const lastDataRow = data.length + 1
const sumRow = lastDataRow + 1
worksheet[`D${sumRow}`] = { f: `SUM(D2:D${lastDataRow})` }

Why Not Just Use CSV?

CSV is a plain-text format — it has no concept of a formula, cell formatting, or multiple sheets. If your output only ever needs raw values, CSV is simpler and universally compatible. The moment you need a formula, conditional formatting, or more than one sheet in a single file, you need XLSX, since those features simply don't exist in the CSV spec.

Common Pitfalls

  • Off-by-one row numbers: remember row 1 is your header row, so data starts at row 2 — a frequent source of formulas pointing at the wrong cell.
  • Excel formula syntax varies by locale: some locales use semicolons instead of commas as argument separators (SUM(A1;A2) vs SUM(A1,A2)) — stick to the US-locale syntax when generating programmatically, since that's what SheetJS expects.
  • Forgetting book_new(): you need both a worksheet and a workbook — writing a worksheet alone won't produce a valid XLSX file.

The Bottom Line

Use json_to_sheet for a quick static conversion, but if you need live formulas, set each cell's .f property directly after generating the base sheet — SheetJS supports it, it's just not the default behavior of the JSON conversion helper.