Preserve pivot table formatting after refresh

[sharshra]

I lose the conditional formatting whenever pivot table is refreshed, though I have checked in the option 'Preserve cell formatting on update'. It is very annoying that I have to format after every refresh. Does anyone know what would have gone wrong & how to fix this?

 

[Alex Blakenburg]

When you are applying the conditional formatting you will get some options that relate specifically to pivot tables. You need to set these.

You will find the full set of instructions here on the Contextures site:-
Add Pivot Table Conditional Formatting and Fix Problems

You are looking for a box that looks like the below and you most likely want the 3rd option as highlighted (extracted from the above site)

 

[sharshra]

Thanks, Alex. But, I don't see those options. Screenshot of what I see if shown below. Am I missing anything due to which I don't see those options?

 

[Alex Blakenburg]

You might want to update your profile and show what version of Excel you are using.
Are you using Standard Pivot or Power Pivot (mind you both should work the same) ?

Do you have D2 selected and is D2 in the pivot table when you go into Conditional formatting ?
Before the screen in your picture do you see something like the image below and did you select one of the Pivot Table options in the drop down box ?

 

[sharshra]

Thanks, Alex. I'm using Office 365 & updated the same in my profile.

Answering your other questions -
- Yes, I'm using standard pivot.
- I selected the cells in the pivot table & then clicked on conditional formatting. Then I selected 'This PivotTable' option from the dropdown.

 

[Alex Blakenburg]

When you edit the rule, what appears in the Applies to column ?
Then show me what columns & rows the pivot table covers. The applies to range should be in the pivot table.

 

[sharshra]

Screenshots are pasted below. Table below is replaced with dummy values since I can't paste the actual pivot table due to confidential data. Applies to range is correct & I see it working correctly before refreshing. When refreshed, the conditional formatting is lost.

Status	#	%
aaa	388	43.4%
bbb	125	14.0%
ccc	104	11.6%
In Prod	94	10.5%
eee	71	7.9%
fff	59	6.6%
ggg	46	5.1%
hhh	7	0.8%
Grand Total	894	100%

 

[Alex Blakenburg]

The way it looks to me is that if you want to apply the Conditional Formatting (CF) to columns that include the Row Label columns you won't get the pivot table options.
If you apply the Conditional Formatting to a single Data Column, you will get the Pivot Table Options which allows the formatting to stick.
Sorry I know this was not the answer you were hoping for.

If I try to change the CF that is on a Pivot Table Data column back to all 3 columns (ie include the Row / Label column eg L-N) I get the error message below.

 

[sharshra]

No problems, Alex. Thanks for all the suggestions so far. Yes, I can apply CF on single column but it fails for multiple columns when refreshed, even after selecting preserve formatting after update option.

I'm not sure why I don't see the apply rule window . Hence, I don't get that error message as well

 

[Alex Blakenburg]

Unfortunately that is consistent with what I am finding.
In case its an option for you, a possible alternative might be to create the summary table in Power Query. If you leave the output as just a table conditional formatting should work fine on it.