What Is Inside an Excel File and What It Means for Data

An Excel file looks like a grid, and most people treat it as one: rows, columns, values. A program that reads the file sees something different. It sees a package of XML documents in which the grid is only one part, where a cell may hold a pointer to a string stored elsewhere, where a date is a number in disguise and a formula is a recipe plus a remembered answer. Nearly every Excel data problem you will meet comes from the gap between the grid and the package: the zip code that lost its leading zero, the date that arrives as 45292, the total that is empty when a script reads it, the column that is text in one row and a number in the next.

This guide opens the package and explains what is in it, in the order that matters for people who move data in and out of spreadsheets. It is the map for the Excel cluster on this site. Each section points to a focused article, and the later clusters on loading Excel data into a database, sending database results to Excel, openpyxl, pandas, dates and types and translating formulas into SQL build on it.

A workbook is a zip file

Rename report.xlsx to report.zip and open it with any archive tool. It opens, because an XLSX file is a ZIP archive. Inside are folders and XML files arranged according to the Open Packaging Conventions, a standard for bundling the parts of a document that Microsoft Office, and several other formats, share. A small workbook contains roughly this:

[Content_Types].xml
_rels/.rels
docProps/core.xml
docProps/app.xml
xl/workbook.xml
xl/_rels/workbook.xml.rels
xl/worksheets/sheet1.xml
xl/sharedStrings.xml
xl/styles.xml
xl/theme/theme1.xml

Each file has a job. [Content_Types].xml declares what type each part is. The _rels folders hold relationships, which tell a reader that sheet 1 is the file at xl/worksheets/sheet1.xml. xl/workbook.xml lists the sheets with their names, visibility and the date system. xl/worksheets/ holds one XML file per sheet with the cell data. xl/sharedStrings.xml holds the text values. xl/styles.xml holds fonts, fills, borders and, importantly for data, the number formats. Larger workbooks add folders for tables, charts, drawings, images, comments, defined names and a calculation chain.

The practical consequence is that nothing about the file is hidden. If an import tool misreads a value, you can unzip the file and look at the XML the tool read. The article an XLSX file is a zip of XML walks through doing exactly that, and a short script gives a quick inventory:

import zipfile

with zipfile.ZipFile("report.xlsx") as z:
    for info in z.infolist():
        print(f"{info.file_size:>10}  {info.compress_size:>10}  {info.filename}")

The two size columns show the uncompressed and compressed size of each part. For a workbook with a lot of data, the sheet files dominate. For one that is oddly large, a media folder or a huge styles.xml is the usual explanation, as covered in why Excel files get huge.

The older .xls format is not a zip and not XML. It is a binary format from before 2007, and it has tighter limits. The differences between the three formats you will meet, .xls, .xlsx and .xlsb, are in XLS versus XLSX versus XLSB.

What a sheet stores

Open xl/worksheets/sheet1.xml and you find a sheetData element that holds rows, each holding cells. Here is a minimal example with a header and two data rows:

<worksheet>
  <sheetData>
    <row r="1">
      <c r="A1" t="s"><v>0</v></c>
      <c r="B1" t="s"><v>1</v></c>
      <c r="C1" t="s"><v>2</v></c>
    </row>
    <row r="2">
      <c r="A2" t="s"><v>3</v></c>
      <c r="B2"><v>19.99</v></c>
      <c r="C2" s="1"><v>45292</v></c>
    </row>
    <row r="3">
      <c r="A3" t="s"><v>4</v></c>
      <c r="B3"><v>5</v></c>
      <c r="C3" s="1"><v>45293</v></c>
    </row>
  </sheetData>
</worksheet>

A cell is a c element with a reference r such as B2, an optional type t, an optional style index s, and a value v. Three things deserve attention.

First, only cells that hold something are written. An empty cell has no element at all, so the file does not store a rectangle. A reader must decide what to do about the gaps, and different libraries return None, NaN or an empty string. Row elements are skipped the same way when the whole row is empty.

Second, the type attribute is small. With no t, the value is a number. t="s" means the value is an index into the shared strings table. t="b" is a boolean, t="e" an error such as #DIV/0!, t="str" a string that came from a formula, and t="inlineStr" a string stored right in the cell, which some writers prefer. There is a t="d" for ISO dates that Excel itself rarely writes. There is no text type separate from strings, and there is no date type.

Third, the style index s points into styles.xml. The date cell C2 holds the plain number 45292 and a style with index 1, and that style says "format as a date". Strip the style and the cell is a number.

Everything a program reads from a sheet comes from these elements. The article cheat sheet of Excel data types as programs see them lists each case with what openpyxl and pandas return for it.

Text lives in a shared table

The sample above shows <v>0</v> for the header cells, with t="s". The zero is not a value; it is a position in the shared strings table:

<sst count="5" uniqueCount="5">
  <si><t>product</t></si>
  <si><t>price</t></si>
  <si><t>ordered</t></si>
  <si><t>notebook</t></si>
  <si><t>pen</t></si>
</sst>

Each unique string is stored once, and each cell that uses it points to its index. A column of one hundred thousand rows with ten distinct values stores ten strings and a hundred thousand small numbers. That saves space, and it is why a reader must load the whole shared strings table before it can interpret any cell. It is also why streaming readers need care: they hold the strings in memory even when they stream the cells (reading large Excel files in read-only mode).

A string that has partial formatting, such as one bold word, is stored as rich text with several runs. Most data libraries flatten it to plain text and discard the formatting. A string can hold up to 32,767 characters, and a cell that holds more is truncated by Excel on save.

One more consequence: text that looks like a number is still text if the cell says so. The zip code 01234 typed into a cell formatted as text is stored as a shared string, and it stays 01234. Typed into a general cell, Excel converts it to the number 1234 on entry, and the zero is gone before any file is written (keeping leading zeros).

Numbers are doubles with a display format

A number cell stores an IEEE 754 double-precision value. This gives about 15 to 17 significant decimal digits, and Excel displays and keeps only 15. Anything beyond is rounded when the number is typed. A 16-digit credit card number typed as a number loses its last digit to zero, and nothing recovers it. Large identifiers belong in text cells (Excel number precision and the 15 digit limit). Integers are exact up to 2 to the power 53, which is 9,007,199,254,740,992, but the 15-digit display and entry limit applies first.

Integers and decimals are the same type. The value 5 and the value 5.0 are one number, stored as 5. A reader library decides whether to hand your code an integer or a float, and the choice differs. openpyxl returns an int when the stored text has no decimal point and a float otherwise; pandas often returns floats for a whole column if any cell has a decimal or is empty. A column of ids with one missing value arrives as floats such as 17.0 unless you ask for a nullable integer type.

Percentages, currency and scientific notation are all formats on top of the same number. A cell shows 45% while the stored value is 0.45. A cell shows $1,234.50 while the stored value is 1234.5. The format lives in styles.xml, and a program that reads the value gets 0.45 and 1234.5, which is usually what you want. It is a surprise only when you compare a screenshot with the output.

Dates are numbers

In the sheet XML above, C2 holds 45292 with a style. Excel stores a date as the number of days since an epoch, with the time of day as the fraction of a day. The value 45292 is 1 January 2024 in the default Windows date system. The value 45292.5 is noon on that day. The number format on the cell, a custom format like yyyy-mm-dd, makes it display as a date. If you change the format to General, the cell shows 45292.

This matters whenever data leaves Excel. A reader that does not look at the number format sees only a number, and your table gets a column of five-digit integers. Libraries differ: openpyxl checks the format and returns a Python datetime for cells whose format looks like a date, while a raw read of the XML gives you the number. When a date column arrives as numbers, the fix is a conversion, covered in how Excel stores dates as serial numbers, with versions for SQL and Python.

Two further details shape every conversion. The workbook has a date system flag in workbook.xml, date1904, which shifts every date by 1,462 days when set. Files created in very old versions of Excel for Mac use it (Mac and Windows date systems). And Excel treats 1900 as a leap year, which it was not, so serial numbers before 1 March 1900 are off by a day compared with the real calendar (the 1900 leap year bug). For dates after February 1900, a conversion that counts from 30 December 1899 is correct.

Formulas keep a recipe and an answer

A formula cell stores both:

<c r="D2"><f>B2*C2</f><v>99.95</v></c>

The f element is the formula text, without the leading equals sign. The v element is the last value Excel calculated, the cached value. A program that reads the file normally gets one or the other depending on how it is configured. pandas reads the cached values. openpyxl returns the formula text unless you open the workbook with data_only=True, in which case it returns the cached value (reading only the values and not the formulas).

The cached value is only as fresh as the last time the file was saved by a program that calculates. A workbook written by a script that adds formulas has no cached values at all, since the script does not evaluate them. Open the file in Excel, save, and the values appear. Until then, anything that reads cached values sees empty cells in every formula column. The explanation, with the consequences for pipelines, is in what happens to formulas when code reads an Excel file, and the handling in pandas in formulas and cached values in pandas.

Formulas can also refer to other workbooks, volatile functions that change on every open (NOW, RAND) and ranges that grow. Treat a formula column as a derived column whose value depends on when it was last calculated, and when exactness matters, recompute the numbers yourself in SQL or Python from the inputs.

Styles decide what you see

xl/styles.xml holds the number formats, fonts, fills, borders and cell style definitions. The sheet cells carry only an index. For data work the number formats are the part that matters: they are the reason a number looks like a date, a percentage, a currency or a phone number. A style can also hide a value completely: the format ;;; displays nothing at all while the value remains in the cell. A white font on a white fill does the same.

A direct consequence: what you see can differ from what you read, on purpose. A cell showing 1,235 may hold 1234.567. A cell showing 12/01/2024 may be 1 December or 12 January depending on the locale, and the stored number is the same either way. When you reconcile numbers between a spreadsheet and a database, compare the stored values and not the displayed ones.

Conditional formatting, data validation drop-downs and cell comments are stored in the sheet XML or in separate parts. Libraries ignore most of them by default. If a person encodes meaning in color, such as a red row for a canceled order, that meaning is lost in an import, because the color is a style and the style is not data. Ask for a column.

Merged cells, hidden rows and other structure

A sheet may contain structure that has no equivalent in a table. Merged cells are stored as a list of ranges:

<mergeCells count="1"><mergeCell ref="A1:C1"/></mergeCells>

Only the top-left cell of a merged range holds a value. The other cells are empty in the XML. A report with a merged title across the top, or a merged region label that spans several rows, imports with blanks where the person saw one big cell. Fill the merged value downward or across before analysis (merged cells, hidden rows and other things that break imports, and handling merged cells with openpyxl).

Hidden rows and columns still exist in the data, so an import brings them in. A person who hid 40 rows to tidy the view may not realize that the totals in your table include them. Filters work the same way: the filter hides rows and leaves them in the file. Sheets themselves can be visible, hidden or "very hidden", the last of which the Excel interface does not list. A pipeline that reads "all sheets" reads those too.

Title rows, subtotals, blank spacer rows and notes at the bottom are not structure the format enforces; they are habits. A sheet is free-form, and a table with a header on row 1 is a convention. Treat every sheet as unstructured until you have looked at it. The import articles show how to cope: handling title rows above the header and skipping header junk with pandas.

Tables and named ranges

Excel has two features that bring real structure. A table, created with "Format as Table", is stored in its own part as a named range with a header row, column names and a defined extent. Reading a table by name avoids guessing where the data ends (reading Excel tables by name with openpyxl). A defined name labels a cell or a range, such as TaxRate or Orders, and formulas can use it. Both are metadata in workbook.xml and the table parts, and neither is visible to a reader that only walks the cells. The explanation for developers is in Excel named ranges and tables.

If you control the file, ask for a table. It makes the import code shorter and removes the guess about where the data stops.

Limits worth remembering

An XLSX sheet holds at most 1,048,576 rows and 16,384 columns, which is the range from A1 to XFD1048576. That is about 17.2 billion cells in theory, far more than memory allows in practice. The older XLS format allowed 65,536 rows and 256 columns. A cell holds up to 32,767 characters, a sheet name up to 31 characters, and a number keeps 15 digits. Row and column limits matter when you export a large query: rows past the limit are lost or the write fails (Excel row and column limits and how to work around them). If your table is bigger than a million rows, Excel is the wrong container; use CSV or a database, or a columnar format such as Parquet.

The limits that surprise people are the practical ones. A workbook with a few hundred thousand rows and many formulas opens slowly and may run out of memory on 32-bit builds. A file with a million rows takes tens of seconds to parse in most libraries. Those figures depend on the hardware and library, and the right response is to measure with your own file.

