Join Chris Dutton for an in-depth discussion in this video SEASON filter, part of Excel Workshop: Building a Dynamic Heat Map.
- View Offline
- All right, our next step is going to be…to create a second filter…to display the data by season.…To do this, we're going to take three steps.…We're going to start by creating a "Season" filter…just like we created the "Accident Type" filter…using data validation.…The only difference is we're going to include an option…for "ALL" season,…not just the individual four seasons.…The second step is going to be…to adjust the COUNTIFS function…to account for cases where the user has selected…the "ALL" option.…And then third, we're going to duplicate that entire formula…and modify it to pull in season-specific data.…
So let's get started and create our "Season" filter.…I'm going to select cell I2…and just type Season here with a colon…and use the Format Painter…to basically apply the same formatting…as my original filter.…I'm going to merge cells J and K.…And then, again, I need to create a source list…for the second filter.…I'm going to include an "ALL" option first…followed by each individual season,…Spring, Summer, Fall and Winter.…
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