Sorting rules - Conditioned Formating - Excel web app

Question

Hello everyone,

I recently worked out a time planer online with 3 worksheets in place.

• 1st has the employee names and the calender in it (called Overview)
• 2nd has the approved time periodes in it (called approved)
• 3rd has the planned time periodes in it (called planned)
• The information for 2nd and 3rd sheet comes from PowerAutomate and an Online Form. The data gets automatically transfered to the 3rd excel sheet, then approved or not via another tool (Approval) and then copied to the 2nd sheet if approved.

Goal is to make the sheet as user friendly as possible - so if possible with no need to implement or write anything in it manually.

I now made a "countif" that works in itself and does what it should. It checks the date in the 1st check against the submitted start and end date in the 3rd worksheet and highlights the planned period in orange. If the request gets accepted the "countif" does the same with the 2nd worksheet and highlights it green.

Since i did the rules offline they worked like they should since the i could move them around.

Now in the web-app the order of application suddenly changes and the "mark orange" rule is suddenly in first place instead of second.

Is there a way to fix that?

To this day i tried:

• Opening in Excel for Mac and swap the rules
• changed the worksheet name to "Z_planned" so the first letter comes after the "approved" worksheet (note - its german. so that was not the case prior to this change)

Any help would be appreciated

Answer 1

Hi Mike,

Thank you for the detailed reply.

From this official article: Use conditional formatting to highlight information (microsoft.com), when two or more conditional formatting rules apply to a range of cells, these rules are evaluated in order of precedence (top to bottom) by how they are listed in this pane. And new rules are always added to the top of the list and therefore have a higher precedence. This matches the test 2 result.

About the reason why the rules will be swapped after uploading to OneDrive, I researched a lot on my side, unfortunately, I didn't find any related article about this behavior.

Given this situation, the workable way is to add conditional formatting on the Excel for the web based on the rules I mentioned at the beginning.

The second option I can think of is to edit condition for orange to exclude those conditions that also meet the criteria for highlighting green. In this case, you can work in desktop Excel and upload to OneDrive without the concern of misplaced rules.

Best regards,

Jazlyn

Answer 2

The issue was that i created the file offline due to the fact that the online app looks different from the "offline" version. So i wasnt sure how to create rules without a preset conditions. After uploading the file to OneDrive it was working properly with the rules - after some time, and i do not know what triggered it, the rules suddenly swapped places.

The rules are mostly the same - hence the only idea i had was that after I closed the excel sheet and it was opened again it "updated" the file and automatically sorted the rules "alphabetically" or by length (the 2nd rule is shorter)

After changing the worksheet names to "Approved" and "Z_Pending" so the corresponding rules would get sorted the same way. For now.. it seems to work.. i hope it does it thing now. Edit: After looking into the file again today it changed the sorting again...

So test 2 is now what you suggested "... try to delete the rule and re-add the rule on Excel for the web version to see the result." After the weekend the rules are still in place where i had them. But it was needed to add the rule you want to implement / run 2nd to be added first.

Seems a bit counterintuitive but i guess excel just layers the rules - so whatever gets added, gets added on top.

I was just wondering how the rules get sorted in order to prevent this from happening in the future.

Answer 3

Hi Mike,

Thank you for the reply. We understand that you could not share the file due to the security.

You mentioned "After some time of the file being online the sorting suddenly changes for some reason", may I confirm that whether you mean that there was no problem for the file when you just uploaded to the cloud? If yes, then you can try to delete the rule and re-add the rule on Excel for the web version to see the result.

Best regards,

Jazlyn

Answer 4

Unfortunately the are sorted differently.

I added "Rule Approved" and then "Rule Planned" -> sorted it to have the "Approved" above the "Planned" -> saved it.

After some time of the file being online the sorting suddenly changes for some reason.

I am unable to share the OneDrive link due to Security Protocol :/

Answer 5

Hi Mike,

Thank you for posting your query in our community.

In the Excel for the web, may I know the order of these two rules is same as the order in Excel client? To help you better, could you please send a sample file for us via OneDrive link so that we can investigate it for you.

Appreciate your cooperation in advance.

Best regards,

Jazlyn