Compatibility differences between Calc and Excel

[fiat_lux]

I’m looking to train some people in Calc, who are Excel users. I would like to know a) which settings in Calc to use to be most like Excel, and B) what other differences there are.

For a) I see Formula to use Excel formula syntax. Is there anything else?

For b) I see Differences in Use between Calc and Excel - Apache OpenOffice Wiki which seems out of date.

EDIT: some additional details–
This is for USA users who have many personal xlsx files they would like to start using in Calc. They do not use macros/VBA, or highly advanced features. I understand the UI is different, and that recent, Excel 2016+ functions may not work. I am looking for differences they should know about, or settings to use to ease compatibility.

[Zizi64]

About the formula syntax: The LO has a lot of localized versions. And because the comma (,) is the decimal separator in a few languages, it is better to use the native semicolon ( separator in the formulas.

About the user interface: The LO has a normal (old fashioned) menu-toolbar view, and a SideBar, and a Ribbon-like toolbar lookout. You can choose it, what you want to use.

About the document types. There is not (never was and never will be) 100% compatibility between the different file types. Always work in the native ODF (.ods) file formats. (Save a copy into the foreign file format at the end of editing - if it is necessary. You may will lose some formatting properties.)

About the VBA: The StarBasic+API and the Excel VBA are basicly different tools, therefore they are not compatible. (The LO can run some VBA code, but can not run all of them. And the Excel can not handle any LO API function.) You must rewrite all of VBA macros if you want to use them efficiently in the LO.

[gabix]

to be most like Excel

Use Excel. That’s it. Why would you use Calc if like Excel is a priority?

If the users really would like to start using Calc, they should just do it. They are the only ones who will find out what differences are significant to them.

[Lupp]

If I did what @fiat_lux is preparing, I surely had in mind to show users of low sensibility how much they can get from the FLOSS cosmos, and how little -if any- advantages a software can offer that allowed a small group to earn $s by the manytimes 10^9 based on marketing tricks, on unfairly fighting (by not correctly supporting them) open standards and compatibility, on misleading users to believe “commercial” and VBA is “the real thing”…
In fact I even feel a bit unhappy to not have any Excel at hand, because I otherwise might better be able to help others with projects of “enlightening” software users concerning FLOSS.
Open free software like LibreOffice will probably not survive if we don’t try here and there to widen its community. How (e.g.) can we get public institutions (governmental, municipal,…) to stop feeding the monster if not by spreading the message?

[Lupp]

(continued)
@fiat_lux: It seems, however, you probably aren’t sufficiently well prepared. If you try to base a kind of course on compatibility alone, there may be a flop. Training people always is and should be also teaching and a kind of “spreading wisdom”. If you try it based on tools you don’t actually know in detail, you may fail - and the effect will then not be zero, but negative.

[Zizi64]

The AOO/LO Calc applications are not Excel clones. The LO Calc has a littlebit higher compatibility with the Excel than the AOO Calc has (improved cell functions, VBA-compatibility, etc…), but there is not 100% compatibility.

[igorlius]

Hello,

maybe this is helpful for you: https://wiki.documentfoundation.org/Feature_Comparison:LibreOffice-_Microsoft_Office

Its a long table and many things might be of low or no interest, but its a start.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

[Lupp]

Why “a bit dated”? At least it claims to be based on LibreOffice V7.0 which is the most recent released version. (Today=2020-08-24)

[igorlius]

I must have misread the timestamp, i’ll edit my answer. Thanks.

[keme]

Microsoft office encourages an intuitive approach, while LibreOffice encourages a structured/standards compliant approach to solving tasks. This makes it easier to get started with the MS suite, but once you gain experience and start working on larger projects, you will see that the structured approach allows you to work more efficiently and make more consistent documents.

Introducing users to calc by making it more like Excel is a bit like introducing drivers to SUVs by making them more like a sedan. An SUV can do most of what a sedan does. Often more efficiently and sometimes not as elegantly, but it gets the job done. Better emphasize the advantages of an SUV, instead of dumbing it down to a “sedan with benefits”.

Yes, there are cases when something is lost when you work on Excel files using Calc. Most of the time content is preserved, but visual design may suffer. No dumbing down of Calc will improve this. If you want to introduce Calc, you have to be honest about it. Admit that something is lost, then proceed to demonstrate what may be gained.

[fiat_lux]

These are some fairly bold claims, but lack specifics…can you point to some examples of the “intuitive” and “dumbed down” vs. structured" and “standards” approaches?

[keme]

Standards: LibreOffice defaults to storing files using the iso certified odf file formats, while MS office defaults to a constantly extending “superset” of the ooxml office file format (known as the “transitional ooxml”). The constant change means that only ms apps can predictably support all ms-formatted files.

Structured: The page formatting is less smooth in Writer compared to Word, but the added abstraction of page styles makes it easier to have consistently repeating page layouts interspersed across your document. The same style concept appears in Calc compared to Excel.

“Dumbing down” is the first outcome when you make Calc behave more like Excel. You can easily remove a few (useful, but different, and possibly intimidating) abstractions, but adding funcionality towards Excel needs a significant effort.

As for “bold claims”: Sedans and SUVs (or a station wagon if you will) are both legitimate means of transportation. Some like the smooth experience and some need the workhorse.

[fiat_lux]

@keme
That was not really a very helpful reply (not trying to be rude.)

1. ODF 1.3 is not ISO certified either, I believe…and the inner details of file formats are not really user facing, and not relevant to the above.

2. Comments about Word/Writer are not relevant to a question asking about Calc/Excel…style editing in Calc/Excel I am not sure what you referring to…they are similar, except Excel has a much nicer GUI to select styles, instead of a pulldown.

3. Another general comment about Calc/Excel with no specifics…no idea what “You can easily remove a few (useful, but different, and possibly intimidating) abstractions, but adding funcionality towards Excel needs a significant effort.” refers to…why not say exactly what those are?

[keme]

Yes you are right, I was a bit preoccupied, my own agenda and forgetting the real issue of Calc vs. Excel.

I forgot that default file format. I have indeed “dumbed down” my own install to certified level (and also Excel, which was a more convoluted path.)

The part about honesty is perhaps the only thing my “answer” contributes. If you want users to transfer from Excel to Calc, the user interface is maybe your smallest issue. There will be trouble handling some files coming from Excel. Your reason for transferring should outweigh the trouble.

So,

• Why do you want your users to quit Excel and use Calc instead?
• Will they accept the truth?

For better contributions towards your request, look to what other helpers say. My best experience relevant to this task is older than LibreOffice, I failed (override from my superiors, no users obstructing the change), and I am perhaps more emotional than rational and structured about it. Sorry!