Join Chris Dutton for an in-depth discussion in this video True Season, part of Excel Workshop: Building a Dynamic Heat Map.
- View Offline
- All right, so we've got a bit more data prep to do…before we actually start building the heat map itself.…And the first thing that we're going…to do is redefine our season variable…using the YEARFRAC values that we created.…So, we're going to do that using conditional statements…which is a fancy term for IF THEN statements in Excel…as well as logical operators…which are your basic greater than,…less than, equal to, not equal to signs, etcetera.…So conditional formulas essentially just check…whether certain conditions or logical tests are true…or false and they return a value based on the result.…
So, one word of warning…when you're using conditional formulas is…that if you include a text reference within your formula,…make sure you surround it in quotes.…If you don't, you'll get a #NAME? error.…So there are four components of conditional formulas.…The first component is the conditional itself…so these are your IF, AND, OR, and NOT statements…and often times, these are nested together…so you'll use an AND or an OR statement…
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