Calc: How to change conditional formatting priority

[EarnestAl]

Unfortunately, there was nothing in the file. I recreated it but this time with two sheets with different ordering of precedence; on one sheet the range B1:B3 conditions were done first, the second the reverse is true.
cf_overlap.ods (10.1 KB)

When I open Format > Conditional > Manage the condition with precedence is highlighted even if it is second in the alphabetical list as shown in your screenshot.

Therefore the highlight indicates precedence.

I added a comment to the bug 148154 – Reordering Conditional Formats in Manage CF dialog

[supernova01]

My bad, your file looks the same.

However, you are wrong about the highlighting. I can click once on any formatting rule and it will be highlighted, regardless of what cell is selected before opening the list, so it’s not reliable. In addition, I think it always shows the rule that’s created the first. I can see in my original file that the default highlighted rule doesn’t even include the selected cell in its range.

Also, at first I thought you were right and wanted to edit my post again, but seems like I can’t edit my post twice. I apologize if later it looks like false information. (Any help is appreciated teaching me how to edit my post a second time.)

[EarnestAl]

The only clue is the initial view when you open the Manage Conditional Formatting dialogue. There is one condition that is highlighted and it seems that is the one that takes precedence in the list of conditions. This might be why your selected cell is not in the highlighted range.

To edit your own comment or question, just click the pencil icon at the bottom.
Cheers, Al

[Villeroy]

The new user interface for conditional formatting looks better than the one in OpenOffice, but it is misleading. From the API point of view, there are no overlaps. Each cell has exactly one ConditionalFormat with multiple conditions. When you create a new C.F. overlapping the old one, the old one is simply overwritten.
The SpecialCells extension shows 3 differently formatted groups of ranges:

1. A1:A3;C1:C3
2. B1:B3
3. the unformatted rest
   

[supernova01]

There is no meaning in calling it the highlighting of precedence if it isn’t related to the selected cell. No matter which cell I select, I see all the formatting rules, and only the first created rule for the entire sheet is highlighted when I open the list. That doesn’t even make sense if no ranges overlap.

Even if the highlight is reworked to show which rule takes priority for a selected cell, it would still mean I couldn’t see the precedence order for the rest of the list in case the cell doesn’t activate the first applicable rule.

There is no pencil icon at the bottom:

Cheers, nova

[supernova01]

• If I define a rule for A2:C2 and another for B1:B3, they will both work when applicable exclusively for B2. There is no overwriting. That also means a cell can have more than one ConditionalFormat.

• The old rule is the one that takes priority in case multiple conditions are met. So when you write a new rule, you pray for the old one not to execute. (Which makes it hard to manually adjust the priority.)

I can define a rectangular range like A1:C3, so I don’t understand what I’m looking at with that extension. It doesn’t look related to my question tbh.

[EarnestAl]

For the question it might be different, try the pencil icon in the top right.

[supernova01]

It only opens the edit history:

The white pencil icon on top left is not clickable. Switching to other display options doesn’t change anything. Double clicking on the text doesn’t let me edit, right click doesn’t bring any option to edit.

I had the edit button the first time. It just disappeared after the first edit.

[mikekaganski]

@EarnestAl

For the question it might be different

No, more likely it’s some unreasonable limit imposed to new users on the site. Like “no more than N edits; no more than X posts; no more than M links and L attachments per day…”

[Villeroy]

Forget “SpecialCells”. It does not really show what I wanted to demonstrate.

The API property “ConditionalFormatting” for B1:B3 has only one c.f. entry. There is no overlapping second entry for that range because any cell range has only one c.f. with multiple conditions but not multiple c.fs.

The GUI should list the first c.f. entry as A1:A3;C1:C3 rather than A1:C3 because that is the multiple selection where the c.f. takes place. B1:B3 has a different c.f. unrelated to the first one.

[supernova01]

That is not true.

You can try it yourself and when the rule for B1:B3 doesn’t get triggered, the rule for A1:C3 will work. So it is not ignored, just has lower priority.

You can even set 2 or more rules for exactly the same range, and see that they all work when their conditions are exclusively triggered.

[schiavinatto]

Hello @supernova01, I’ve suffered a lot with Conditional Formatting.
I came to the conclusion that the cell will be formatted by the first rule that is positive.

[supernova01]

Hello @schiavinatto, I am aware of the truth of your conclusion. We have talked about it above already.

My question is asking for a way to take control of the priority, not how it works. Because simply knowing that the oldest rule takes priority doesn’t mean much.

Thanks for the input though.

[mikekaganski]

@supernova01

asking for a way to take control of the priority

… is off-topic here. No matter if you consider it a feature request, or a bug report.

[supernova01]

I wasn’t asking for a new feature, nor reporting a problem.

I was just asking if there was an existing way that I couldn’t figure out. It was clear in the post. So, it’s not off-topic.

Judging by the crowd, I guess I got my answer:

Is there any way for me to reorder them without deleting and adding again?

No.

[melton]

I found that the conditional formats are ordered by the ranges. I was able to change the order by appending “a1,” to the range on my later conditional format. This moved it to the top of the list.

[EarnestAl]

You can change the order by prepending A1; but it doesn’t change the priority of conditional formatting. That comes down to the order of creation of conditions which is not shown. The only clue is that the condition that is highlighted when you open Manage Conditional Formats dialogue is the first one created.

[ArtAttack]

I’m cross-posting here as this thread seems more current and in case it helps others.

Reordering Conditional Formats (Not Conditions)?

Hi, everyone. I struggled with this and wanted to offer a workaround. I would not want to call it a solution because it would be preferable to actually order the conditions similar to how Excel does it to indicate priority. From my observations it appears priority is determined based on when the condition was added. There is no way to actually know, thus if you delete the condition and re-create it then it will be considered the latest/new and be evaluated first. It’s a pain to do but at least…

[phil.mexico]

I’ve been struggling with this as well, and really wanted it to work.

I hope it helps someone to share that I was surprised to see that there are 2 ways to configure multiple rules:

First, there is the list of rules displayed when clicking “Format > Conditional > Manage…”

Configuring several rules here is where I ran into trouble.

But then, second, when clicking on one of those rules, I saw that there are several conditions that can be configured in one rule:

You can select the order of these conditions there, which worked for me.

[Villeroy]

Cell value is equal to TODAY()

Day(Today()) is the 7 in 7th May 2026