We love using Excel with Project Online or Project Server to pull updated live data at any time. Often, our clients want to see this data with some sort of conditional formatting. Maybe it’s a heatmap throughout a numbers grid, or perhaps text highlighted in bold or red when a number exceeds a threshold.
In a static page, we would simply choose the cells that we want to show and create the conditional format. However, when working with live data in a pivot table, it’s a little trickier. We tried to work around this by choosing whole columns instead of specific cells. That way, we figured, the length of our table could shrink or grow, depending on the data included.
But we couldn’t fool Excel. The moment we refreshed our data, the target area for conditional formatting started to change. All of a sudden, we were faced with some cells that simply didn’t obey the conditional formatting rules. (Picture a sea of green cells, with a few red ones, and suddenly a few white/unformatted ones in the middle. See the “Applies to” area below:
So, we went back to the drawing board, and we found a great solution.
- Start by clicking on the upper left cell of your pivot table to select the entire table.
- When you set up conditional formatting, you will see a drop down at the top of the window. One option in the dropdown will be “This Pivot Table”. When you first create the conditional formatting, you might see the actual text from the upper left corner, this will work as well (in our case, this would be “Sum of hours”).
- Select this option.
- Set your conditional formatting. Now, when you refresh your data, the conditional formatting will stick!
If you’d like to find out about classes we offer in Reporting from Project Online or Project Server, contact us!
