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 220.127.116.11 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.