Excel–Creating a Heatmap that Keeps its Formatting

Rebecca Culver's avatar
posted by
 
10/12/2015
 
2038 views

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.

image

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.

image

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:

image

So, we went back to the drawing board, and we found a great solution.

  1. Start by clicking on the upper left cell of your pivot table to select the entire table.
  2. 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”).
  3. Select this option.
  4. Set your conditional formatting.  Now, when you refresh your data, the conditional formatting will stick!

image

If you’d like to find out about classes we offer in Reporting from Project Online or Project Server, contact us!

Comments

Got something to say? Join the discussion »

leave a reply

 [Quick Submit with Ctrl+Enter]

Remember my details
Notify me of followup comments via e-mail