Row conditional formatting broken when filtering pivot table

Question

Hi all,

I have a pivot table in which I have conditional formatting rules applied to each row e.g. If the % difference is less than 10% show the whole row in blue, if the % difference is between -10% and 10% show the whole row in green. I set these rules to be applied to the ranges shown below:

If I filter the pivot table the conditional formatting gets removed from certain columns and the "Applies to" range automatically gets changed to the values shown below:

Any ideas how I can keep the conditional formatting applied to all rows/columns originally specific even when filtering the pivot table?

A dummy copy of the workbook can be found here.

Answer 1

Hi Jenni Copple,

Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

 And I appreciate your kind words.

I checked your provided recording video and file and I tested from my end, and it seems that it works from my side without any problem.

First, please try to update your Office 365 version into current latest level, 2202(14931.20764) Semi-Annual Enterprise Channel (some bug will be fix in this way) that was released on 11, October 2022. For your reference: Update history for Microsoft 365 Apps (listed by date)

Since you mentioned, "The issue *does* replicate in Safe Mode." If this situation disappeared in Safe Mode, then this issue could be caused by the incompatible add-ins, you can disable add-ins (Azure DevOps Integration 2019) by this way: Open Excel>add-ins>Disable add-ins. For reference: disable application add-ins and COM add-ins and verity the result with me.

Meanwhile, I need to confirm does another user in your organization meet the same behavior as well? it will help for us to narrow down your problem.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad

Answer 2

Hi Waqas,

Thanks for your continued support. I will need assistance from our IT department in order to try this latest troubleshooting step so it may be a few days before I can provide you with an answer. I'll get back to you as soon as I can.

Kind regards,

Jenni Copple

Answer 3

Hi Jenni Copple,

Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

 And I appreciate your kind words.

You can post back any tine when you feel free and have updates, we will keep follow up this case for you. And I try my best to help you.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad

Answer 4

Hi Waqas,

Thank you for your new suggestions.

I had two colleagues try the same workflow (one who *does* have the Azure DevOps Integration 2019 add-in installed and one who does *not* have the add-in installed) and both of them could replicate the issue. I also tried it on my personal PC which has never had the Azure DevOps Integration 2019 add-in installed and could still replicate the issue.

I also updated to the version you mentioned and can still replicate the issue:

The issue also replicates if I disable the Azure DevOps Integration 2019 add-in.

Looking forward to hearing your response 🙂

Kind regards,

Jenni Copple

Answer 5

Hi Jenni Copple,

Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

 And I appreciate your kind words.

It seems another user also get the same problem as well. If possible, for you could you please switch your Office 365 Semi-Annual Enterprise Channel to Current channel on one of your machine/pc using ODT and test in Current channel and verify the result with us.

Here is article for your reference:

Change the update channel with the Office Deployment Tool (ODT)

Office Deployment Tool

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad