Bug 83301 - Allow more than 3 conditional format codes
Summary: Allow more than 3 conditional format codes
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting Number-Format
  Show dependency treegraph
Reported: 2014-08-31 16:33 UTC by rlk
Modified: 2019-05-15 12:34 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Note You need to log in before you can comment on or make changes to this bug.
Description rlk 2014-08-31 16:33:50 UTC
If I attempt to assign the following format code:

[BLACK][=0]"(all fast) ";[BLACK][=1]"(fast adj)";[BLACK][=2]"(races)";"(non-CD)"

it fails (and clicking the checkbox shows that everything starting with [BLACK][=2] is wrong), but this works:

[BLACK][=0]"(all fast) ";[BLACK][=1]"(fast adj)";"(races)"
Comment 1 Matthew Francis 2014-08-31 17:35:07 UTC
It's documented in https://help.libreoffice.org/Common/Number_Format_Codes that 3 is the maximum number. It may be, among other reasons, that there are interoperability reasons for this (i.e. that it couldn't be exported to an Excel file with more than three).

There is another way to do what you're trying to do, although it's slightly more long winded:
1. Create four styles for your four cases, so that for instance the first one has a name of '(all fast)' and a number format code of "(all fast)" <-- including the double quotes
2. Select the range you want to have the style apply to
3. Open the menu Format - Conditional Formatting - Condition...
4. Add four conditions:
   Condition 1: Cell value is, equal to, 0; Apply Style  (all fast)
   Condition 2: Cell value is, equal to, 1; Apply Style  (fast adj)
   ... and so on

There also isn't a convenient way to name this whole set of conditions, but you can copy and then "Paste Special" with "Formats" selected to move them around
Comment 2 Joel Madero 2014-09-03 21:02:51 UTC
As explained in comment 1, LibreOffice only supports 3 formats and this is documented.

That being said - this is a fine enhancement request, marking as such.

Note: Enhancement requests are done almost exclusively by volunteers, that being said they choose (ie. we don't push) what enhancement they want to do. So despite this being a valid request - it's dependent on a volunteer choosing to implement and therefore there is no ETA. Patches are welcome if you or someone you know wants to implement this.
Comment 3 rlk 2014-09-17 00:43:41 UTC
In the event, I found another, better way of doing this, using Data->Validity with a pick list.
Comment 4 Joachim Otahal 2015-09-01 01:22:02 UTC
As for LibreOffice Still the same problem.
My case: Make huge numbers "human readable" with a selectable amount of digits since there is no template for that.

Working (German install) show at least two, at max four digits:
[>=10000000]#.. "Mil";[>=10000]#. "Th";Standard
(Libreoffice always adds "Standard" at the end, probably "Default" in English installations)

Not working:
[>=10000000000]#.. "Bil";[>=10000000]#.. "Mil";[>=10000]#. "Th";Standard

Also not working:
[>=10000000000000]#.. "Tri";[>=10000000000]#.. "Bil";[>=10000000]#.. "Mil";[>=10000]#. "Th";Standard

I'll try the workarounds described in this bug report now, I guess they will work. Thanks for the hints.
Comment 5 rlk 2015-10-21 14:55:23 UTC
Another way that works is to use conditional formatting with the conditional formula being STYLE(...).  As far as I'm concerned, this can now be closed.
Comment 6 Joel Madero 2015-10-21 17:50:35 UTC
I think it's still a valid request so going to leave it open. Shouldn't require all these tricks to use more than 3 conditional formats :) That being said, it's good to know there are a couple ways to work around the issue.
Comment 7 Eike Rathke 2017-09-30 16:56:11 UTC
The number format code structure needs to be interoperable with Excel and other spreadsheet implementations that use the same. Expanding the number of conditions is not possible. Use conditional formatting with different number formats if such is needed.
Comment 8 Mike Kaganski 2019-05-15 05:58:01 UTC
(In reply to Eike Rathke from comment #7)

I perfectly understand the reasoning here. Still, I agree with comment 6; and I feel that limiting ourselves for compatibility reasons is too ... limiting. Except for the compatibility, there seems to exist nothing limiting the possible number of subformats with conditions, so having arbitrary number of conditional subformats, plus one subformat without condition (for all other numerical values), plus one for text, seems like unambiguous superset of existing syntax; and for export to foreign formats like XLS(X), it looks reasonable to just drop the excessive subformats, so that only two first conditional subformats plus unconditional plus textual are kept. In the end, we don't limit ourselves to 256 columns just to keep compatibility with XLS?
Comment 9 rlk 2019-05-15 12:34:03 UTC
I agree with #8.  There's no obvious reason why LibreOffice must confine itself to being an exact match for Excel.

If I attempt to save a spreadsheet in .xlsx format, I already get an alert that the document might contain formatting or content that can't be saved in that format, without specifying what that format or content is.  This would simply be an example of such.  I have no interest in whether this spreadsheet will work in Excel, and this would be a useful superset capability.