Can't see Calc as an alternative to Excel—Please, proof me wrong

English
#1

Don’t get me wrong. I am a big fan of both FOSS projects and Linux ecosystem.

Excel has three horrible things for me:

  • Monthly suscription ($$$$)
  • They spy me without my consent
  • I am forced to update (therefore, some macros, scripts, plugins, etc. just go to hell)

Last week I downloaded LibreOffice and tried to play around in order to make a decision. However, I did not see it as a viable alternative for me. I know I did not used it for more than just a couple of hours, but here are my concerns:

  • No option for formatting as table. This is quite important if you work (like I do) with tons of different tables. Instead of memorizing “A2:B6” for each sheet, something like “table[quantity]” makes references way easier.
  • No XLOOKUP—Giving this up makes simply no freaking sense. This is the most used function in any of my sheets. Cannot understand how this has not been implemented yet.
  • No FILTER—Same as above. I am continuously using FILTER function. Super useful.
  • No TOCOL (nor other similar functions)
  • I don’t know how spill is working in Calc, but seems to work in a very different way. Therefore, I guess functions like FILTER are simply not available.
  • Didn’t even try what happens if you are using a workflow with hundreds of CSVs, as I do, but… Something tells me it’s too deep and technical and it will not work as a charm.
  • Conditional formatting works pretty different. I usually make a traffic light icon trio: 1 is a green circle, 0 is a yellow circle and -1 is a red circle. Tried a bit, don’t see how to do this. Seems like the logic to apply here differs a lot from Excel.

Please, avoid the typical “you’re trolling” comments or the also super common “if you want Excel just use Excel”. This is a serious thread.

My point is I want you to calm me down and tell me things like: “hey, here you have a plugin for FILTER and XLOOKUP” or “yeah, spills work differently, try this”.

Thank you all in advice.

#2

You might care to look at the Release notes for 24.8 due for release later this month, ReleaseNotes/24.8 - The Document Foundation Wiki

And yes, there is an extension, Lox365: XLOOKUP for LibreOffice » Extensions

1 Like
#3
  • No option for formatting as table
  • No XLOOKUP
  • No FILTER
  • No TOCOL — Although Lox365 might be a good solution.
  • I don’t know how spill is working in Calc
  • Using a workflow with hundreds of CSVs
  • Conditional formatting works pretty different
#4

Perhaps there is some other office suite that will meet your expectations - other than M$.

1 Like
#5

Did you see this entry?

I am sorry, I can’t help you with functions after Office 2010. Most of the above functions are simply combining existing functions.

BTW

This is a serious suggestion, there are fundamental differences between the formats. If you share and edit documents with others who use MS Office, then you should also use MS Office.

#6

At this point I ask myself why you still use M$ and Excel and present some of the alleged advantages here?
I can only come to one conclusion:
you are troll

#7

Did you consider named ranges / database ranges?

This lacks any details completely. What specifically you do on which data with which expectation, and what do you see instead? (A hint: a sample spreadsheet could be useful.)

#8

CSV are text files, Excel and Calc can work with them.
If there are hundreds of these files, then automation is possible using macros (macros will be different for Excel and Calc).

1 Like
#9

Table structured references

1 Like
#10

It is tough to handle this not as trollong if I read something like your

There was no XLOOKUP() in Excel in Office 2016/2019, so all the sheets there are useless? Or maybe there is another way possible? Could you use VLOOKUP in some/most/all cases?
.
Concerning LibreOffice, there is work on XLOOKUP and other functions, and there are some extensions. Test what is available as 24.8 will come soon.
.

You can use named ranges.
.

And this is simply too shallow to say much here. One thing, wich actually droves me away from Excel (2003, so pretty long ago), was importing csv without asking for charsets etc and then “proudly presenting” wrong data. But I know there are several users who don’t like to be asked every time.
.
On the other hand, we have Base to bundle csv as datasources, where the content of them would be available to Calc too - even augmenting this by queries is possible. As most(?) users of MS-Office have no database (to be paid separate), Excel has to do a lot of stuff, wich a database could do better. But LibreOffice has Base included. Today I use Sqlite for most things I would have used csv or dbase 20 to 40 years ago.
.
If you really wish help, open single topics on problems you need to solve. Show a file you have, and ask how to achieve the same or similiar result.
.
Then you can decide, if you are up to tge task learning something new and convert your projects. Don’t expect Calc or LibreOffice to be a drop-in replacement, this wil not happen. If you need something like this, consider to put the latest MS-Office in a virtual machine, so you may use it as long as MS continues to have their activation servers for Windows… You can also isolate this from MS quite a bit…

#11

With your selection of issues, it looks like your work is heavily towards strictly tabulated data. If you really want to switch away from Microsoft Office, you will have to make a few concessions.

  • The functions you ask about, can most likely be emulated by way of compounding Calc functions in your formulas. Those will not be nearly as readable and compact as what you have with Excel.
  • Calc allows spill when your formulas are entered in array mode. There may be less safety measures (against overwrite) than what Excel provides, I am not sure. You probably need to plan more ahead.
  • For rendering and free form work, a spreadsheet is a nice tool. For handling well structured tabular data, a database app may be better. LO Base can work with csv files directly, or import such files into a “proper” database storage format. Calc/Base interaction is available.

If you really need to maintain your smooth workflow using the functions you are accustomed to, you also need to accept the drawbacks of Excel/Microsoft. There is not, and never will be, a perfect drop-in replacement.

If you decide that you can make the change and work this out, there are people here who will contribute. There will also be a learning curve, steep uphill …

1 Like
#12

Might be a good thing. I thought LibreOffice was “the best” alternative, but I guess FreeOffice deserves a try. Thanks.

Just to clarify: in my business, we all want to switch. We’re a small team. The thing is we want to be sure the change worths it. It wouldn’t make sense if we change and loose some vital features.

The fact that Excel has some horrible cons doesn’t erase the fact that it has some cool features many workers like me have been using for years. Let’s say I’m stuck in a jail and I want to get out. Don’t call me a troll just for asking for help.

1 Like
#13

Calc is a spreadsheet program which does not pretend to be a database. The whole office suite including all components, 2 macro languages and several database drivers is a download of 370 MB.
Functions SORT and FILTER can be implemented by means of a database connection and plain, simple SQL. A database connection may use a spreadsheet range as source and as output medium.
XLOOKUP has been added for those who could not get their heads around the classical INDEX and MATCH functions as in INDEX(return_vector;MATCH(search_value;search_vector;0)) where the 2 vectors can be any pair of rows or columns as long as they have the same count of elements.

2 Likes
#14
  1. Select cells and click Format > Conditional > Icon Set
  2. In dialogue from the drop down select Traffic lights 1, set your percentages, value, etc and OK
    TrafficLights
    TrafficLights729×605 18.5 KB
3 Likes
#15

FreeOffice/Kingsoft Office/WPS is a suite which draws heavily on Microsoft Office, and I believe some components of Excel and Word are indeed licensed by the company for use in their suite. In my experience, visually and with formula compatibility, the WPS family of apps may be more “forgiving” in their MSO compatibility. That comes at a cost with advanced functionality and language/file type support.

Depending on your situation, you may indeed be better served by the WPS suite. I certainly was not.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.