What is an XML spreadsheet (Excel 2003 XML)?
Short answer
An XML spreadsheet, formally XML Spreadsheet 2003, is a single plain-text XML file that stores a workbook using Microsoft's SpreadsheetML 2003 schema. Excel 2003 introduced it as an alternative to binary XLS. Current Excel versions can still open and save it, and LibreOffice can too. It is human-readable but larger and less capable than XLSX.
XML Spreadsheet 2003 is a text format for workbooks. Unlike XLSX, which is a ZIP package of many XML files, an XML spreadsheet is one file containing one XML document. Microsoft called its schema SpreadsheetML. That name is confusing, because the XLSX format uses a different, newer schema that is also called SpreadsheetML. The two are not compatible.
What does a minimal XML spreadsheet look like?
The root element is Workbook, in the namespace urn:schemas-microsoft-com:office:spreadsheet. A minimal file with one sheet and three cells looks like this:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="Sheet1">
<Table>
<Row>
<Cell><Data ss:Type="String">Item</Data></Cell>
<Cell><Data ss:Type="Number">42</Data></Cell>
<Cell ss:Formula="=RC[-1]*2"><Data ss:Type="Number">84</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
Each Cell holds a Data element with a type of String, Number, DateTime or Boolean. Formulas are written in R1C1 notation: RC[-1] means the same row, one column to the left. Styles are defined once in a Styles block and referenced by ID. The mso-application line tells Windows that the file belongs to Excel.
Which programs can open an XML spreadsheet?
- Excel: File > Open and select the file, or Save As and choose XML Spreadsheet 2003 (*.xml). Current versions of Microsoft 365 still list this type.
- LibreOffice Calc: opens it and offers Excel 2003 XML (.xml) as a save type.
- Text editors and scripts: any language with an XML library can read or write it, with no spreadsheet library.
Double-clicking an .xml file often opens a browser or text editor rather than Excel, because .xml is not tied to a spreadsheet program. Use File > Open from inside the spreadsheet app.
Why do people still use the Excel 2003 XML format?
- It is easy to generate. A server can write the XML with string templates and Excel will open it with formatting, multiple sheets and formulas.
- It is human-readable and works with ordinary diff and version-control tools.
- Some older systems and export tools still produce it.
How do the limits of XML spreadsheets compare with XLSX?
- It is verbose. Every cell carries several tags, so files are much larger than the compressed XLSX equivalent.
- The 2003 schema predates features such as tables, modern conditional formatting and the current chart engine. Excel 2003 itself had a limit of 65,536 rows and 256 columns.
- Microsoft treats it as a legacy format. New workbooks should normally be XLSX.
- Support outside Excel and LibreOffice is limited, so check the format with the receiving program before relying on it.
What goes wrong when generating or opening an XML spreadsheet?
- A file that is not a spreadsheet XML, such as a plain data export, makes Excel ask how to open it, for example as an XML table. That is a different feature.
- Formulas in R1C1 style are easy to get wrong when generating the file by hand. Test a few cells after opening.
For a general comparison of the formats, see which spreadsheet format to use.