Conditional formatting is a powerful visualization tool, which becomes exponentially more powerful when combined with the flexibility of a PivotTable. In this tip, review several ways to customize how conditional formats react to changes in the table layout, and practice some advanced techniques using color scales and data bars.
how we can bring out pivot tables to life … with conditional formatting. … This is a four star, rather advanced tip … and that's only because there are some nuances here … that make conditional formatting with pivot tables … a little bit trickier than conditional formatting … with standard cell values. … So, we're going to start by … the same old familiar rules from the Home tab. … Data bars, color scales, icon sets, et cetera, … to highlight patterns or trends … in the pivot table itself, kind of like this. … In this case, we've added formatting … to visualize patterns and average prices … and average point ratings for different lines … for a given center country, in this case, Canada. … Now, unlike traditional self formatting, … we can actually specify some options … for how the pivot table format reacts … to changes in the table layout. … So in other words, these options here … allow us to determine the scope of the formatting … that we've applied. … So you can scope that formatting …
Note: The instructor uses Office 365 ProPlus. Some features may not be available in all versions of Excel.
- Customizing a PivotTable field list
- Grouping dates and values
- Enabling multiple filters
- Conditional formatting
- Refreshing source data
- Adding custom sort lists