Conditional Formatting in a Pivot Table

[smiley1978]

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. Any help would be appreciated.

 

[Jerry Sullivan]

By Conditional Formatting, are you referring to the Data Area being formatted uniformly whatever the configuration, or are you referring to formatting cells depending on their values (like range Conditional formatting)?

If it's the first one, consider using a custom PivotTable Style.

Click on your Pivot > Ribbon > Design tab > in the PivotTable Styles group, click the down arrow of the scroll bar >
New PivotTable Style.

Once you have saved a custom PivotTable Style to the PivotTable Style Pallette, you can reuse it in that Workbook.

If you want to reuse it in another Workbook, you can temporarily copy a Pivot that is using that style into your other Workbook. Your PivotTable Style Pallette will then appear in that Workbook's Pallette.

 

[smiley1978]

I am referring to the second option. Regardless of the configuration, I want the data area to have conditional formats based on their values.

 

[Jerry Sullivan]

Beginning with xl2007 there were some context-sensitive options added to the Conditional Formatting(CF) dialogs when applying formatting to PivotTables.

If you're not already familiar with the 3 types of "scoping" you can apply to a rule for a PivotTable, see this link which appears in Excel 2010's Help. (not sure if it's in xl2007's Help)
Add, change, find, or clear conditional formats - Excel - Office.com

Your question relates to this part of the description:
"If you change the layout of the PivotTable report, by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained, as long as the fields in the underlying data are not removed."

In my experience, this is not always true (or I might be misinterpreting the description).
PivotTable CF that is applied with "Scoping by value field" or "Scoping by corresponding field" seems to exhibit this behavior:

filtering: CF Maintained

hiding levels: I'm not postive what that means. CF is not Maintained when a field is deselected then reselected in the Field List.

collapsing and expanding levels: CF Maintained

moving a field: CF Maintained if changing the Position of a field within the same PivotTable area; Not Maintained if moved to another area.

Based on that (and I'd appreciate a correction if I have any of that wrong), I believe you'll need to use a VBA approach to achieve your desired result.

Here are links to two examples that take very different approaches.

This code by Doug Glancy stores the CF settings in a header cell, then reapplies the CF to the data range after a change.
http://yoursumbuddy.com/re-apply-excel-pivot-table-conditional-formatting/

The code by Jon Peltier uses VBA to test a condition and apply formatting instead of using Excel's CF Function.
Pivot Table Conditional Formatting with VBA | Peltier Tech Blog | Excel Charts

I'd be glad to help you adapt either of those examples, or develop alternative code if those don't suit your needs.
Looks like a big gap that could use a good general-purpose-code workaround.

 

[smiley1978]

I found what I was looking for. Home>Styles>Conditional Formatting>Manage Rules. Once here, there is a drop down at the top of the dialog box: "Show formatting rules for :". I selected "This PivotTable".

Then I added a new rule and assign it a cell in the pivot table, save it and close the rule. I then go back and edit the rule and at the top of the edit dialog box, there is a box that has a range selection box and three option buttons:

Selected cells
All cells showing "XXXXXXX" values
All cells showing "XXXXXXX" values for "YYYYY" and "ZZZZZZ"

"XXXXXXX" = values in the data area of the PivotTable.
"YYYYY" and "ZZZZZZ" (for this example) are the Row and Column Labels.

If I select the second option, it applies the format to all values in the data area, regardless of configuration.

 

[Jerry Sullivan]

Those 3 scoping options are the ones described in the Microsoft link I referenced.
Add, change, find, or clear conditional formats - Excel - Office.com

The step of:

Home>Styles>Conditional Formatting>Manage Rules. Once here, there is a drop down at the top of the dialog box: "Show formatting rules for :". I selected "This PivotTable".

...allows you to find all the existing rules applied to your PivotTable, but doing those steps doesn't apply rules to the PivotTable.

This method should still have the shortcoming of not maintaining CF if a field is deselected-reselected or moved to another area of the PivotTable. If you don't need the capability to maintain the CF for those events, then your approach should work fine and there's no need for any VBA.

 

[mcmagleo]

Hi,

I have a pivot table monitoring stock levels for products- (I am using excel 2013). I want to apply conditional formatting to the the Row header "Sum of Stock cover in Days" wherein if "Sum of Stock cover in Days" is less than "Sum of Min Cov." it will colour red, if "Sum of Stock cover in Days" is greater than "Sum of Max Cov.", it will colour yellow and if "Sum of Stock cover in Days" is between "Sum of Min Cov." and "Sum of Max Cov." it will colour to green.

What will be the steps to do this using the row headers so that when data refreshes it wont lose the conditional formatting ? Note that Row headers are stuctured consistently when raw data is dumped.

By the way, i dont have any background in using VBA or macros.

Any help will be appreciated.