Opening converts pivot tables and changes conditional formatting

[molinodl]

I created a file in Excel that uses data from tables on one sheet as a source for multiple pivot tables on another sheet. Upon opening this Excel file with Libre Office, the pivot tables are converted to regular cells. This would be okay except that it deletes some but not all of the converted data from these pivot tables. Additionally, Libre Office deletes data from the headers in the tables on the source data sheet. Additionally, Libre Office changes conditional formatting on the rows where the pivot tables are. Conditional formatting on other rows on the pivot table sheet are not affected.

If I protect the two sheets in Excel, save it, and then open with Libre Office the pivot tables are still converted to regular cells but the data that they showed is not deleted The source sheet tables have some but not all of their headers deleted. The conditional formatting still changes on the rows where the pivot tables are.

I have checked and unchecked the box in Excel about refreshing these pivot tables on opening. I saw no difference to doing this.

I have a work around where I cut and paste special selecting values only before saving the file in Excel. This converts the pivot tables to regular cells. However, this works because none of the users that receive the file must access the pivot tables. If they did, I wouldn’t be able to let any of the users work in Libre Office.

This problem happens across various of my user’s platforms. It happens on Windows 10, Windows 8 and the lastest Mac OS. It happens in the latest Libre Office update 6.2.5.2 and the version previous to that.

Unless I can trust Libre Office, I will prevent my 75 users from working with this program. A free app that mimics Excel is nice but if it randomly deletes data upon opening a file like as in this case, I can’t trust it and won’t use it.

[anon73440385]

Long text - but I cannot see a question targeted to other users of LibreOffice. So what is your question?

[molinodl]

How do I get Libre Office to not convert pivot tables on opening?
How do I get Libre Office to not destroy conditional formatting on opening?
How do I get Libre Office to not delete source data from a table on opening?
How do I obtain a genuine fix and not have a kluge work around?

[Hrbrgr]

First of all, there is no 100% compatibility between Excel and Calc. But that’s not because of the LibreOffice developers. A program like M$-Office that hides its program code is very hard to imitate and make compatible.

I ask the question in the room: "Why don’t you create your file in LibreOffice from the beginning and save it in an ODF format, so that everyone who also has LibreOffice can use it reasonably?
That would make sense to IMHO.

I know, the alternative for you is Excel 75 times to buy.

I wish you great success!

[molinodl]

Thank you Ebot for reading and commenting on my issue. I wish that I could mandate the app that my 75 volunteers use as you suggest. Life would be easier for sure. In that way I could avoid any compatability issues. Unfortunately, I can’t and I must live with the differences between apps and try to resolve them when they come up. I permitted use of LibreOffice over other open source products because it runs on a wider variety of platforms than any other open source app. I expect compatability issues for the very reason that you said. However, I expect basic functions like “conditional formatting” and “pivot tables” to convert. I expect that when such basic functions aren’t compatable that documentation would describe this. In that way, I could make good decisions about my design or about letting users run the app. No such documentation exists that describe compatability issues for conditional formatting or pivot tables. Surely, I am not the first to encounter this problem.

[Hrbrgr]

Dear @molinodl, as you may know, the LibreOffice project consists mostly of volunteers. For example, there are paid people who are interested in getting LibreOffice to perform certain functions or to improve existing ones. Overall, there are too few who keep any documentation up to date. It is as it is.

I hope you can solve your problem anyway.

But you can also look at Bugzilla, if there is possibly a Bugentry to your problem. Or you write there yourself a bug for your problem.

I wish you great success.