Sheet ReserveSpreadsheets worth keeping.

Formats

What is an ODS file?

Short answer

An ODS file is an OpenDocument Spreadsheet, the native format of LibreOffice Calc and Apache OpenOffice Calc. It is a ZIP package of XML files defined by the OASIS OpenDocument standard, also published as ISO/IEC 26300. Excel, Google Sheets and many other programs can open and save ODS, though advanced features may not carry over.

Updated

ODS stands for OpenDocument Spreadsheet. It is the spreadsheet member of the OpenDocument Format (ODF) family, which also includes .odt for text documents and .odp for presentations. ODF was standardized by OASIS in 2005 and published as ISO/IEC 26300 in 2006. Later versions, ODF 1.2 and 1.3, were also approved by OASIS, and 1.2 was adopted by ISO/IEC.

What is inside an ODS file?

Like XLSX, an ODS file is a ZIP archive of XML. The main parts are:

Part What it holds
mimetype A one-line text file, application/vnd.oasis.opendocument.spreadsheet, stored first and uncompressed
content.xml The sheets, cell values and formulas
styles.xml Named styles, page layout and number formats
meta.xml Author, creation date and statistics
settings.xml View settings such as the active cell and zoom
META-INF/manifest.xml A list of every file in the package

The mimetype file lets other programs identify the document type from the first bytes of the archive without parsing anything.

There is also a single-file, uncompressed variant called Flat ODF, with the extension .fods. It is one XML document, which is easier to inspect or generate by script, but larger.

How are formulas stored in an ODS file?

ODF defines its own formula syntax, called OpenFormula. In the file, a formula appears with an of: prefix and bracketed references, for example of:=SUM([.A1:.A3]). Programs translate this to their own syntax when they open the file, so =SUM(A1:A3) looks the same in Calc and Excel.

Which programs can open and save ODS files?

  • LibreOffice Calc and Apache OpenOffice Calc: native format.
  • Microsoft Excel: opens ODS from File > Open and saves it through Save As, choosing OpenDocument Spreadsheet (*.ods).
  • Google Sheets: imports ODS with File > Import and exports it with File > Download > OpenDocument format (.ods).

When should I choose ODS instead of XLSX?

ODS is a good choice when the recipient uses LibreOffice, when you want an open standard that no single vendor controls, or when a government or institution asks for ODF. For general sharing with Excel users, XLSX is usually the safer default. The format comparison on the blog covers the differences in more detail.

What can go wrong when converting an ODS file to XLSX?

  • A round trip between ODS and XLSX can change things. Fonts, column widths and number formats usually survive, but macros, pivot tables, some chart types and some conditional-formatting rules may be altered or dropped.
  • Newer functions may not exist on the other side. A file that uses XLOOKUP in Excel will show an error in a LibreOffice version older than 24.8.
  • Unlike CSV, ODS stores data types and the text encoding in the file itself, so it does not suffer from delimiter or encoding guesswork.

To check what a conversion changed, open both versions side by side and compare totals in a few formula cells rather than assuming the file is identical.