Convert XLS to ODS

The .xls format is genuinely old. Microsoft introduced it in 1987 and it remained the default until 2007, which means a workbook in it may predate almost every tool in your organisation. It is a binary format with a documented specification, and reading it correctly requires software that has been taught its layout record by record.

OpenDocument went the other way. OASIS published it in 2005, it became ISO/IEC 26300, and an .ods file is a ZIP archive containing XML — you can rename it, open it and read the contents with nothing more specialised than a text editor. That difference is the whole case for the conversion when the horizon is long: a file whose structure is written down in a public standard does not depend on any vendor continuing to care about it.

You can, and for one spreadsheet you should. LibreOffice Calc reads the old format directly and has done for two decades, so nothing is stopping you from double-clicking the file and getting on with the afternoon.

Converting is about what happens afterwards. Every time that file is saved, LibreOffice asks whether to keep the foreign format or use ODF, and every colleague answers differently. Formatting that has no representation in the old format is quietly discarded on each round trip, and the organisation ends up with a library of files it can open and does not own. If the decision has been made to standardise on OpenDocument, converting once is what makes that decision true on disk rather than in a policy document.

The formulas come across as formulas. ODS stores them in OpenFormula, the expression language specified alongside the document format, and the conversion translates Excel’s syntax into it — the argument separators, the reference style, the range notation and the sheet qualifiers all differ slightly and are all handled.

That means the workbook keeps working rather than becoming a set of frozen numbers. Named ranges, absolute and relative references, cross-sheet references and array formulas survive the trip in the ordinary case. What is worth doing on a workbook that matters is opening the result and pressing recalculate, then comparing a few totals against the original — five minutes of arithmetic is a much stronger check than reading a list of what is supposed to work.

The standard library — the maths, text, date, lookup and financial functions — maps across, because both applications implement essentially the same set and have done for years. The gaps are at the edges.

Two kinds cause trouble in an old workbook specifically. The first is an Analysis ToolPak function from the era when those were an add-in rather than part of Excel, which may have been stored with a prefix and no longer resolves anywhere. The second is a user-defined function written in VBA, which will not exist after the conversion because its code does not come with it: the cell keeps the formula text and returns an error. Both are easy to find and impossible to miss — search the converted sheets for error values and every one of them is a place that needs a decision.

Macros do not survive. VBA and LibreOffice Basic are different languages with different object models, and no converter attempts a translation, because a partial one would produce code that runs and does something other than what it used to.

For a migration this is a known cost rather than a surprise, and it is worth taking stock of before the conversion rather than after. Most old workbooks contain either no macros or a recorded one that formats a report, and a recorded macro is usually easier to rewrite than to port. The genuinely difficult cases — a workbook that has become an application, with forms and event handlers — are the ones to identify early and treat as software projects rather than as files to convert.

Charts convert and stay charts, redrawn by Calc from the underlying data rather than pasted in as pictures, so they update when the numbers do. Cell formatting, number formats, borders and column widths come across; conditional formatting comes across in the ordinary cases and is worth checking on a workbook that leans on it heavily.

Pivot tables become Calc’s equivalent, which it calls a data pilot, and this is the feature most likely to need attention afterwards. The two implementations differ in how they store their layout and their grouping, and a pivot with several grouped date levels or a calculated field can come through needing to be rebuilt. Rebuilding one takes a few minutes and is a better use of them than trying to work out why a total is off by a rounding.

The binary format has hard structural limits that the OpenDocument format does not share, and a workbook that has been living close to them has probably been shaped by that fact for years — data split across sheets, columns reused for two purposes, a summary tab that exists because the detail would not fit.

Converting removes the constraint but does not undo the workarounds, and it is worth a look at any file where somebody has clearly been fighting the format. A migration is the one moment when consolidating three sheets back into one is cheap, because everything is being touched anyway and nobody has yet built new work on top of the converted file.

Migrations are never one file. The practical approach is to sort by what depends on what: convert the workbooks that nothing links to first, check them, and only then convert the ones that reference them, because an external link rewritten while its target is still in the old format is a link you will be fixing twice.

Convert copies and keep the originals until the migration is signed off. The old files cost nothing to keep, they are the only evidence of what a formula used to be, and the one thing guaranteed to be discovered late is a workbook that somebody outside the project has been quietly depending on. Working through them in batches, with a check after each batch, finds a systematic problem on file three instead of on file three hundred.

This conversion does not run in your browser. Translating between two office formats needs a real office engine, and that runs on a server: the file is sent over an encrypted connection, converted in an isolated environment with no route out to the internet, and the working files are deleted as soon as the job finishes.

The free limit is 25 megabytes per file, which an .xls will rarely approach — the format predates the habit of embedding large images, and its own structural ceilings cap how much it can hold in the first place. Where the spreadsheets cannot leave your organisation at all, LibreOffice installed locally does exactly the same conversion, and on a migration of any size you will want it anyway for the batch handling.

If the file is going back to somebody using Excel, send an XLSX instead. ODS opens in current Excel but the support is a translation rather than a native path, and a recipient who has to explain to their IT department why they are opening an unfamiliar format will ask you for something else instead.

Choose ODS when the destination is LibreOffice, when an open-format policy applies, or when the file is being archived and the question is which format will still be readable in twenty years. Those are the cases where the ISO specification and the readable XML inside the file are worth more than compatibility with whatever the recipient happens to have installed today.