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

Dear Jenni Copple,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, please let us cooperate and please don’t worry, we can work together to narrow down and resolve the situation.

Try to select menu item File -> Options -> Advanced

Under "Editing options" header uncheck the box against "Extend data range formats and formulas". Refer to screen shot below.

It doesn't work then as I cannot access your sample file link, you may upload into your OneDrive account and share a link here and we will be tested on that and verify the result with you in a productive manner.

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

Sincerely, 

Waqas Muhammad

Answer 2

Hi Waqas,

Thank you for your quick response. Unfortunately your suggestion did not work on the real workbook. I have uploaded the dummy workbook to my OneDrive here. Hopefully you can access it now :)

Kind regards,

Jennifer

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.

I request you, please allow me some time and I will test on my side with your provided file, and I update you here ASAP after I get the result.

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

Sincerely,

Waqas Muhammad

Answer 4

Hi Jenni Copple,

Thanks for your understanding and patience and I appreciate your kind words.

Based on my test with your provided sample file, it seems when I Filter the pivot table the conditional formatting keep stay certain columns and the "Applies to" range cannot changed.

May I know have you ever installed any add-ins for Excel? if possible, could you please try open your Excel insafe mode which allow you to safely use it when it has encountered certain problems.

To confirm whether this is a bug in a specific version, please provide us the version information. To check version: Open Excel>File>Account>Product Information and capture a full screenshot and share here and we will be tested on that version and verify the result with you.

Meanwhile, if you don’t mind and if it is convenient for you, could you please send recording video to me so that I can look from my side, and I will check this behavior with exact the same steps and verify the result with you.

If there is confidential information, then you can send it to me in Private Message (PM). you can access PM from the top right corner of this page, as shown in image below:

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

Sincerely,

Waqas Muhammad

Answer 5

Hi Waqas,

Hopefully I've included all the information you've requested below 😊

Add-ons: The only add-on I have installed is the Azure DevOps Integration 2019

Product information:

The issue *does* replicate in Safe Mode.

I have sent you a recording of the issue and the real workbook in a private message.

Please let me know if you need any further info 😀

Kind regards,

Jennifer