Excel Conditional Formatting disappears unless smaller dataset in filter

Question

Conditional Formatting on a Power Pivot (Excel 2016 32-bit) using a Color bar is working nicely when results are page filtered to a smaller subset. But when nothing is filtered, the CF disappears, so no heat-map!

It's not a massive dataset - it is grouped but with no page filter, still only 5,200 excel rows. Any filter allowing more than around 1,000 rows makes the CF disappear. Adjust the page filter to get below that, and CF re-appears!

I don't think the CF is connected to the page filter (certainly nothing in it's settings shows that) so it looks like it just can't handle much data! Pretty poor really, so I'm either overlooking something, or simply very disappointing (esp latest version of Excel). Have tried clearing all rules and starting from scratch, but no joy.

Anybody else had this problem?

Answer 1

After almost 2 years (!) this is still the same problem, on many different desktop installations, plenty of RAM and CPU, and also upgrading to 64-bit Excel.

Looks like the CF simply times-out if it can't handle the workload, so you just have to restrict the data-sets if you want to keep the "heat-mapping" CF. HTH.