To convert an Excel table to JSON, use a script or another conversion tool: Microsoft Excel for Microsoft 365 does not provide a built-in Save As JSON command. For a repeatable local conversion, Python with the openpyxl library can read a named table and write its rows as a UTF-8 JSON array of objects.
What an Excel table becomes in JSON
An Excel table is a structured range with a header row, data rows, and optional features such as filters, a total row, and a table name. JSON is a text format for representing data. A common conversion turns each table row into an object and uses the Excel column headings as object keys. The objects are collected in a JSON array.
For example, a table with columns Product, Quantity, and In stock might become:
[
{"Product": "Notebook", "Quantity": 12, "In stock": true},
{"Product": "Pen", "Quantity": 30, "In stock": false}
]
That structure is useful when an application or web service expects a collection of records. Each object represents one row; the keys identify fields, and the values contain the corresponding cell data. The structure is not the only possible JSON design: an API might instead require nested objects, a wrapper such as an items property, or different field names. Check the destination’s specification before converting.
Choose a conversion method
The right method depends on whether this is a one-time export, a recurring workflow, or a file that contains sensitive information. Excel can save a worksheet as CSV, but CSV is not JSON and does not preserve all workbook details. A script can target a specific named table and control output more precisely. Automation platforms can suit recurring business processes, while an online converter may be convenient for a small, non-sensitive file.
- One-time, small export: Save the relevant data as CSV and convert it with a trusted local tool, taking care to select only the intended table.
- Repeatable conversion: Use a local script that reads the table by name, so other worksheets and unrelated cells are not accidentally exported.
- Business workflow: Consider an approved automation service if it can access the workbook’s storage location and produce the required JSON shape.
- Sensitive data: Prefer an approved local or organizational process over uploading a workbook to an unfamiliar website.
Excel for the web and Excel for Mac do not change the basic limitation: there is no general-purpose Save As JSON option in Excel. Menu names and the availability of automation features can vary by platform and organization, so confirm the feature in the version you use.
Convert a named table with Python
This example reads a table from an .xlsx workbook and writes an array of JSON objects. It uses openpyxl, a Python library for reading Excel workbooks. Install Python if needed, then install the library from a command prompt with python -m pip install openpyxl. On some Windows installations, the command is py -m pip install openpyxl. Change the workbook path, worksheet name, and table name in the script to match your file.
from datetime import date, datetime, time
import json
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils.cell import range_boundaries
source = Path('workbook.xlsx')
sheet_name = 'Data'
table_name = 'Orders'
output = Path('orders.json')
# Read table metadata, including its cell range.
meta_wb = load_workbook(source, data_only=False)
meta_ws = meta_wb[sheet_name]
table = meta_ws.tables[table_name]
min_col, min_row, max_col, max_row = range_boundaries(table.ref)
headers = [meta_ws.cell(min_row, col).value
for col in range(min_col, max_col + 1)]
if any(not isinstance(header, str) or not header.strip()
for header in headers):
raise ValueError('Every table column needs a nonblank text heading.')
if len(set(headers)) != len(headers):
raise ValueError('Column headings must be unique.')
# Read cell results rather than formula expressions.
values_wb = load_workbook(source, data_only=True, read_only=True)
values_ws = values_wb[sheet_name]
records = []
for row in values_ws.iter_rows(
min_row=min_row + 1, max_row=max_row,
min_col=min_col, max_col=max_col, values_only=True):
records.append(dict(zip(headers, row)))
def json_default(value):
if isinstance(value, (datetime, date, time)):
return value.isoformat()
raise TypeError(f'Cannot convert {type(value).__name__} to JSON')
output.write_text(
json.dumps(records, ensure_ascii=False, indent=2,
default=json_default),
encoding='utf-8'
)
values_wb.close()
meta_wb.close()
print(f'Wrote {len(records)} rows to {output}')
The script expects a worksheet named Data and a table named Orders; those are examples, not special Excel names. In Excel, select a cell in the table and look for its name on the Table Design tab. The worksheet tab supplies the sheet name. If either name is wrong, Python will report that it cannot find the sheet or table. The output file is written next to the script unless you specify a different path.
Before relying on the result, check whether the workbook has a totals row. Depending on the table range and workbook metadata, a totals row may be included among the cells the script reads. If it appears in the JSON, adjust the range or handle that row separately. The example also stops if headings are blank or duplicated, because JSON object keys should identify fields unambiguously for the systems consuming the file.
Check the output and data types
JSON distinguishes strings, numbers, booleans, arrays, objects, and null values. A numeric Excel cell is generally written as a JSON number, text as a string, and an empty cell as null. Dates need extra attention: Excel stores dates as serial numbers with formatting, while JSON has no built-in date type. The script converts Python date and time values to ISO-style text, such as 2026-11-02 or 09:30:00. Agree on a date format with the receiving application; a date-only value and a timestamp are not interchangeable in every system.
Leading zeros are another common concern. If an identifier such as 00427 is stored in Excel as a number, the leading zeros may already be lost; formatting the cell to display zeros does not necessarily make the underlying value text. Store codes, postal codes, and account-like identifiers as text when their characters, rather than their numeric value, matter. Likewise, a displayed percentage may be stored as a decimal, and a currency symbol may be formatting rather than part of the value. Decide whether the JSON should contain the underlying value or a formatted display string.
The example uses ensure_ascii=False, so non-English characters can be written directly to the UTF-8 file. JSON strings still require escaping for characters such as quotation marks and line breaks; Python’s JSON library handles that. Avoid building JSON by joining cell text with commas or adding quotation marks yourself, because a value containing a comma, quote, or newline can make the output invalid.
Formulas, blanks, and table structure
For formula cells, the script opens the workbook a second time with data_only=True. This asks openpyxl for the value cached in the workbook rather than the formula text. It does not calculate formulas. If Excel has not recalculated and saved the workbook, a cached result may be missing or out of date. Open the file in Excel, allow calculations to update, save it, and then run the conversion again if the JSON needs current formula results. If you need formulas themselves, read with data_only=False and decide how the receiving system should represent them.
Blank cells become null in the object rather than causing the field to disappear. That distinction matters: a receiving application may treat a missing key differently from a key whose value is null. Completely blank rows inside a table can also produce objects full of nulls. Inspect the output and remove or filter such rows only if that matches the meaning of your data.
A table export contains cell values, not the full Excel experience. It does not carry over column widths, filters, conditional formatting, formulas as live calculations, charts, or workbook relationships. Structured references and table styles are not JSON data. If those workbook features matter, JSON alone is not a replacement for the original spreadsheet.
Common problems and how to avoid them
- Wrong range or table: A worksheet may contain several tables or notes beside the data. Target the table by its Excel table name rather than guessing a range.
- Unexpected headings: Blank or repeated headings make records ambiguous. Give each column a distinct, meaningful header before exporting.
- Dates look different: JSON has no native date type. Specify whether the consumer expects a date, a timestamp, or a formatted string, and validate a few values.
- Formula results are blank or old: A library may read saved formula results without recalculating them. Recalculate and save in Excel before conversion.
- Numbers change meaning: Leading-zero identifiers, long account codes, percentages, and rounded display values can be misread if you assume the screen display is the stored value.
- Output is valid but rejected: The destination may require particular key names, nesting, or data types. Compare the JSON with its schema or a known-good example.
After conversion, open the JSON in a text editor and check its first and last records, row count, headings, a date, an empty cell, and any values with punctuation or non-English characters. If the receiving service offers a validator or schema, use it. A file can be syntactically valid JSON and still have the wrong fields or data types for its intended use.
Privacy and safe handling
An Excel workbook can contain hidden worksheets, comments, personal information, or other data beyond the visible table. A script that exports a named table limits the output to that table’s declared range, but it cannot determine whether every field is appropriate to share. Review the table and the resulting JSON before sending it to another person or service. JSON is plain text, so anyone who can read the file can see its contents.
Online converters require uploading the workbook, which may conflict with workplace rules or confidentiality requirements. Use only a service approved for the data involved. Keep the original workbook unchanged, save the JSON to an appropriate location, and remove temporary exports according to your organization’s retention practices.
Frequently Asked Questions
Can Excel directly save a table as JSON?
No. Excel for Microsoft 365 does not include a general Save As JSON command. You can use a script, an approved automation workflow, or a conversion tool to create JSON from the table.
Can I convert an Excel table to JSON without Python?
Yes. You can use a trusted conversion utility or an organization-approved automation service. Saving as CSV is another intermediate option, but CSV is not JSON and may lose distinctions such as data types and workbook table boundaries.
Does the conversion include formulas or their results?
That depends on the tool. The Python example reads saved formula results, not formulas, and it does not calculate them. Recalculate and save the workbook in Excel first if current formula results are required.
How do I preserve leading zeros in JSON?
Store identifiers with leading zeros as text in Excel before exporting. If Excel already stores 00427 as the number 427, the original zeros cannot reliably be recovered from the numeric value alone.
Will JSON keep Excel formatting and filters?
No. A typical table-to-JSON conversion exports data values and field names, not table styles, column widths, filters, or other workbook presentation features. Keep the workbook if those features are needed.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Get Rid of the Paste Options Box in Excel 365To stop Excel for Microsoft 365 on Windows from showing the floating Paste Options button after you paste,…
- How to Make a Flowchart in PowerPointTo make a flowchart in PowerPoint, choose Insert > SmartArt > Process for a quick, mostly linear diagram,…
- Wrap Text in Google Sheets on Mac: A Step-by-Step GuideTo wrap text in Google Sheets on a Mac, select the cell or cells, then choose Format >…
- How to Make Labels in Word: Print Them CorrectlyTo make labels in Microsoft Word, open Mailings > Labels, choose the label brand and product number, enter…
- Excel Day Hour Minute Second Format: Display DurationsTo display an elapsed time in Excel as hours, minutes, and seconds, apply the custom number format [h]:mm:ss.…
- Excel Data to Word: Choose the Best Way to Transfer ItTo put Excel data in Word, select the cells in Excel, press Ctrl+C, click where the data should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.