Excel does not have a built-in worksheet function that returns any table as a ready-to-download JSON file. For a reusable export in Microsoft 365, use an Office Script to read a named table and call JSON.stringify; use Power Automate when the export needs to run as part of a workflow. A formula can assemble JSON text for a small, controlled range, but it takes care to produce valid JSON.
What it means to return Excel data as JSON
JSON is a text format for representing structured data. A typical export represents a table as an array of records: each record has fields corresponding to column names, and each field has a value. For example, a customer table might become a list of objects with fields such as customer ID, name, and status. An alternative is an array of arrays, with one header array followed by rows of values.
Excel tables are structured ranges with headers, rows, and optional totals. They are not JSON objects, and saving a workbook as an Excel file does not convert them into JSON. The phrase return table data as JSON can mean several different things: show JSON text in a cell, return a JSON string from a script, send rows to another service, or create a .json file. Choosing the right approach depends on which result is needed.
JSON also has rules that ordinary spreadsheet text does not. Strings need quotation marks, embedded quotation marks and certain control characters must be escaped, and numbers, text, true or false, and null are distinct value types. A text export that merely looks like JSON can fail when another program tries to parse it.
Office Script: a practical table export
For a named Excel table, an Office Script can read its header and data ranges and serialize them. In Excel for the web, open the workbook and choose Automate > New Script. Replace the starter script with the following, change Sales to the actual table name, and run it:
function main(workbook: ExcelScript.Workbook): string {
const table = workbook.getTable('Sales');
const headers = table.getHeaderRowRange().getValues()[0];
const rows = table.getRangeBetweenHeaderAndTotal().getValues();
return JSON.stringify({ headers, rows });
}
This returns a JSON string containing a headers array and a rows array. It deliberately preserves the table as arrays rather than using column names as object keys. That avoids problems when headers are blank or duplicated, although the receiving system must use the header positions to interpret each row. For instance, the value at position zero in each row belongs to the first header.
Office Scripts are available in Microsoft 365, but availability can depend on the account, administrator settings, and Excel platform. They are supported in Excel for the web and in supported Microsoft 365 desktop versions. The key limitation is that returning a string from a script does not, by itself, download a JSON file or write the result into a worksheet cell. It is particularly useful when a flow or another supported process will consume the script result.
The script reads the table’s underlying values. Excel dates are commonly stored as serial numbers, so a date cell can appear in the JSON as a number rather than a formatted date such as 2026-11-03. If a consumer requires ISO date strings, add explicit date conversion for the relevant columns and define how blanks, times, and time zones should be handled. Formatting visible in Excel is not automatically a conversion rule.
Create a JSON file with Power Automate
When the goal is to move rows to another service or save an export on a schedule, Power Automate is often a better fit than a formula. A flow can use an Excel Online (Business) action such as List rows present in a table, then pass the returned records into later actions or create a file in a connected storage service. The Excel workbook must be stored in a location the connector can access, such as OneDrive for Business or SharePoint, and the source data should be formatted as an Excel table.
A practical flow usually needs decisions beyond serialization:
- Choose the workbook and table, and decide whether to retrieve every row or a filtered subset.
- Check connector pagination settings if the table may exceed the action’s default number of returned rows.
- Decide what should happen when a cell is blank, a date is stored as a serial value, or a column contains an error.
- Create or replace a file using a clear name and location, and consider whether repeated runs should overwrite or preserve earlier exports.
Connector limits and pagination behavior can change with service plans and settings, so check the current action documentation for the account in use. A flow is also subject to permissions and service availability. For a one-time export, the additional setup may be unnecessary; for recurring transfers, it can avoid manual copying and provide a repeatable process.
Can a worksheet formula make JSON?
Yes, a formula can concatenate values into JSON text, but Excel does not provide a general-purpose JSON-encoding worksheet function. A hand-built formula must add the required braces, brackets, commas, and quotes, while also escaping any quotation marks, backslashes, or line breaks inside text values. It must distinguish numeric values from text and handle empty cells consistently. A formula that works on a few clean sample rows may produce invalid output when a real value contains punctuation or a line break.
For example, joining a name and amount into text might seem straightforward, but a name containing a quotation mark can break the JSON string unless that character is escaped. A blank cell might need to become an empty string, a zero, or JSON null, depending on the receiving system. Those choices are data rules, not cosmetic formatting. Formula-based output is most appropriate for a small fixed layout with controlled values and a clear test plan; it is a poor substitute for a serializer when data is varied or externally supplied.
Dynamic-array formulas can make it easier to work with changing ranges in current Microsoft 365, but they do not remove the need for correct JSON escaping. Also, a formula returns text inside the workbook. It does not create a file or transmit the result. If a downstream application expects JSON, validate the output with a JSON parser before relying on it.
Choose an output shape and handle data types
Before exporting, decide whether the consumer expects an array of objects or an array of arrays. Objects are convenient because a field can be accessed by a name; arrays are compact and avoid naming collisions but rely on column order. The Office Script above returns headers and rows, a useful explicit array-based shape. If an API requires objects, transform each row into an object using unique, non-empty column names and define what to do with repeated headers.
Review data types and special cases rather than assuming the cell display is the stored value:
- Numbers may have a display format that adds currency symbols or rounds decimals; JSON should normally carry the numeric value, not the formatted display string.
- Dates and times may be stored as numeric serials. Convert them deliberately if the target expects readable date strings.
- Leading zeros in identifiers can be lost if Excel has treated an identifier as a number. Store such values as text when the zeros are meaningful.
- Blank cells, formula results that are empty text, and errors are not necessarily the same thing. Decide how each should be represented.
- Text with quotation marks, backslashes, or line breaks must be escaped by a proper JSON serializer.
These distinctions matter because a JSON export is data exchange, not a screenshot of the worksheet. If exact appearance is the requirement, a formatted report or PDF may be more suitable than JSON.
Common problems and troubleshooting
If a script says that it cannot find a table, check the table name rather than the worksheet tab name. Select a cell in the table and look for its name on the Table Design tab. A normal cell range is not automatically a table; create one with Insert > Table or Home > Format as Table, and confirm that the header row is identified correctly.
If the export is missing rows, check that the script or flow is pointed at the intended table, not just the visible portion of the sheet. With Power Automate, check pagination and any filters. Hidden rows may still be part of the table data, so hiding a row is not a reliable way to exclude it from an export. If only certain records should be included, filter or select them explicitly in the export process.
If a receiving application rejects the result, validate the JSON syntax and inspect the data types and field names. Frequent causes include an unescaped quotation mark, a trailing comma, a number written with a locale-specific decimal separator, duplicate object keys, or dates in an unexpected format. Avoid manually editing serialized JSON unless the edit is validated afterward.
Excel JSON export compared with alternatives
Office Scripts are suited to repeatable work inside Microsoft 365, particularly when another script or Power Automate flow will use the result. Power Automate suits scheduled or event-driven handoffs, though it requires connector access and flow configuration. A formula is convenient for visible, small-scale text generation but carries the greatest risk of custom escaping logic. For one-off transfers, copying table data into a tool that accepts CSV may be simpler, but CSV is not JSON and has different rules for quoting and types.
Power Query is useful for importing, cleaning, and reshaping data, then loading the result to a worksheet or data model. It is not the same as a one-click JSON-file export from a table. If the task is to send data to an API, use a supported connector or an automation process that explicitly serializes the records, rather than assuming that loading a query to a sheet has created JSON.
Privacy and safe handling
JSON is plain text, not an encryption format. A file saved to a shared folder or sent through a flow can expose the same sensitive values as the workbook. Check storage permissions, flow ownership, connector access, and retention requirements before exporting personal, financial, or confidential data. Export only the columns and rows the destination needs, and avoid putting sensitive data into test examples or logs.
For a script or flow that writes to a shared location, use an access-controlled destination and verify who can read the output. Keep a source workbook copy when an automation changes data, and test with a small non-sensitive sample before scheduling a recurring transfer. JSON can make data easier for systems to consume, but it does not make that data safer by itself.
Frequently Asked Questions
Can Excel save a table directly as a JSON file?
There is no general Save As option that converts an Excel table to JSON. Use a script or automation to serialize the table, then create a .json file, or use a specialized export process appropriate to the workflow.
Can I return JSON in a cell?
A formula can build JSON text in a cell for a controlled range, but it must correctly escape text and handle data types. For variable or externally supplied values, prefer a JSON serializer and validate the result.
Why are my Excel dates numbers in JSON?
Excel stores dates as serial numbers internally, and reading raw cell values can return those numbers. Convert date columns to a defined string format if the receiving application requires readable dates.
Does the Office Script above create a downloadable file?
No. It returns a serialized string. A flow or other process must use that result to create a file or send it somewhere; running a script does not automatically save a JSON file.
Should I use JSON or CSV?
Use JSON when the recipient needs named fields, nested data, or explicit data types. CSV is often simpler for a flat table and spreadsheet exchange, but it does not represent nested structures in the same way and does not carry JSON types.
Related
- Excel Protect Sheet: Can You Refresh a PivotTable?
- Excel Protect Cells in a Sheet: Setup, Limits, and Tips
- Excel Print a Certain Area: Set a Print Area
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 Add an Image Link to an Excel Cell in Microsoft 365To put a clickable link to an image in an Excel cell, select the cell and press Ctrl+K…
- Excel Column K Number: What It Means and How to Find ItIn Excel, column K is column number 11. Excel counts columns from left to right, starting with A…
- Excel Export to Tab-Delimited CSV: How to Save It CorrectlyYes, you can export tab-separated data from Excel, but the usual CSV option does not normally create a…
- Can You Manually Delete a Page in Word?Yes. You can manually remove a page in Word by selecting and deleting its contents, or by deleting…
- How to Create Multiple Address Labels in Microsoft WordTo create multiple address labels in Word, set up a recipient list, choose the label stock you have,…
- Google Sheets: Check If a Value Falls Within a RangeTo check whether a value is between two limits in Google Sheets, use =AND(A2>=$B$1,A2<=$C$1). This returns TRUE when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.