Apply Conditional Formatting to a Pivot Table
December 19, 2022 - by Bill Jelen

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.

-
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.


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

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