Excel versus CSV, in one comparison

CSV is plain text with no types, no styles and no formulas. Excel is a rich container with all of those. For moving data between systems, CSV's poverty is an advantage: what you write is what the other side reads, and no cell can hide a formula or a merged region. Excel wins when the audience is a person who will filter, format and annotate. The decision, with the failure modes of each side, is in Excel versus CSV for sharing data. A short version: send CSV to machines and XLSX to people, and when a machine must read an XLSX, ask for a table on one sheet.

Reading a workbook in code

Every Python library on this site reads the same package, with different defaults:

• openpyxl is the reference reader and writer for XLSX. It gives you cell-level access to values, formulas, styles, merged ranges and tables (reading and writing Excel files with openpyxl).

• pandas wraps a reader engine and gives you DataFrames in one call, which is the shortest route for tabular sheets (working with Excel in pandas).

• DuckDB reads XLSX through its Excel extension, which is useful for querying a sheet with SQL without a Python step (loading Excel files with DuckDB).

• Power Query inside Excel reads other files into Excel with a repeatable recipe (Power Query for repeatable Excel cleanups).

For loading into a database, the order of operations is nearly always the same: read the sheet as text where you can, stage it in a table with permissive types, check counts, then convert into the final table (staging Excel data in a table, validating row counts after an Excel import).

How a reader turns a cell into a Python value

A library that reads a sheet applies the same small decision list to every cell, and knowing the list explains most surprises. If the cell has t="s", look up the string. If it has t="b", return a boolean. If it has t="e", return an error marker, which pandas converts to NaN and openpyxl returns as the error text such as #N/A. If it has no type, it is a number, and now the library checks the style: when the number format is a date or time format, it converts the serial number into a date or datetime, and otherwise it keeps a number. A formula cell goes through the same steps using its cached value, unless the library was asked for formulas.

Two outcomes follow. The first is that the date decision depends on the format string, and custom formats that do not look like dates to the library leave the serial number as it is. A column that shows Jan-24 is read as a date; a column formatted with a vendor-specific pattern may not be. The second is that a column's final type is chosen after the cells are read. pandas inspects the values of a whole column and picks one type: integers with a missing value become floats, a column mixing numbers and text becomes object, and a column whose cells are all dates becomes a datetime column. Mixed columns are the frequent cause of "it worked for last month's file" failures, and detecting mixed types in an Excel column before import shows how to find them first.

The remedy is the same in every case: decide the type yourself. Read identifiers as text, name the date columns you expect, and convert at a point where a failure is loud. Specifying column types in read_excel shows the syntax, and the staging approach in the database articles moves the same decision into SQL.

A first look at an unfamiliar workbook

When someone sends a workbook and asks you to load it, spend five minutes before writing code:

1. List the parts with the script above. Look for media, vbaProject.bin (macros), externalLinks and many sheets.

2. Open xl/workbook.xml and check the sheet names, any state="hidden" attributes, and date1904.

3. Open the first sheet and look at the header row and the first few data rows, as the program will see them.

4. Search the sheet XML for <f>: count the formulas and decide whether you need values or recipes.

5. Search for mergeCell to see whether merged regions exist.

6. For any date-looking column, look at the cell's style and confirm it is a date format, not text.

7. Compare the row count of the XML with the count the sender expects.

Doing this once teaches you more about a file than ten trial imports. After a while you do it from memory, and the odd file stops being a surprise.

When you do not need to know what is inside the file

You exchange simple, well-formed data. If both sides agree on one sheet, a header row, text for identifiers and ISO dates, a library's defaults work and the internals never matter. The guide pays off when the defaults fail.

You can ask for CSV. If the source system can export CSV, take it. You trade styles and formulas for a format you can read with head, and the trade is usually in your favor (saving an Excel sheet as CSV).

You use a managed import. Some tools, such as a database's import wizard or a BI product's connector, read the package for you. When they misread a column, you will be back here, but while they work, there is nothing to learn.

The file is a report, not data. A formatted summary with subtotals, charts and notes is meant to be read by a person. Request the source data instead of parsing the report.

The format is specified in ECMA-376, Office Open XML, and Microsoft documents the product's constraints in Excel specifications and limits. To move a small sheet's data into JSON for inspection, try the CSV to JSON converter.