Protocol for creating a spreadsheet with compatible formatting for Excel?

English
#1

I work for a holistically managed ranch that is developing an apprenticeship program to help train the next generation of regenerative land managers in consistently achieving desirable ecological, social and economic outcomes. To track and guide the learning and mentoring process, we have developed a skills evaluation spreadsheet in LibreOffice.

Unfortunately, the spreadsheet also has to work with Excel, because that’s what the rest of the ranch team uses and what contacts outside the ranch also use. It does, mostly, with the following issues:

  1. Conditional formatting is mostly gone. I have two sets of conditional formatting:
    a. color-code score columns from lowest score to highest score, yellow to green, to quickly see what is above or below average for that particular set of skills.
    b. highlight comments columns in red whenever a discrepancy of 1 or more exists between the mentor’s evaluation and apprentice’s self-evaluation.

The “a” formats disappear, while the “b” formats remain when opening and/or saving an .ods file in Excel. According to Is there a way to preserve conditional formatting created in LibreOffice .xlsx and opened in Excel? Thanks - #5 by Phoinx the way to preserve conditional formatting is to create the formatting in Excel first, rather than in LibreOffice.

  1. Opening the .ods file in Excel appears to mangle the hyperlinks. These are simply internal references between sheets in the same file to enhance navigation, so I would think that would be a non-issue, but they are listed as “invalid” in Excel, and when the file is saved in Excel and is opened again in LibreOffice, the hyperlinks no longer work.

The original file (link works)
=HYPERLINK("#'Workplace Ethics & Etiquette'.A1", $'Workplace Ethics & Etiquette'.A1)

When opened and saved in Excel, it becomes:
=HYPERLINK("#'Workplace Ethics & Etiquette'.A1", 'Workplace_Ethics_&_Etiquette'.A1)
But then doesn’t work in either program.

It appears that Excel changes the names of the sheets themselves, adding the underscore to each sheet. I’m not sure why the link no longer works in either program after opening and saving in Excel, though.

According to here: LibreOffice Calc cell worksheet references that also work in Excel? - Stack Overflow
The way to ensure that hyperlinks work is to change the Tools->Options->Libreoffice Calc->Formula->Formula Options->Formula Syntax to “Excel A1”

My questions are:

  1. Is any of this behavior a bug that should be reported or that already exists? Is it because MS Office isn’t fully compliant with the ODS format? Or is this some other issue(s)?
  2. Is there a standard or best practice protocol for maximizing compatibility when developing a spreadsheet to work with both Excel and LibreOffice?
  3. Are there any potential repercussions or adverse effects I should be aware of to the above-mentioned workarounds, ie., of using “Excel A1” formula syntax to preserve worksheet references and hyperlinks and doing the conditional formatting in Excel so that both programs can preserve conditional formatting?
#2

You can search in the Bugzilla:
https://bugs.documentfoundation.org/

#3

Never was and never will be 100% compatibility between the different file formats.

The Open Document Format (.ods) is an international Standard. The Excel can open it, but cannot save into that file format.

The OOXML (.xlsx) is an international Standard, too. But the Excel uses the Transitional version it by default, instead of the Strict version. And the Microsoft often changes the rules of the Transitional version. Only the developers of the Excel can follow the changes.

I strongly suggest you to use one file format and one spreadsheet application is you want to work safely and effectively.

2 Likes
#4

Never do it. Choose one of the speadsheet softwares for all of users. (The LO is free)

1 Like
#5

The cited suggestion is about use of XLSX format; while you write about using ODS. So:

  1. If you experience the mentioned problem using XLSX, it is definitely a LibreOffice interoperability bug, and must be reported;
  2. If you see that Excel opens ODS and messes up with it, it is most likely Excel’s bug (their poor support of ODF).

The formula has two argument to HYPERLINK: first one ("#'Workplace Ethics & Etiquette'.A1") is URL (reduced to the fragment in the current document), and the second one ($'Workplace Ethics & Etiquette'.A1) is the shown text. You mention: “It appears that Excel changes the names of the sheets themselves”. Fine; and it also changes references accordingly - as you see in the second argument, so it still points to the correct cell to obtain the text shown in the HYPERLINK. But it naturally can’t change the strings that include some indirect references - as you see in the first argument - so the URL’s fragment part, starting from # character, and pointing to a cell on a sheet named “Workplace Ethics & Etiquette”, using Calc’s syntax (sheet and cell separated by a dot, etc.) is unchanged. So it can’t work in Excel (which expects Excel syntax for the URL), and in Calc (simply because Excel renamed the sheet, and there’s no more “Workplace Ethics & Etiquette”, but “Workplace_Ethics_&_Etiquette”).

Generally, I’d suggest you to use ODS for your work; but in your situation, I’d say use XLSX - and rely on LibreOffice’s much superior support of external formats, compared to Excel’s external formats support. That would eliminate errors introduced by Excel being e.g. incapable of keeping sheet names with spaces; but you still need to make sure that reference strings use compatible syntax - and indeed, use Excel A1 for that.

2 Likes
#6

Thanks Mike. I’ll try switching to Excel A1 and .xlsx format and see if that helps preserve the references and the conditional formatting in Excel.

To clarify, if it doesn’t work for some reason, then that would be a bug I need to report against LO?

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