Why does your manager keep asking for JSON when all you have is an Excel sheet? Why did the dev team reject your ‘Copy → Paste into VS Code’ attempt? Why does the official Microsoft docs page on this topic just say ‘not supported’ and vanish?
The answer isn’t ‘you need Python’ or ‘hire a developer’. It’s that Excel *can* export to JSON — just not with File > Save As. You need to build it, not click it.
The Setup
We’re working with a real internal sales report from Alibaba Cloud’s APAC channel team — updated weekly, pulled from CRM exports. It has 9 rows, mixed data types, and one critical quirk: the Region column contains multi-word names with spaces (e.g., ‘East Asia’), and Deal Size is formatted as currency but stored as numbers. This matters later — trust me.
| ID | Account | Region | Close Date | Deal Size |
|---|---|---|---|---|
| S-7821 | Acme Corp | East Asia | 2024-03-15 | $45,200 |
| S-7822 | NexGen Logistics | Southeast Asia | 2024-03-18 | $12,950 |
| S-7823 | Zephyr Labs | Australia & NZ | 2024-03-20 | $8,400 |
| S-7824 | TerraFirma Ltd | East Asia | 2024-03-22 | $62,100 |
| S-7825 | Vanta Systems | Southeast Asia | 2024-03-25 | $31,750 |
| S-7826 | Orion Health | Australia & NZ | 2024-03-27 | $19,300 |
| S-7827 | Kairos Group | East Asia | 2024-03-29 | $27,600 |
| S-7828 | Lumina Solutions | Southeast Asia | 2024-04-01 | $14,850 |
| S-7829 | Stellar Dynamics | Australia & NZ | 2024-04-03 | $53,200 |
The Challenge
You’re told to ‘export this to JSON’ for an API integration. You try copying A1:E10 and pasting into a JSON validator — it fails instantly. You try saving as CSV and renaming to .json — the dev says ‘this isn’t valid JSON, it’s just comma-separated text’. You open Power Query and see no ‘Export to JSON’ button. And yes — Excel *really doesn’t have one*. The core problem isn’t missing features. It’s mismatched expectations: JSON requires structure (objects, arrays, quoted strings, escaped characters), while Excel stores flat, typed, unquoted values.
The hidden trap? Dates like 2024-03-15 in cell D2 are stored as serial numbers (45366). If you TEXTJOIN them raw, you’ll get "CloseDate":45366 — not what the API expects. Same for currency: $45,200 becomes 45200 unless you format it first.
Walking Through It
Here’s how we actually do it — no macros, no downloads, just native Excel (365 or 2021+). We’ll build a single formula in F1 that outputs valid JSON for the entire table. Start by cleaning up the source.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | In G1, enter: =TEXT(D2,"yyyy-mm-dd"). Drag down to G10. | Converts 45366 → "2024-03-15" | Ctrl+D |
| 2 | In H1: =TEXT(E2,"#,##0"). Drag down. | Removes $, keeps commas: 45200 → "45,200" | Ctrl+D |
| 3 | In I1: ="{\"id\":\""&A2&"\",\"account\":\""&B2&"\",\"region\":\""&C2&"\",\"close_date\":\""&G2&"\",\"deal_size\":"&H2&"}" | Raw object per row, with escaped quotes | Alt+= (to insert =) |
| 4 | In J1: ="["&TEXTJOIN(",",TRUE,I2:I10)&"]" | Wraps all objects in brackets → full JSON array | Alt+M+U (for TEXTJOIN) |
Yes — you *must* escape double quotes with backslashes inside the string. That’s the counterintuitive bit everyone skips. If you forget \ before each ", Excel treats it as the end of the string and errors out.
Now paste the result from J1 into any JSON validator (like jsonlint.com). It passes.
The Result
This is the exact output from J1 — copied as plain text, no formatting:
[{"id":"S-7821","account":"Acme Corp","region":"East Asia","close_date":"2024-03-15","deal_size":45200},{"id":"S-7822","account":"NexGen Logistics","region":"Southeast Asia","close_date":"2024-03-18","deal_size":12950},{"id":"S-7823","account":"Zephyr Labs","region":"Australia & NZ","close_date":"2024-03-20","deal_size":8400},{"id":"S-7824","account":"TerraFirma Ltd","region":"East Asia","close_date":"2024-03-22","deal_size":62100},{"id":"S-7825","account":"Vanta Systems","region":"Southeast Asia","close_date":"2024-03-25","deal_size":31750},{"id":"S-7826","account":"Orion Health","region":"Australia & NZ","close_date":"2024-03-27","deal_size":19300},{"id":"S-7827","account":"Kairos Group","region":"East Asia","close_date":"2024-03-29","deal_size":27600},{"id":"S-7828","account":"Lumina Solutions","region":"Southeast Asia","close_date":"2024-04-01","deal_size":14850},{"id":"S-7829","account":"Stellar Dynamics","region":"Australia & NZ","close_date":"2024-04-03","deal_size":53200}]What Could Go Wrong
Three real issues we saw last week in the Shanghai office:
- Mistake #1: Using
=TEXTJOIN("",TRUE,A2:E10)instead of building per-row objects first. Result: a flat, quote-less mess likeS-7821Acme CorpEast Asia2024-03-1545200— not JSON, not even close. - Mistake #2: Forgetting to wrap the final array in
[ ]. Output starts with{"id":...instead of[{"id":.... Validator says ‘Unexpected token {’ — confusing if you don’t know JSON syntax requires outer array/object braces. - Mistake #3: Leaving ampersands (&) unescaped in Region names. “Australia & NZ” becomes
"Australia & NZ"— breaks parsing. Fix: replace&with&in source or use SUBSTITUTE inside the formula.
Next step: copy the value from J1, right-click → Paste Special → Unicode Text (to avoid Smart Quotes), then save as .json. Done.
