Alternative to Office: Microsoft Excel Formulas Using LibreOffice Calc

OK so I'm farming for some keywords with that title, but I'm not actually joking at all! I'm very serious about this open source Software whose code is publicly available and modifiable. Transparency, collaboration, and often free as in free lunch are important parts of the open source model. tool, and I rely on it almost everyday. I say almost because I don't write documents or spreadsheets everyday.

If you are interested in saving money by switching to LibreOffice, you need to know if the Excel formulas transfer to this free open source Software whose code is publicly available and modifiable. Transparency, collaboration, and often free as in free lunch are important parts of the open source model. tool.

What is open source?

The test

I ran a comprehensive test of 20+ common Excel formulas by creating a spreadsheet in Microsoft Excel online, then opening that same file in LibreOffice Calc to see if the formulas WORK and if they TRANSFER.

We get both! Except one formula: TEXTSPLIT.

If you're using the ODS (open document standard) file format, TEXTSPLIT won't transfer and it's not available.

The LibreOffice documentation makes it sound like it might be available if the file is Excel format, but I wasn't able to get TEXTSPLIT to work if I worked in LibreOffice with an Excel xlsx file format either.

See below for a summary table of what worked and what didn't.

Formulas in spreadsheets

I use spreadsheets for budgeting and tracking and various other things, but I know others who need more from their spreadsheets, like lots of formulas and reliability. So I wanted to double check how well the formulas transfer. Almost all the ones I checked do! There was one single formula that didn't.

The one caveat I'll give is that I haven't tested macros.

Most formulas transfered flawlessly - IF, SUM, AVERAGE, MAXIFS, MINIFS, SUMIFS, TEXTJOIN, MID, LEFT, RIGHT, MIN, MAX, MATCH, LEN, DATE, ROUND, STDEV, COUNTA, NOW, TODAY, HOUR, YEAR, DAY, EOMONTH, RAND all produce identical results between Excel and LibreOffice Calc.

Conclusion

As you can see in the comparison image, Excel and LibreOffice Calc are very similar. A funny example of this is I helped my partner with something this morning, and I got on his case for using Excel, "Why aren't you in Libre Office? What is this garbage??" But it wasn't excel he had open, it was Calc! His screen is bigger, so the ribbon is also bigger.

You can set up the ribbon-style navigation that you might be used to in LibreOffice tools. Check out my post showing how

LibreOffice is free and open source. So this saves money over subscriptions for a product that looks and behaves like what you're familiar with. If you pair LibreOffice with the right Linux An alternative to Windows or MacOS on computers. distro Bundles the Linux kernel with other software components for a complete operating system , then you can have the peace of mind that someone else isn't looking over your shoulder spying on your files.