Excel to JSON: convert a sheet to JSON in 3 ways

A developer asked for “the data as JSON”. They want an array of objects, one per row, keys from your headings:

[
  {"name": "Ana", "city": "Pune"},
  {"name": "Raj", "city": "Goa"}
]

Way 1: a formula (quick, small sheets)

Headings in row 1 (name, city), data from row 2. In C2:

="{""name"":"""&A2&""",""city"":"""&B2&"""}"

Drag down. Each cell is one JSON object. Then in a cell below:

="[" & TEXTJOIN(",", TRUE, C2:C100) & "]"

Copy that cell, paste into a text file, save as .json. Doubled quotes "" are how Excel writes one quote inside a string.

Way 2: Power Query (clean, repeatable)

1. Click in the data › Data › From Table/Range.
2. In Power Query: Add Column › Custom Column, formula Json.FromValue(_) is not available directly — instead use Transform › To Table steps… Simpler: Home › Advanced Editor and append , Json = Text.FromBinary(Json.FromValue(Table.ToRecords(#"Changed Type"))) as the last step, then set in Json.
3. Close & Load. Copy the single cell.

Repeatable with Refresh when data changes.

Way 3: a converter

Save as CSV, then use any “CSV to JSON” tool online. Fine for public data; not for customer data.

Numbers vs text

Quotes make a value text. For numbers leave them out: ...""qty"":"&B2&"}".

Check it worked

Paste the result into jsonlint.com — it must say Valid JSON.