Ask Your Question
0

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

asked 2017-02-17 09:10:49 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 '<filename>.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.)

edit retag flag offensive close merge delete

Comments

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

steve.101 gravatar imagesteve.101 ( 2017-02-18 02:48:20 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-05-26 22:18:19 +0100

Phoinx gravatar image

updated 2020-05-26 22:18:58 +0100

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.
edit flag offensive delete link more
0

answered 2017-02-19 18:39:44 +0100

m.a.riosv gravatar image

There is a know bug about this matter.

https://bugs.documentfoundation.org/s...

edit flag offensive delete link more

Comments

Thanks for that, but unfortunately it doesn't help me :)

steve.101 gravatar imagesteve.101 ( 2017-02-27 10:45:35 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-17 09:10:49 +0100

Seen: 946 times

Last updated: May 26