MrExcel.com - Your One Stop for Excel Tips & Solutions

Apply Conditional Formatting to a Pivot Table

December 19, 2022 - by

Apply Conditional Formatting to a Pivot Table

Problem: The new conditional formatting options in Excel are amazing, but they require special care in pivot tables. If you include the grand total row, it will get the largest data bars, and the detail cells have relatively meaningless bars.

Strategy: You can use the Manage Rules dialog to assign conditional formatting to only certain cells. You can initially create the “wrong” formatting and then edit it to refer to only the selected cells. For example, follow these steps:

  • 1. Select cells B5:B15. You want the first cell in the selection to be the correct type of cell. In this case, it is a value for a product.

  • 2. Select Home, Conditional Formatting, Data Bars, Solid Fill, Red. Excel applies data bars, but the region totals are getting the largest bars.

Data bars in a pivot table don't look good because the East region total and west region total are larger than the detail rows. There is a Pivot Table icon to the right of the last data bar.
Figure 873. Cells B8 & B12 artificially get the largest data bars.

  • 3. Open the pivot options dropdown at the bottom right corner of the pivot table. Choose to apply the formatting rule to only cells for revenue and product.

Use the drop-down menu on the Pivot Table icon. Apply Formatting Rule To... Choose All Cells Showing Sum of Revenue Values for Product. The other choices are Selected Cells or All Cells Showing Sum of Revenue values.
Figure 874. A dropdown appears in the grid.
In the Edit Formatting Rule dialog, it says Apply Rule to $B$5. Choose All Cells Showing "Sum of Revenue" values for "Product".
Figure 875. Go to the Edit Rules dialog in Excel 2007.

Results: the data bars are applied only to the detail product rows.

With the data bars no longer applied to the regional totals, the data bars work.
Figure 876. The data bars are applied only to like cells.

Additional Details: Similar settings are available for icon sets and color scales.

This article is an excerpt from Power Excel With MrExcel

Title photo by Vinicius "amnx" Amano on Unsplash