• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table conditional format based on row value

A

Alan Ramsay

New Member
Hi there,

I am hoping there is a way to use conditional formatting to change the fill color of the data cells on a pivot table based on the row value. In the picture below you can see I have grouped some values together to form the row categories - I would like to tell excel to fill the cells with the data a different color where the row is "600-650" and a different color again for the row value "550-600" and so on for all the row categories shown.

Ideally this formatting would apply dynamically so if I change the pivot table layout by making a filter selection I would not lose the formatting of the cells.

Can anyone help? Many thanks.


600-650 1
550-600 2 2 1
500-550 1 1
450-500 1 1
400-450 1
350-400 1
300-350 2
250-300 1
200-250 1 1
150-200 2 1
100-150 5 2
50-100 2
0-50 6 2



600-650
550-600 2 2
500-550 1
450-500 1
400-450 1
350-400
300-350 2 3
250-300 1
200-250 1
150-200 2
100-150 5
50-100 2
0-50 6 2
 
Hi Narayan,

Many thanks for the response. I did read through the alternative thread you highlighted but the solutions provided may be beyond my current Excel skill level - I was really hoping that something could be done through the existing Excel interface without having to involve VBA.

On the face of it I would think this is a fairly frequent requirement - to format a range based on a row value in the pivot table - is there no way to approach resolving this using the existing Excel functions?

Thanks again,
Alan
 
Hi Alan ,

The problem is not the CF of the pivot table cells ; this can be easily done using the standard worksheet CF feature of Excel.

I think the problem is when the pivot table shrinks or expands as you filter ; in these cases , the CF is either lost or misbehaves.

You can try this out , and see if you face the same problem ; if you do , then I think there is no option but to use VBA.

Narayan
 
Hi Narayan,

Thanks again for the response - you are right in that I have issues retaining the formatting when the pivot shrinks or expands. But I am also struggling to see how to have the data cells in the pivot conditionally formatted based on the categories in the rows. Are there any "guides" or examples of this I could refer to?

Cheers,
Alan
 
 
You must log in or register to reply here.
Back
Top