How to convert conditional formatting to static/permanent formatting

[Donaithnen]

This was first asked several years ago (How to convert conditional formatting to static and formatted cells with formulas to plain text?) but apparently closed because it never got an answer.

The question is, given a set of data with conditional formatting, how to you copy that data to another sheet or another document while keeping the visible formatting but without copying the conditional formatting rules. The intent is to “freeze” or “flatten” the formatting onece you’ve achieved the desired results and don’t want any further changes to the data to be reflected by changes in the conditional formatting.

[Donaithnen]

Since i couldn’t find an answer here i ended up perusing several Excel and OpenOffice forums. I found multiple answers, but most of them involved running complicated macros to copy the formatting. However there was one slightly awkward but brilliantly simple solution. Just save the spreadsheet as an html document, then open up that html document in LibreOffice. The formatting will have been applied directly to the cells but the conditional formatting will not be present. You can then copy that data to whatever final location you want.

[ChameleonScales]

This trick doesn’t work with the “Color Scale” mode. It might only work with the “Condition…” mode (I only tested these 2 modes).
If anyone has a solution for Color Scale I’m all ears (I’m currently using LO 7.0.4 but will upgrade soon, though I doubt it will change).

[Gregorius]

Hi guys

I was looking for exactly the same thing and Donaithnen advise (save as HTML) worked great for me. One problem that occurs with this solution is that it changes fonts and width of the columns. It is however easy to rectify.
Thank you for that, I appreciate the time you spend looking for the solution.

[bene25]

Thanks for help, it works!