In preparation for my big annual reporting push I’ve developed a (partial) Unified Method of Pivot Table Formatting. My motivation was to define a system that allows me to copy pivot tables as values while preserving the formatting, and that increases formatting flexibility. Simply stated the Unified Method is:
“Use Conditional Formatting for Everything, and Apply Every Conditional Format to the Entire Pivot Table.” *
That’s right. No built-in “PivotTable Styles,” no field-level conditional formats, and no more just selecting the whole table, drawing a border around it and hoping “Preserve cell formatting on update” works.
* Excel 2007 and later. Applies only to formula-based conditions, e.g., not to color scales. Other restrictions apply.
Above is a section of a world population pivot table. It has the following conditional formats:
- The header row is colored orange with a black border around each cell.
- There’s a black border to the left and right of the table.
- Subtotal rows are bold with a black border above and below but no interior border, and are the same color as the header row.
- Detail row cells are surrounded by a light gray border.
- There are no borders between columns A and B, as Column A is only one pixel wide and flops over into Column B, as with “Oceania Total.”
- Country rows have alternate banding by country.
- State/Province rows’ population number font is gray
That’s quite a list, I think you’ll agree. In the past I might have used regular formatting, pivot table styles and field-level conditional formatting. I’d like to avoid all of those approaches, for the following reasons:
Regular Formatting: By “regular formatting” I mean something like selecting the whole pivot table and applying an outside border. This requires the pivot table setting “Preserve cell formatting on update” to be turned on. I’ll admit I’ve never mastered the quirks of this setting, so I’d like to just avoid it.
I’ve already found things, like header row word-wrapping, that may make me relent on this one.
Built-in PivotTable Styles: I really don’t like the built-in pivot table styles, for a few reasons:
- The text and cell colors are ugly to the point of unusability. They almost always need modification.
- Modifying them is a pain. The names for pivot table parts are weird. For example, what’s a “First Row Stripe,” and how does “Column Subheading 2” compare to “Subtotal Column 2?” So I do a lot of guessing and backing out of the dialog to see if I guessed right. Very clunky.
- Finally, if you copy the pivot as values these formats disappear, although John Walkenbach has a solution for that.
Field-level Conditional Formatting: The field-level conditional formatting that became available in Excel 2007, and that I discuss in Re-Apply Pivot Table Conditional Formatting, is certainly better than the pivot table styles. But, again, the formats disappear when you copy the pivot table as values. And you can only use them to format the value fields of a pivot table, so for something like alternate row banding that includes row labels you need to apply the rule twice.
THE “UNIFIED” APPROACH
So, instead of the approaches above, I apply every conditional format to the entire pivot table. I use the ModifyAppliesToRange method, as discussed in the post linked above, to re-apply conditional formatting to the entire pivot table when it’s refreshed. This keeps all the formatting intact when I copy the table as values. It also allows me to easily apply formatting to specific columns and rows.
Note that in the Extend Pivot Table Conditional Formatting Post I only dealt with the rows of the pivot table that had data. In the example file at the end of this post I’ve extended the code to include the entire pivot table.
Since I’m applying the conditional formatting to the whole pivot table, the conditions sometimes need to specify row or column numbers. For example, I only want to gray the text for State/Province rows in columns D and E. That condition looks like this:
This simply says if column C is blank, gray the text from Column D to the right. I could also specify less than or equal to 5 (column E), but since the conditional formatting is limited to the pivot table that’s not necessary.
Another part that was fiddly is not showing borders between columns A and B. To do this requires two formulas, one to negate column A’s right border, another to blank out column B’s left border. Here’s the formula and setup for column A:
The order of the rules is very important with this and other conditions. These “no-border” formats need to be before the formats with the borders.
A Couple More Things
I found this post harder to write than most. Although I think this is an interesting and helpful approach, I don’t know how clear I’ve been. If you have any questions, let me know.
It’s worth restating that the Unified Method of Pivot Table Formatting really only works for Excel 2007 onwards. Earlier versions limit you to three conditional formats in a given cell. Also, it only works for formula-based conditional formatting, i.e., not for color scales, icons, etc.
You can download a workbook with the pivot table shown above. It also includes the code to extend the conditional formatting to the whole table after it’s refreshed.