Problems with Conditional Formatting in Pivot Tables in Excel 2024

[jfrsanders]

I have a source table and an associated, simple pivot table, which includes a subset of the columns of the source table. When I add data/lines to the source table and press refresh, the new data is reflected in the pivot table. However, the conditional formatting I set for the pivot table columns is not carried over to the new data, and I have no idea why. My conditional formatting in the pivot table is standard stuff, such as changing the fill color as a function of a formula, or setting the fill color as a function of a numerical range. I have asked around a lot about this, and it seems that in earlier versions of Excel, one could automatically conditionally format data added to a pivot table, by associating the conditional formatting with the title of a column. However, in my new 2024 version of Excel, I do not find this option. It seems strange that something offered on an older version of Excel would not be available on a newer version. Can somebody please help me on this? Thanks a lot.

Below, I have pasted a screenshot of a simple source table (left) and associated pivot table (right). I conditionally formatted the Party column of the pivot table to have a different fill color depending on the letter. However, when I added two more lines to the source table, this conditionally formatting is not carried over to the new data in the pivot table.

 

[BrerRabbit]

Can you post the formula you used in the conditional formatting please?

 

[BrerRabbit]

I4 formula = =SORT(Table1,2)

conditional formatting formula: =$M4="D"

$ keeps it to the column. 4 will become 5 will become 6 in the area the formula applies to.

Applies to: make it as long as you want. This will facilitate the table becoming bigger. So if your table is 200 rows, make the conditional formatting for 400 rows. Or the entire column ie M:M in this instance.

 

[jfrsanders]

Thanks a lot, Brer Rabbit. Extending the conditional formatting beyond the anticipated length of my pivot table solves most of my problems, e.g., Cell Value contains 'I' -> $I$4:$I$1048576, instead of just $I$4:$I$10.