PivotTable VII: – Conditional formatting gets even better, or visualizing your data in PivotTables
In a series of previous articles I introduced the new conditional formatting capabilities in Excel 12 (see here for the whole series of posts). Today I want to add one more article to the series, which is the work we have done to make conditional formatting work really well in Excel PivotTables. This is an area that I love to show people, because the work we have done reduces a formerly-tricky task down to a few clicks.
The first point I want to make is that all the features we added around new types of conditional formatting (data bars, colour scales, icon sets, etc.), new rules (top 10, below average, etc.), and new UI are available for use on PivotTables. However, we did not stop there. To provide a great experience with conditional formatting inside PivotTables, we now associate the conditional formatting rules to the structure of the PivotTable instead of to the cells. What this means is that as users work with PivotTables (adding and removing fields, refreshing the data, expanding and collapsing levels, Pivoting fields between rows and columns, grouping fields, etc.), the formatting tracks the cells in the PivotTable appropriately, so the users don’t need to worry about what is happening to the formatting – things just work, and they can focus on analyzing their data instead. Let’s walk through an example.
In the PivotTable below I’m looking at sales for different bike models, and I have arranged my report so that I have a column with sales data for each year. To make it easier to visually compare the sales of various bike models, I want to apply conditional formatting to the sales values. In the same way as I would when conditionally formatting “ordinary” (i.e. non-PivotTable cells), I select some cells containing sales values …
(Click to enlarge)
… and then I use the ribbon to add a Data Bar format which helps users quickly scan their data to compare values and find outliers.
(Click to enlarge)
With two clicks, the conditional formatting is now applied to the cells I selected.
(Click to enlarge)
This is helpful, since I can now easily get a much better sense as to the relative sizes of the numbers I have selected, but oftentimes what I actually want is to apply the conditional formatting to *all* cells displaying bike sales. In this example, I might want to compare mountain bikes with road bikes using the same conditional formatting rule, but I want to avoid formatting subtotals (such as the total for the entire Mountain Bike category), since they would skew the results. You might be thinking “that’s going to take a lot of multiple-selection, especially on big PivotTables with a lot of levels.” To make it very easy to choose the “scope” of a conditional formatting rule in a PivotTable, Excel 12 provides some “on-object-UI” (OOUI) which gives you the choice of which scope you meant for the rule to be applied to (by scope I mean which set of cells get the conditional formatting).
(Click to enlarge)
Let me briefly explain the three options. (Note, we are still working on the wording of the last option. It’s also worth noting that these options are also exposed in the conditional formatting creation and management UI, so you don’t have to rely on the on-object UI.)
- Selected cells – this will leave the conditional formatting applied to just the selected cells
- All “Sum of Sales Amount” cells – this will apply the conditional formatting to all Sum of Sales Amount cells in the PivotTable, regardless of level, and including subtotals. This will be useful in cases for measures that aren’t sums – if you have an “Average Retention” measure, for instance, all values (including subtotals and grandtotals) will be between 0 and 1 and can be sensibly formatted using a single rule.
- All “Sum of Sales Amount” cells with the same fields – this will apply conditional formatting to all Sum of Sales Amount cells at this level in the PivotTable, which excludes subtotals. I suspect this will be the most commonly used.
In this case, I want to apply the rule to all cells displaying sales for individual bike models and individual years. To do this, I’ll pick: All “Sum of Sales Amount” cells with the same fields. After I have made this selection, the PivotTable will now show the conditional formatting in all cells showing sales for an individual product category and an individual year.
(Click to enlarge)
You’ll notice that there is no conditional formatting of the sales values for the “Product Category” field (“Mountain Bikes” and “Road Bikes”). It wouldn’t make much sense since those values are not at the same level as the values for the individual products.
For the sake of completeness, here is what the PivotTable would look like if I had selected All “Sum of Sales Amount” cells
(Click to enlarge)
However, this doesn’t make much sense in this particular example because the grand totals skew the formatting in all the other cells so it’s hard to spot any differences. That said, this type of scoping works great for relative values, (for example % profitability) where you can directly compare values at any level of detail.
Once the conditional formatting is applied, I can interact with the PivotTable and the formatting will be reevaluated dynamically (as I mentioned above). For example, if I change my report filter to only show sales to a specific country, the sales values will be reduced to only show that information and the conditional formatting will be automatically reevaluated to reflect the new values.
I can also add and remove fields and have the formatting adjust to that. Here is a screenshot of the same PivotTable after having removed the “Product Category” field.
(Click to enlarge)
And if I add another field instead, the conditional formatting rule is automatically reevaluated again. Here is a screenshot of the PivotTable after adding the “Country” instead of the “Product Category” field I removed before.
(Click to enlarge)
That’s the summary for conditional formatting and PivotTables. With these improvements, PivotTables can now be used as a great tool for exploring data, highlighting trends, spotting outliers, etc.