Conditional formatting for Pivot table

[Tanu]

Hello,


I want to hihglight growth %( more than 30 green clour, less than 30% red clounr etc) data contained in pivot table. I tried applying conditional formatting by selecting only growth % cells. But the problem I'm facing here is wen I filter out and select some other country to have a look on growth rate of different products in dat country I m seeing only flat data without any clolour formatting. How to apply conditional formatting to entire worksheet so that as and wen I filter out I shd be able to see colour coding for entire range?

 

[vasim]

Select all then do your conditional formatiing.


Edit - Select all.

 

[Tanu]

No no, Please see the below pasted data...


Country (All)


Column Labels

Q1 Q2 Q3 Q4

Country Sales(units) Growth % Sales(units) Growth % Sales(units) Growth % Sales(units) Growth %

AAAA

2007 1776 3089 2741 4513

2008 3783 113.01% 2013 -34.83% 4521 64.94% 4840 7.25%

2009 2357 -37.69% 4102 103.78% 4309 -4.69% 4025 -16.84%

2010 53306 2161.60% 60933 1385.45% 80922 1777.98% 79524 1875.75%

BBBB

2007 9840 14283 8754 10864

2008 12056 22.52% 11659 -18.37% 14002 59.95% 12261 12.86%

2009 7851 -34.88% 12747 9.33% 9602 -31.42% 12674 3.37%

2010 33501 326.71% 33190 160.37% 33193 245.69% 41857 230.26%

GFF

2007 454 836 204 203

2008 139 -69.38% 376 -55.02% 427 109.31% 232 14.29%

2009 186 33.81% 245 -34.84% 298 -30.21% 332 43.10%

2010 334 79.57% 554 126.12% 318 6.71% 647 94.88%

HTT

2007 421 420 322 286

2008 333 -20.90% 425 1.19% 199 -38.20% 402 40.56%

2009 503 51.05% 221 -48.00% 252 26.63% 343 -14.68%

2010 283 -43.74% 242 9.50% 441 75.00% 412 20.12%

KMMM

2007 510 79 215 69

2008 176 -65.49% 70 -11.39% 54 -74.88% 98 42.03%

2009 169 -3.98% 194 177.14% 253 368.52% 227 131.63%

2010 142 -15.98% 101 -47.94% 273 7.91% 187 -17.62%

TTT

2007 447 525 509 986

2008 942 110.74% 550 4.76% 446 -12.38% 277 -71.91%

2009 428 -54.56% 527 -4.18% 378 -15.25% 1505 443.32%

2010 1121 161.92% 697 32.26% 517 36.77% 417 -72.29%

WWWW

2007 565 1140 1090 1152

2008 688 21.77% 696 -38.95% 807 -25.96% 1726 49.83%

2009 411 -40.26% 417 -40.09% 746 -7.56% 697 -59.62%

2010 1085 163.99% 421 0.96% 923 23.73% 1088 56.10%

YYYYY

2007 44 48 67 46

2008 70 59.09% 43 -10.42% 33 -50.75% 36 -21.74%

2009 41 -41.43% 44 2.33% 87 163.64% 83 130.56%

2010 50 21.95% 64 45.45% 84 -3.45% 76 -8.43%

Grand Total 134012 150951 166987 182085


Here, I can select the growth cell and can colour code it. Wen I change the country(On top of this table(currently showing "ALL") I cannot see colour code applying to them. I want to colour code this table in such way that even If I try changing the country, colour code shd get chnaged accordingly.

 

[Meedan]

I believe you are using Excel 2007. ( This might work in Excel 2010 also)


Click on the cell where you need to apply conditional formatting. (Doesn't matters if any particular country is selected in filter.)


In Home Tab, click on Conditional Formatting - New Rule. In the New Formatting Rule window, the very first frame - "Apply Rule to: ". You have to select the second option "All Cells showing 'Sum of Growth %' values".


Now you can change the filter to any country & the conditional formatting will work on all filters.


--------------------------


In Case you are using Excel 2003,follow below steps.


1. In Country Select All(No Filters). As you have shown in post above.

2. Select the Growth % Data(For All Quarters)till total, where you want your Conditional formatting to work.

3. Apply the first condition - If More than 30% then Green colour.

(Cell Value is - Greater than - 30%) Click on format button - Pattern - Green Colour.

4.To apply the second condition i.e Less than 30% then Red COlour.

(Cell Value is - Between - 1% and 30%) Click on format - Red Colour.


Now you can change your filter to any country & your conditional formatting will work smoothly.


I hope I have helped you in some manner.