bottom line:Learn how to apply conditional formatting to PivotTables so that formats are dynamically reapplied when the PivotTable is modified, filtered, or updated.
Skill level:intermediary
Download the excel file
Here is the file I used in the video. You can use it to practice.Add, remove, and change conditional formattingin a variety of pivot table examples.
Dynamic table conditional format.zipDescargar
What is conditional formatting?
Formatting cells different from the surrounding data will helphighlight and highlight. Conditional formatting allows you to set up rulesFormat cells by criteria, so that theThe cell format is applied automatically.
This allows us to easily identify trends and anomalies in our reports. Some examples are, but the possibilities are endless:
- Earnings in excess of a certain dollar amount
- Poorly Performing Products
- time series trends
- and more.
Conditional formatting can change the font, fill, and border colors of cells. You can also add icons and data bars to cells.
Formatting is also applied when cell values change. This is great for interactive pivot tables where values can change based on a filter or slicer.
How to set up conditional formatting for pivot tables
Setting up conditional formatting for pivot tables is a bitunlike regular cells/ranges. This is how I explain it in this posthow to apply conditional formatting to pivot tables.
1. Select a cell in the Values area
The first step is to select a cell from the PivotTable's range of values. If your PivotTable has multiple fields in the Values area, select a cell for the field you want to apply the format to.
2. Apply conditional format
you will find theConditional Formatting Menuon the Home tab of the ribbon. It opens up a lot of great options when it comes down to it.define rules or conditions. In my example I use thetop 10 articlesRuler.
In the example, I changed the 10 to 3 so that only the three cells with the highest values are selected. I then chose a green fill option for the shape.
This new formatting rule finds the three highest values in the range I selected. These cells are then changed to have a green fill with dark green text. However, since I started with just one cell selected instead of a range, it only changed that cell (by default, this is the highest value).
It's okay, because what interests us is whoFormat Options Menu. This menu appears next to the newly formatted cell. In this menu you canExpand the format ruleto similar environmental data.
3. Using the format options menu
You can use the Format Options menu to format the field in the Values area.
Selecting the second menu option (see image above) applies the formatting rule to all values under the Total Sales heading, including the grand total. In the image below you can see that the first three values are green.
But it's pretty obvious that the grand total will always be the highest value. As a result, you often want to exclude the grand total from your format. That way, you'll compare similar data (in this case, products 1-7) when naming your top 3. If you want to exclude the grand total, you can choose the third option from the Format Options menu: All cells displaying total sales values for the product.
4. Access format options after making changes
Oformat options button 
appears when you apply conditional formatting for the first time. It disappears after making changes to the spreadsheet. If you want to see these options again, you can find the rules atEdit Format Rule Window. You can access it by clicking onconditional formatting button, choosemanage rules, select the rule and clickedit rule.
this brings theEdit Format Rule Window. The top of the window lists the same options that appeared in our menu.
Conditional formatting with multiple fields
If your PivotTable has multiple fields in the row or column sections, it probably also has subtotal rows and columns. In this case, we need to make sure that we apply the format to the correct fields.
The easiest way is to first select a cell at that intersection BEFORE applying the conditional formatting. You can also change the cell later.
As the cell that the rule focuses on changes, you'll see that your formatting options change as well. For example, if I start the process with cell B6 selected, my options will be includedAll cells display "Revenue" valuesas well asAll cells with "Revenue" values for "Product" and "Quarter":
But when I change the focus cell to B7, the subtotal (product) fields are no longer an option. Instead my options areAll cells display "Revenue" valuesmiAll cells with "Revenue" values for "Region" and "Quarter":
the difference is essentialinclude or exclude subtotal fieldsof the range of cells you want to format.
You can change the fields that the rule applies to at any time in the Edit Formatting Rule window.
Oapply the rule tobox indicates the cell that intersects the boxes. You can select another field in the pivot table when this window is open to change fields. See the video above for more details.
What happens when the pivot table changes?
The best thing is that when the pivot table changes,Excel remembers which rules you applied. Therefore, it retains the proper format despite the reorganized data.
The purpose of a pivot table is so that you can quickly and easilyChange your perspective on data. For example, you can exit the view with a click of a button.Income by regionForbilling per employee, Forrepeats by region.
Conditional Formatting Rulesstay intactregardless of how the data changes. This includes applying filters with slicers, changing the layout and options, or moving fields within the row or column area.
For example, here is part of a pivot table withProducts listed in each region.
And here is the same pivot tableRegions under each product.
The green fill format for the top ten entries remains intact even though I have rearranged the dates.
An exception
Conditional formatting has been removedwhen any of the fields included in the format are removed from the pivot table. This is simply because Excel cannot apply rules to fields that no longer exist.
If you go to the Conditional Formatting Rules Manager after deleting one of the fields, you'll see the rule disappear completely.
Learn more about pivot tables and conditional formatting
Need a refresher on pivot tables? Moneyto watch the first of three videos in a series covering PivotTables, PivotCharts, PivotPal, and more.
Here are some other posts on conditional formatting:
- How to filter duplicates with conditional formatting
- Create a donut progress chart with conditional formatting
- 2 ways to apply conditional formatting to shapes
What kind of conditional formats would you like to learn?
This post and video explain how to apply conditional formatting to pivot tables. I show some examples in the video and in the attached excel file.
What other types of conditional formatting would you like to learn? Leave a comment below with your report format or type and I'll create some follow up tutorials.
Thank you so much! 🙂