LibreOffice Calc is the free and open-source spreadsheet in the LibreOffice suite, available for Windows, macOS and Linux. For most everyday uses it does the same things as Excel and opens and saves XLSX files. There are, however, differences in interface and behaviour that are worth knowing if you work with both programs or exchange files with people who use the other one. Here are the ones I come across most often.
File formats
Calc's native format is ODS (OpenDocument Spreadsheet), an open standard. Excel uses XLSX, also based on XML but with different specifications. Both programs read and write both formats, with a few caveats:
- save in the format of whoever receives the file. If the recipient uses Excel, send XLSX; if they use Calc or you want an open format for archiving, ODS;
- repeated conversions back and forth between the two formats accumulate small losses, especially in formatting;
- Calc warns you when you save in a non-native format: the message does not indicate a problem, it simply reminds you that some features might not be preserved.
Interface and command names
By default Calc uses traditional menus and toolbars; from the view settings you can switch on a tabbed interface similar to the Office ribbon. Some features have different names:
| Excel | LibreOffice Calc |
|---|---|
| Data Validation | Data, Validity |
| Goal Seek | Tools, Goal Seek |
| Name Manager | Sheet, Named Ranges and Expressions |
| Conditional Formatting | Format, Conditional |
| Flash Fill | no direct equivalent |
The pivot table has that name in both; in Calc it used to be known as DataPilot, a name still found in guides and forums.
Formulas and functions
The vast majority of common functions exist in both under the same name: SUM, IF, VLOOKUP, SUMIFS, INDEX, MATCH. A few differences worth knowing:
- Excel's newer functions, such as XLOOKUP, FILTER, UNIQUE and LET, have been added to Calc in recent versions; in older installations a file using them shows errors;
- some functions exist on one side only: Calc has several that Excel lacks, and vice versa. If a file must work in both, use the common functions;
- the argument separator: Calc always accepts the semicolon, whatever the regional settings; it is the safest choice even when writing formulas to share in a document;
- references to other sheets: Excel writes
Sheet2!A1, Calc in its native format uses$Sheet2.A1. In practice you need not worry, because conversion is automatic when you open or save a file in the other format; it only matters if you copy formulas as text from one program to the other.
Arrays and array formulas
In recent versions both handle formulas that return several values. In older versions of Calc, as in old Excel, an array formula has to be confirmed with Ctrl+Shift+Enter, and the program wraps it in curly braces. If you receive a file with formulas of this kind and edit them without the right key combination, the formula may stop working.
Macros
This is the sharpest point of incompatibility. Excel uses VBA; Calc uses its own LibreOffice Basic language, and also supports scripts in Python and other languages. Calc can run some VBA macros, but support is limited: macros that manipulate Excel-specific objects often do not work. If a file depends on macros, test it in the recipient's program before handing it over.
Charts and formatting
Basic charts (lines, bars, pies, scatter) convert well. Newer Excel chart types, such as some statistical or waterfall charts, may have no equivalent and be replaced or lost. Likewise, some advanced conditional formatting rules, slicers and table styles may change appearance. Fonts are a frequent cause of layout differences: if a font used in the file is not installed, the program substitutes another one with a different width.
Printing and PDF export
Calc has a very complete built-in PDF export, with options for image quality, bookmarks and protection. Excel exports to PDF too. In both cases it is worth checking the print preview: print areas and page breaks are the settings that most often change when moving from one program to the other.
A checklist before sending a file
In my work, when a sheet has to move from one program to the other, I always run these checks:
- open the file in the recipient's program, if possible;
- check that no #NAME? or other new errors appear;
- check the main totals against the original;
- look at the print preview;
- if there are macros, run them at least once.
In short
- Calc works in ODS but opens and saves XLSX; send the file in the recipient's format.
- Many commands have different names: Validity, Named Ranges and Expressions, and so on.
- Common functions are identical; recent ones need up-to-date versions.
- VBA macros are the main point of incompatibility.
- Before sending, open the file in the other program and check errors, totals and printing.
