Is there a way to preserve conditional formatting created in LibreOffice .xlsx and opened in Excel? Thanks

[steve.101]

Hi. I’ve recently completed an SEO Audit course which uses spreadsheets with conditional formatting. I can do this in Calc as .xlsx, but when opening the file in Excel (as I expect many of my clients would), the conditional formatting is lost. Is there any way around this? Thanks!

For instance - highlighting duplicates, or background colour for cells containing certain text. Can do in LibreOffice but when opening in Excel get the following message:

“Excel found unreadable content in ‘.xlsx’. Do you want to recover the contents of this workbook? If you trust the workbook, click Yes.”

Click Yes, file loads, all there - except the conditional formatting.

(Same thing happens as an .ods file.)

[steve.101]

I’m thinking macros might be the answer - would be a steep learning curve for me, but maybe worth doing if it works. So if anybody’s had experience “simulating” conditional formatting through this means and preserving the results when opened in Excel - please let us know! Thanking you …

[mariosv]

There is a know bug about this matter.

https://bugs.documentfoundation.org/show_bug.cgi?id=99866

[steve.101]

Thanks for that, but unfortunately it doesn’t help me

[Phoinx]

The question is old, but the problem persists in 2020 (LO v.6.3.2.2 and Excel 2010 v.14.0 here).

I think found a workaround.

Facts:

• when you create conditional format rules in LO (and save the file as .xlsx), they are lost in Excel;
• however, when you create conditional format rules in Excel, LO can open the file and recognize them;
• Excel cannot apply styles through conditional formatting - at least, not in the open… When you open the file in LO and read the rules Excel created, there are actual styles created for it (here is “ExtConditionalStyle_1”, “ExtConditionalStyle_2” etc);

So, what I propose is:

1. open your file in Excel, create at least 1 conditional format rule (for a range of cells), save the file;
2. open file in LO, edit rules as you want, but preserving the styles’ names Excel created (you can even create new “ExtConditionalStyle_#” styles!), save the file;
3. open the file in Excel again and the rules still should work.