Join Chris Dutton for an in-depth discussion in this video Highlighting a current moment in time, part of Excel Workshop: Building a Dynamic Heat Map.
- View Offline
- So our last step…is to highlight the current moment in time,…in other words, the intersection of the highlighted row…and the highlighted column.…So if you don't already have the range selected,…go ahead and select E8 all the way through AB14.…What we're going to do is add a third Rule here,…and basically all that Rule is going to do…is combine the two Rules that we already created.…So I'll start with an AND formula,…and here's where I'll just replicate…the row and column formulas that we just wrote.…
So I want two things to be true…in order to Format these cells.…I want (ROW()-7…equal to the value in N2,…and I also want COLUMN()-5…equal to the value in M2.…Close off the parenthesis.…I'll set my Formatting Rules,…and in this case, again, this is going to be a single cell…that's being featured.…So, I want it to pop a little bit more.…
Choose the General Format.…I'm going to go with Bold Italic again.…I'll make it true black,…and then let's add a little Border Outline…around the cell as well.…I'll press OK,…OK, and there you go.…
Chris Dutton provides hands-on examples designed to showcase why certain functions—DATEVALUE, VLOOKUP, COUNTIF, and more—are so valuable to mapping data. He also shows how to use conditional color-scale formatting to map your data matrix, and find different ways to examine the same data with different criteria, using dynamic filters. The course wraps up with a few finishing touches to make your heat map even more useful and engaging, including a preview of what's possible with the Power Map addin.
- Setting up the heat map project
- Using functions such as WEEKDAY and VLOOKUP
- Adding conditional statements
- Formatting with the color-scale feature
- Aggregating data with SUM
- Adding dynamic filters
- Creating formula-based formatting rules
- Adding additional analytics