Mastering XLSX Files with AI: Structure, Limits, and Best Practices
An XLSX file is not a spreadsheet. It is a zipped package of XML documents that a spreadsheet application reads. That one fact explains most of the odd behavior people hit. Files that bloat, formulas that recalculate in a strange order, and small-looking workbooks that take forever to open.
This guide covers what is inside the format, the limits Microsoft publishes, and the habits that keep an XLSX file usable by other people.
What an XLSX file actually is
XLSX is the default Excel format introduced with the Office Open XML standard. Where the older binary format stored everything in one opaque blob, XLSX stores structured XML parts inside a container.
That change is why modern tooling can read the format without Excel installed. It is also why an XLSX file can be inspected, diffed, and repaired in ways its predecessor could not.
The practical consequence for anyone handling data: an XLSX file carries far more than values. Formatting, formulas, pivot caches, conditional rules, and calculation order all travel with it.
Inside the package
Microsoft's SpreadsheetML documentation describes the skeleton precisely. The structure "consists of the <workbook/> element that contains <sheets/> and <sheet/> elements that reference the worksheets in the workbook."
Then comes the detail that surprises most people. "A separate XML file is created for each worksheet." A ten-tab workbook is at least ten documents plus a manifest, not one file with ten sections.
Microsoft lists these as "the minimum elements required for a valid spreadsheet document." Beyond them, a workbook "might contain <table/>, <chartsheet/>, <pivotTableDefinition/>, or other spreadsheet related elements."
Four of those extra parts explain most real-world file behaviour.
The shared string table. Microsoft describes <sst/> as "a construct that contains one occurrence of each unique string that occurs on all worksheets in a workbook." Text is stored once and referenced everywhere, which is why a file with heavy repetition compresses well.
The calculation chain. <calcChain/> "specifies the order in which cells in the workbook were last calculated." It is a cache, and a corrupted one is a classic cause of workbooks that behave strangely until the part is discarded.
The pivot cache. <pivotCacheDefinition/> defines the source of a PivotTable's data, while <pivotCacheRecords/> holds "a cache of the source data." A pivot table can therefore carry a full copy of its source rows inside the file.
Tables and chart sheets. A <table/> marks a range as a single dataset, and a <chartsheet/> is a chart stored as its own sheet.
Microsoft also cites the ECMA-376 standard for the floor: the smallest possible blank workbook still needs a single sheet, a sheet ID, and a relationship. There is no such thing as an XLSX file with nothing in it.
XLSX versus CSV
Both formats hold tabular data. They disagree about everything else.
| XLSX | CSV | |
|---|---|---|
| Container | Zipped package of XML parts | One plain text file |
| Multiple sheets | Yes | No |
| Formulas | Stored, with a calculation chain | Not supported |
| Formatting and types | Preserved | Not preserved |
| Charts and pivot tables | Stored inside the file | Not supported |
| Readable without tooling | No | Yes |
| Typical failure mode | Bloat and hidden state | Lost types and delimiter ambiguity |
Neither is the better format in the abstract. CSV wins when a machine on the other end just needs rows, which is the case our guide to mastering CSV files covers in detail.
XLSX wins when a person on the other end needs the structure. The tab layout, the number formats, and the notes column that only makes sense beside its neighbor.
The mistake is using XLSX as a transport format between systems. All that extra state has to be parsed by something, and it carries no benefit when the destination is a database.
The limits Microsoft publishes
Microsoft's specifications and limits page lists the hard ceilings. These are the ones that matter when handling real exports.
| Item | Published limit |
|---|---|
| Rows by columns on a worksheet | 1,048,576 by 16,384 |
| Characters in a single cell | 32,767 |
| Sheets in a workbook | Limited by available memory |
| Length of formula contents | 8,192 characters |
| Column width | 255 characters |
| Unique items per PivotTable field | 1,048,576 |
| Report filters in a PivotTable | 256 |
File size deserves its own note, because the answer is not a number. Microsoft states that the "64-bit environment imposes no hard limits on file size." Instead, "workbook size is limited only by available memory and system resources."
There is a related detail for anyone still on 32-bit Excel. From Excel 2016, Microsoft says Large Address Aware functionality "lets 32-bit Excel consume twice the memory" on a 64-bit Windows operating system.
The published figures apply to Excel for Microsoft 365, 2024, 2021, 2019, and 2016.
What those limits mean in practice
The row ceiling gets quoted most and matters least. Very few teams hit 1,048,576 rows, and the ones that do have usually outgrown spreadsheets for other reasons already.
The limits that actually bite are quieter. A cell capped at 32,767 characters truncates long text fields silently on some import paths. A PivotTable capped at 256 report filters fails only after a report has grown for two years.
Memory is the real constraint. Sheets and file size are both bounded by available memory rather than a fixed number. The same XLSX file can therefore open fine on one machine and stall on another.
Three things inflate that memory cost more than row count. Pivot caches holding a duplicate copy of the source data, formatting applied to entire columns rather than used ranges, and formulas that reference whole columns.
Best practices for XLSX files other people will open
Keep one table per sheet. A sheet holding three stacked tables is hard for most tools to parse, and a human usually has to explain it first.
Put headers in a single top row. Merged or two-level headers break almost every downstream tool, and they are the most common reason an import returns nonsense.
Format the used range, not whole columns. Selecting column A to apply a fill is the fastest way to add megabytes to a file.
Do not hide state in the file. Hidden sheets, filtered views, and manual calculation mode all travel with the workbook and surprise the next person.
Send values when values are what is needed. If the recipient only needs numbers, exporting to CSV removes an entire class of problems.
Name things for outsiders. Column names are the interface, and the fastest fix for a confusing workbook is usually a short dictionary next to it.
Where XLSX stops being the right container
An XLSX file is a good document and a mediocre database. It holds one team's working copy well and breaks down as soon as several people need to change it at once.
The signals are consistent. Filenames grow version suffixes. Two people quote different totals. Someone maintains a tab that reconciles the other tabs.
At that point the file is not the problem to solve. The workflow around it is, and swapping in a bigger spreadsheet only postpones the reckoning.
Working with XLSX files without doing it by hand
Most of the work an XLSX file creates is not analysis. It is opening, scanning, reformatting, and rebuilding the same chart for the third month running.
Powerdrill Bloom takes that half. You upload the workbook and describe in natural language what you need out of it. Back comes a finished artifact: a summary, a chart, a deck, or an Excel analysis. Uploads for Excel, CSV, PDF, and docs are listed on the free plan, and Pro adds slides, Office documents, and Excel analysis.
Our Excel AI assistant page covers the direct route, and the Excel AI tools hub lists the narrower tasks. The make graphs from Excel page covers the charting side. If pivot tables are the part you would rather skip, summarizing Excel data without them walks through the alternative.
Understanding the format is what keeps you out of trouble. Handing the repetitive part to something else is what buys back the afternoon. Try Powerdrill Bloom on the messiest workbook you own.
Frequently asked questions
What does XLSX stand for?
It is the file extension for the Office Open XML spreadsheet format. The final X reflects that the package contains XML parts rather than the older binary layout.
How many rows can an XLSX file hold?
Microsoft publishes a worksheet limit of 1,048,576 rows by 16,384 columns. A workbook can hold multiple sheets, bounded by available memory rather than a fixed count.
Is there a maximum XLSX file size?
Not a fixed one. Microsoft states that a 64-bit environment imposes no hard limits on file size. Workbook size is bounded only by available memory and system resources.
Why is my XLSX file so large?
The usual causes are formatting applied to entire columns, pivot caches storing a second copy of the source rows, and images. Row count alone rarely explains it.
Should I use XLSX or CSV to share data?
Use XLSX when a person needs the structure, formulas, or multiple sheets. Use CSV when a system needs the rows and everything else is overhead.