Apply Conditional Formatting to a Pivot Table
December 19, 2022 - by Bill Jelen
![Apply Conditional Formatting to a Pivot Table Apply Conditional Formatting to a Pivot Table](/img/excel-tips/2022/12/apply-conditional-formatting-to-a-pivot-table.jpg)
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.](/img/content/2022/12/LE10000843.jpg)
-
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.](/img/content/2022/12/LE10000844.jpg)
![In the Edit Formatting Rule dialog, it says Apply Rule to $B$5. Choose All Cells Showing "Sum of Revenue" values for "Product".](/img/content/2022/12/LE10000846.jpg)
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.](/img/content/2022/12/LE10000845.jpg)
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