To convert Excel to JSON cleanly, prepare the spreadsheet first: put unique headers in row 1, keep one table per sheet, format IDs and ZIP codes as text, store dates in ISO format, decide how empty cells should appear, and plan any nesting. Then run the conversion and validate the JSON before importing it anywhere.
Introduction
Most guides on how to convert Excel to JSON focus on the button you click. In my experience, the button is the easy part. The hard part is everything that happens before it.
I have spent a lot of time moving spreadsheet data into websites, apps and automation tools, and the pattern is always the same. Someone needs to convert Excel to JSON, the conversion "works," the file looks fine, and then the import fails. Or worse, it succeeds with wrong data: prices as text, dates as random five-digit numbers, product codes missing their first zero, and two columns quietly merged into one.
None of those problems come from the tool you use to convert Excel to JSON. They come from the spreadsheet. Excel is designed to show data to people, while JSON is designed to hand data to programs, and the gap between "what Excel shows" and "what Excel stores" is where most errors hide.
This guide is the checklist I now follow every time I convert Excel to JSON. It works whether you use an online tool, a Python script or a developer's custom code, because it fixes the data before any of them touch it. If you are a marketer, an ecommerce manager, an analyst or a developer who keeps receiving messy spreadsheets, these eleven steps will help you convert Excel to JSON without hours of back-and-forth.
Why Spreadsheet Data Breaks When You Convert Excel to JSON
Before the checklist, it helps to understand the root cause. JSON (JavaScript Object Notation) is a strict text format. It has only a few data types: strings, numbers, true and false, null, objects and arrays. Every key must be in double quotes, and the structure has to be exact.
Excel, on the other hand, is flexible and forgiving. It lets you:
- Leave header cells blank or repeat the same header twice
- Merge cells across rows and columns
- Mix numbers and text in one column
- Display a date while storing a serial number underneath
- Show "05401" on screen while storing the number 5401
- Hide rows and columns that are still part of the file
- Put notes, totals and titles anywhere on the sheet
When you convert Excel to JSON, every one of those freedoms has to be translated into strict rules. If the converter has to guess, it guesses literally, and literal guesses produce JSON that is technically valid but practically wrong.
So the goal of the checklist is simple: remove the guesswork before you convert Excel to JSON.
Step 1: Decide the JSON Shape Before You Touch the Spreadsheet
The first question when you convert Excel to JSON is not "which tool?" It is "what should the JSON look like?" If you are handing the file to a developer, ask them. If you are the developer, write down the target structure first.
The most common shapes are:
- Array of objects: one object per row, with column headers as keys. This is the default for most tools and what most APIs expect.
- Object keyed by ID: each row stored under its ID, which is useful for fast lookups. Every ID must be unique.
- Nested JSON: objects inside objects, such as an address inside a customer, or an items array inside an order.
- One object per sheet: a workbook with several tabs becomes one JSON object with a key for each sheet.
Here is the simplest and most common target, an array of objects:
[
{ "sku": "MUG-001", "name": "Ceramic Mug", "price": 1200, "inStock": true },
{ "sku": "MUG-002", "name": "Travel Mug", "price": 2100, "inStock": false }
]
Write this sample down before anything else. Every later step is about making the spreadsheet produce exactly this structure when you convert Excel to JSON.
Step 2: Clean the Header Row (Your Future JSON Keys)
When you convert Excel to JSON, every column header becomes a key. A sloppy header row is the number one cause of messy output I see.
My header rules:
- Headers live in row 1. Delete report titles, logos, dates and blank rows above the table. Most tools that convert Excel to JSON assume the very first row is the header.
- Every column has a header. Blank headers produce strange keys such as __EMPTY or Unnamed: 4, depending on the tool.
- Every header is unique. The JSON specification says names inside an object should be unique, and many parsers keep only the last value when a name repeats. Two "Phone" columns can silently become one.
- Use one naming style. Pick camelCase (firstName) or snake_case (first_name) and use it everywhere. Ask the developer which one their code expects.
- No spaces or symbols. Price (PKR) incl. tax is legal JSON, but it is miserable to type in code. Use priceIncTax and note the currency in a separate column or in documentation.
- No merged header cells. Unmerge them and give each column its own name.
One habit that helps me a lot: I keep a "data dictionary" tab that lists each key, its type and an example value. It takes ten minutes and prevents endless questions later.
Step 3: Keep One Clean Table per Sheet
Tools that convert Excel to JSON read rectangles. They expect a block of rows and columns with no interruptions. Anything that breaks the rectangle breaks the JSON.
Before you convert Excel to JSON, remove or move:
- Total and subtotal rows. A "Total" row becomes a fake record with the word "Total" as a product name.
- Notes and comments typed into cells below or beside the table.
- Blank spacer rows used for visual grouping. Some tools skip them, others create empty objects.
- Merged cells in the data area. Only the top-left cell keeps the value; the rest become blank.
- Hidden rows and columns. Hiding does not delete. They are still converted unless your tool has an option to skip them.
- Multiple tables on one sheet. Move each table to its own sheet.
A quick trick: select the data and press Ctrl + T to format it as an Excel table. If the table boundaries look wrong, your data has gaps or extra pieces that need cleaning.
Step 4: Fix Data Types Column by Column
This is where most "the import worked but the data is wrong" problems start when people convert Excel to JSON. Go through the sheet one column at a time and decide what type each column should be in JSON.
Numbers that are really text. Product codes, phone numbers, ZIP codes, account numbers and IDs are labels, not quantities. Format these columns as Text in Excel before typing or pasting the data, because Excel strips leading zeros from numbers. A ZIP code entered as a number turns 05401 into 5401. Excel also keeps only 15 significant digits, so a 16-digit card or account number has its last digit turned into zero. Text format protects all of them when you convert Excel to JSON.
Text that should be numbers. Prices typed as "Rs 1,200" or "$1,200.00" come out as strings that code cannot add up. Store the plain number (1200) in the price column and put the currency code in its own column.
Percentages. A cell that shows 25% usually stores 0.25. Some tools export the displayed text "25%", others export 0.25. Decide which your app expects, and name the key clearly, for example discountRate for 0.25.
Yes/No columns. JSON has real booleans, true and false, and they must be lowercase. Columns full of "Yes", "Y", "TRUE" or "Active" should be standardized so the output can become proper booleans.
Mixed columns. If a column holds numbers in some rows and words like "N/A" or "TBC" in others, many tools will turn the whole column into text. Pick one type per column and use empty cells for missing values.
Step 5: Handle Dates the Right Way
Dates cause more confusion than anything else when people convert Excel to JSON, because Excel never stores the date you see.
Excel stores dates as serial numbers, counting days from a starting date. In the default 1900 date system, July 5, 2011 is stored as 40729. Some older Mac workbooks use the 1904 date system, where the same date is 39267. The gap between the two systems is 1,462 days, which is enough to shift every date by about four years if a tool assumes the wrong one.
When a tool reads the stored value instead of the displayed one as you convert Excel to JSON, your JSON shows something like "orderDate": 45597, which means nothing to a developer.
My date rules:
- Output dates as ISO 8601 text, such as 2026-11-02 or 2026-11-02T14:30:00Z. Every programming language can read it and sorting works correctly.
- Check your tool's date setting. Many libraries offer an option for ISO dates. In Python pandas, for example, date_format="iso" does exactly this.
- Agree on time zones. If times matter, store them in UTC or include the offset. "14:30" alone is ambiguous once data crosses countries.
- Check the workbook's date system if the file came from an old Mac or a legacy system, before you trust any date in it.
- Watch out for text dates. "02/11/2026" can mean February 11 or November 2 depending on who typed it. Convert ambiguous text dates to real dates or ISO text first.
Step 6: Decide What Empty Cells Should Become
Empty cells look harmless in Excel, but when you convert Excel to JSON they can appear three different ways:
- The key is left out: {"name": "Sara"}
- The key is null: {"name": "Sara", "email": null}
- The key is an empty string: {"name": "Sara", "email": ""}
Each one behaves differently in code. A check for email === null will miss the empty string, and a check for a missing key will miss both of the others. Pick one rule for the whole file, write it in your data dictionary, and make sure your conversion method follows it.
I usually choose null, because it keeps every object the same shape and makes the "missing" state explicit. Some JavaScript libraries skip empty cells by default, so you may need to turn on an option that fills blanks with null.
One more trap: some tools treat certain words as "missing" automatically. Python pandas, for example, treats text like NA, NULL, None and #N/A as missing values by default. If "NA" is a real value in your data, such as the country code for Namibia, switch that behavior off before you convert Excel to JSON.
Step 7: Replace Formulas and Error Values
Formulas are invisible traps when you convert Excel to JSON. Depending on the tool, a formula cell can come out as:
- The calculated result (what you usually want)
- The formula text itself, like "=SUM(B2:B10)"
- Nothing at all, if the workbook was generated by software and never opened and saved in Excel
Error values are worse. #N/A, #DIV/0!, #REF! and #VALUE! can end up as text strings in an otherwise numeric column, or as null, depending on the tool.
My fix is simple and reliable:
- Wrap risky formulas in IFERROR() so errors become blanks or zeros on purpose.
- Copy the finished data and use Paste Special > Values into a clean "export" sheet.
- Convert Excel to JSON from the export sheet, not your working sheet.
Keeping a separate export sheet also means you never break your original calculations while preparing the data.
Step 8: Plan Nested JSON Before You Convert
If your target JSON is flat, skip this step and convert Excel to JSON directly. If it needs objects inside objects or lists inside records, you need a plan, because a spreadsheet is flat by design.
There are three practical patterns.
Pattern 1: Dot-notation headers. Name columns address.street, address.city and address.zip. Tools and scripts that support this convention build an address object automatically. A basic converter will output the literal key "address.city" instead, so check your result.
Pattern 2: Repeated parent ID for one-to-many data. For orders with several items, put one item per row and repeat the order ID:
A short script then groups the rows by orderId so each order holds an items array:
{
"orderId": 1001,
"customer": "Sara",
"items": [
{ "itemSku": "MUG-001", "qty": 2 },
{ "itemSku": "MUG-002", "qty": 1 }
]
}
Pattern 3: One sheet per level. Put customers on one sheet and orders on another, linked by a customer ID. Read both sheets and join them in code. I use this for anything deeper than two levels, because it keeps the workbook easy for non-technical colleagues to edit.
Most simple online converters produce flat JSON only, so if you need to convert Excel to JSON with deep nesting, plan on a small script or ask a developer to handle the grouping step.
Step 9: Clean Special Characters and Encoding
- **Text cells can contain things **that look normal in Excel but need care when you convert Excel to JSON:
- Line breaks inside a cell (Alt + Enter) must become \n in JSON. Good tools handle this; hand-built scripts often do not.
- Double quotes inside text, such as a product called 12" Ruler, must be escaped as \".
- Non-English text such as Urdu, Arabic or accented letters should be saved as UTF-8, which is the encoding the JSON standard requires when data moves between systems. Some tools escape these characters into long \u codes. That is valid JSON, but hard for people to read, so look for an option that keeps them readable.
- **Invisible spaces **at the start or end of cells, often from copy-pasting, create values like "Lahore " that fail exact matches. Use Excel's TRIM function to clean them.
Step 10: Choose the Right Way to Convert Excel to JSON
Only now does the tool you use to convert Excel to JSON matter, and after the first nine steps almost any reliable method will give you clean output. Here is how I choose the way I convert Excel to JSON:
- One-off file, no coding: use a browser-based converter. I often use the free Excel to JSON converter from X2X Converter, which reads the first row as keys, runs in the browser, and lets you copy or download the result in seconds.
- Inside Microsoft 365: Office Scripts on the Automate tab can turn an Excel table into JSON, and Power Automate can run it on a schedule. It needs a business or education plan.
- Large files or repeat jobs: Python with pandas reads the workbook and writes JSON in a couple of lines, with full control over types, dates and empty values.
- Inside a web app: JavaScript libraries such as SheetJS read spreadsheets in the browser or in Node.js.
- No Excel installed: open the file in Google Sheets and use a short Apps Script.
If you are a developer who would rather not write a new parser for every client file, I like the practical take in this guide to data conversion without writing scripts, which covers when a ready-made tool beats custom code.
A note on privacy: some online tools upload your file to a server, while others process it locally in your browser. If you convert Excel to JSON for customer lists, payroll or any personal data, choose a tool that clearly says files stay on your device, or run the conversion locally.
Step 11: Validate the JSON Before You Import It
Never assume the file is correct just because you managed to convert Excel to JSON without an error message. I run three checks every time I convert Excel to JSON.
Check 1: Is it valid JSON? Paste it into a JSON validator or run JSON.parse() in your browser console. Invalid JSON usually means single quotes, a trailing comma after the last item, uppercase TRUE or NULL, or NaN values, none of which the JSON standard allows.
Check 2: Is it the right shape? Compare the first two or three records against the sample you wrote in Step 1. Are keys spelled correctly? Is it an array of objects? Is nesting where it should be?
Check 3: Are the values right? Spot-check the risky columns: one date, one ID with leading zeros, one price, one boolean, one empty cell and the last row of the sheet. Then compare the record count with the number of rows in Excel. If the sheet had 500 products, the JSON should have 500 objects.
For teams that import data regularly, a JSON Schema takes this further. It is a separate file that describes which keys are required and what type each one must be, so a validator can reject bad data automatically before it reaches your app.
A Before and After Example
Here is a realistic product sheet as it often arrives when someone asks me to convert Excel to JSON:
If you convert Excel to JSON straight from this sheet, you get a title as a key, text prices, ambiguous dates, missing zeros and a fake "TOTAL" product.
After the checklist, the sheet has headers in row 1 (sku, name, price, launchDate), SKU formatted as text, plain numeric prices, ISO dates and no total row. The output becomes:
[
{ "sku": "00123", "name": "Ceramic Mug", "price": 1200, "launchDate": "2026-11-02" },
{ "sku": "00124", "name": "Travel Mug", "price": 2100, "launchDate": "2026-11-15" }
]
Same data, same tool to convert Excel to JSON, completely different result. The difference is entirely in the preparation.
Build a Reusable Template for Your Team
If your team needs to convert Excel to JSON every week, do not clean the same mess every week. Build the rules into a template instead:
- A locked header row with the exact key names your system expects
- Columns pre-formatted as Text, Number or Date
- Data validation dropdowns for fixed values (for example, "active" or "inactive")
- A data dictionary tab explaining each column
- A separate export sheet that pulls clean values from the working sheet
Once the template exists, anyone on the team can fill it in and convert Excel to JSON cleanly without knowing anything about JSON.
Common Mistakes at a Glance
Key Takeaways
- Most problems when you convert Excel to JSON come from the spreadsheet, not the converter.
- Decide the target JSON shape first, then shape the sheet to match it.
- Headers become keys: keep them in row 1, unique, filled and consistently named.
- Format IDs, phone numbers and ZIP codes as text so leading zeros survive.
- Output dates as ISO 8601 text and agree on time zones.
- Choose one rule for empty cells and stick to it.
- Paste formulas as values and remove error codes before exporting.
- Plan nested JSON with dot-notation headers, repeated parent IDs or one sheet per level.
- Validate syntax, structure and values before any import.
Frequently Asked Questions
*What should I check before I convert Excel to JSON?
*
Check that headers are in row 1 and unique, each sheet holds one clean table, IDs and ZIP codes are formatted as text, dates are real dates, formulas are pasted as values, and empty cells follow one rule. These checks prevent almost every common error people hit when they convert Excel to JSON.
*Why does my JSON have strange keys like __EMPTY or Unnamed?
*
Those keys come from blank header cells, a common issue when you convert Excel to JSON. The converter needs a name for every column, so it invents one when the header is empty. Give every column a clear, unique header in row 1, and delete stray content to the right of your table.
*Why did my numbers turn into text in the JSON?
*
When you convert Excel to JSON, this usually happens because the column mixes numbers with text such as "N/A", currency symbols or spaces, or because the tool exports displayed text instead of raw values. Keep one data type per column, store plain numbers without symbols, and check your tool's options for raw values.
*How do I stop Excel from removing leading zeros?
*
Format the column as Text before entering or pasting the data, because Excel removes leading zeros from numbers automatically. Typing an apostrophe before the value also works for single cells. Once stored as text, values like 00123 stay intact when you convert Excel to JSON.
*What is the best date format for JSON?
*
ISO 8601 text, such as 2026-11-02 for a date or 2026-11-02T14:30:00Z for a date and time. It is unambiguous, sorts correctly and is understood by every major programming language. When you convert Excel to JSON, avoid exporting Excel's internal serial numbers or locale-specific formats like 02/11/2026.
*Should empty cells become null or an empty string?
*
Either can work, but be consistent. Null clearly means "no value," and keeping the key in every object gives all records the same shape, which makes code simpler. Agree on the rule with whoever will use the JSON, then set up the method you use to convert Excel to JSON to follow it.
*Can a flat spreadsheet produce nested JSON?
*
Yes, with planning. Use dot-notation headers like address.city for nested objects, repeat a parent ID on each row for one-to-many lists, or store each level on its own sheet and join them in code. Simple converters output flat JSON, so deep nesting usually needs a script.
*How do I convert a workbook with several sheets?
*
To convert Excel to JSON from a multi-sheet workbook, choose a method that reads every tab. Many scripts and libraries return one JSON object keyed by sheet name, or you can export each sheet as its own file. Quick online tools often read only the first sheet, so confirm which sheet was converted.
*How can I tell if my JSON file is valid?
*
Paste it into a JSON validator or run JSON.parse() in a browser console. Errors usually mean single quotes, trailing commas, uppercase TRUE or NULL, or NaN values. Then compare the record count and a few sample records with the original spreadsheet.
*Is it safe to convert sensitive spreadsheets online?
*
Only with a tool that processes files inside your browser rather than uploading them to a server. Read the tool's privacy statement first. For payroll, customer or health data, a browser-based converter or a script on your own computer keeps the information on your device.
Conclusion
Most people can convert Excel to JSON in two minutes. Preparing the spreadsheet properly is what decides whether those two minutes save your project or create a week of debugging. When you convert Excel to JSON after cleaning headers, fixing types, handling dates, setting an empty-cell rule, planning nesting and validating the output, the result is JSON that developers can trust and systems can import on the first try.
Print this checklist, build a template for your team, and the next time someone sends you a messy workbook, you will know exactly what to fix before you convert Excel to JSON.



Top comments (0)