Wednesday, April 13, 2016

Excel Pivot table - Conditional formatting pivot table & maintaining format after filtering and un filtering data

Query: I have a pivot table and I want to have the format apply to any cell in the data area, regardless of the configuration of the column and row fields. I want the formatting to fit whatever the table displays and also same to get refreshed whenever data is refreshed. I tried applying it but as soon as data is filtered out conditional formatting gets lost.

Solution: You can follow steps as below:

Step 1 - Select whole  data area,
Step 2 - click on Conditional Formatting on the Home tab of the Ribbon and choose the conditional format you want to apply.
Step 3 - Click on rectangle box at the right end corner,
Step 4 - Select the last option from radio button,

You can also refer below image

Post your query and feedback on