Call Us Today! 1-800-895-3254
[email protected]
Microsoft Training
Integent offers a comprehensive portfolio of training services, with an ideal fit for every organization. Ranging from general courses to custom solutions, we have you covered.
Learn About TrainingSee Full list of Courses
Microsoft Consulting
Integent provides consulting services for a variety of Microsoft applications including Microsoft Project, Project Online, Project for the Web, Dynamics 365, and Microsoft SharePoint.
Learn About Consulting
Microsoft Support
Microsoft technologies set the standard for project portfolio management, business applications, collaboration, and more. Integent has flexible solutions to support every customer.
Learn About Support
Microsoft Project
Integent has the knowledge and experience to help design and deploy a solution to help your organization manage projects and resources more effectively
Learn More About Microsoft Project
Microsoft Dynamics 365
Integent can help you nsure business continuity with a custom cloud solution that connects sales, service, finance, and operations teams to deliver results.
Learn More About Dynamics 365
Microsoft SharePoint
SharePoint is Microsoft’s collaboration platform that is a place where team members can communicate, exchange data, share files and much more.
Learn More About Microsoft SharePoint
Microsoft Power Platform
Consisting of Power BI, Power Apps, Power Automate and Power Virtual Agents, the Microsoft Power Platform helps streamline and improve business functions.
Learn More About The Power Platform
About Integent
Integent was founded in 2009 on the principles of building long-lasting relationships with customers, exceeding expectations, and providing outstanding customer service. 

We’re dedicated to providing a customized solution backed by our knowledgeable team of Microsoft experts.
Discover What Makes Us Different
GSA Schedule
Integent is GSA Schedule Contract holder. A GSA schedule helps simplify selling our services to government customers.
Read More Here
News
Stay informed on Integent’s latest company updates and other important industry information. Our passion is customer success.
Read The Latest News
Videos
Learning new software doesn’t have to be difficult with our library of dozens of free training videos. Our video training always has something new.
Browse Our Training Video Library
Blog
Our industry experts share best practices and common pitfalls to avoid, in order to get the most out of your technology solutions.
Discover More

October 12, 2015

Microsoft Excel–Creating a Heatmap using Microsoft Project Online data that Keeps its Formatting

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!

© Copyright 2023 | Integent.com | All Rights Reserved
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram