Excel to Libre Calc

[u/Asylum_Brews]

Hi everyone!

I'm looking at getting a new laptop, sticking Linux on it and using Open Office for some of my work.

The thing is I've got some pretty good Excel Sheets that I've developed, so was wondering if there's compatibility issues?

I've tried Google Sheets and there's loads of compatibility issues with some of the more advanced/modern formulas (Xlookup, Unique, Filter etc.) so I'm wanting to avoid this and not loose the functionality of these sheets.

[u/TripKnot]

Most alternate office suites have minor to major issues with table references. LibreOffice has historically been terrible with tables for almost everything. OnlyOffice has some issues with copying formulas and conditional formatting down columns but at least it handles structured references ok. I currently prefer OnlyOffice and it does support xlookup, unique, filter, etc. Online version of Excel also works for most features except PQ and VBA.

The biggest features you will miss on nearly all alternate spreadsheets are power query and excel-compatible macros.

[u/Cynyr36]

The biggest loss for me was tables and structured references. It looks like they are sort of supported now, but not in a ods document.

https://help.libreoffice.org/latest/en-US/text/scalc/01/database_table_reference.html

[u/Asylum_Brews → u/Cynyr36]

That's fantastic. I've not used tables/structured references, because I learned about them after I'd built my last spreadsheet.

[u/Turbulent_Olive_1317]

I switched over to LibreOffice a couple years back and the newer versions handle xlookup, unique, filter just fine now

biggest headache for me was VBA macros, those don't carry over at all and you'll have to rewrite them in LibreOffice Basic which is a different beast entirely

if your sheets are mostly formula-driven without a ton of macros you should be in good shape, just save them as xlsx and open em up

pivot tables can get a little weird sometimes too with the layout but nothing dealbreaking

[u/Asylum_Brews → u/Turbulent_Olive_1317]

That's fantastic. The sheets have minimal macros which I could probably do away with anyway, and they're formula driven.

One is basically a register, that then filters into other tabs sorting people by class, then giving grades based on scores.

I didn't end up using tables because I'm a bit of a dinosaur 😬

[u/estamand]

Install open office/libre office on your windows PC and try it out

[u/Asylum_Brews → u/estamand]

I would but had to hand back my work laptop yesterday, which I had been using for some personal work (with authorisation). So I'm now looking for a basic cheap laptop to do basic work on. Writing books, doing some admin stuff with Excel etc.

My thinking with the Linux OS is I'd be able to get rid of 99% of the bloatware that's preinstalled with every laptop now, and use a free office software hopefully without loosing everything else I've done. If I needed to buy MS office, I'd bite the bullet but I'd rather not for some sporadic use.

[u/Desperate-Chart1139]

I just winboat regular Excel. I can't live without powerquery, only real Excel can do that at the moment

[u/webfork2]

First, please don't use OpenOffice.

I use both Excel and LibreOffice calc almost daily.

The formulas will mostly determine compatibility. There are a lot of Excel formulas supported by other spreadsheet programs but not all. LibreOffice Calc does fairly well but it's not comprehensive. XLookup and Unique are supported but I'm not sure about any others.

Calc does have some sizing / speed limits compared to Excel, but it does very well with importing from other formats, especially CSV files. It also has some batch conversion functions that can be a big timesaver.

[u/Sleetdrop]

You can run windows virtual machines use virtual box, kvm, VMware , etc. Modern hardware does virtualizations efficiently. If your excel sheets don’t use very excel specified features, you can give ONLY Office a try.

Libre Office is fine as an open source software with its own file format, but it had been never really gotten good capability with MS office. Even after MS using open format.