Join Chris Dutton for an in-depth discussion in this video Accident analytics, part of Excel Workshop: Building a Dynamic Heat Map.
- View Offline
- Alright, so let's take a minute to step back…and just talk about what this project means…and why we built it in the way that we did.…So I've gone ahead and I've hidden the ribbon,…just so that we can focus purely…on the heat map itself.…And essentially,…what we've done here is…we've used data visualization…to tell a story that otherwise,…might have been lost in the raw data.…In this case,…we're quickly and intuitively identifying…the safest and most dangerous times…to travel in Cambridge.…And if I were just looking at a raw data set,…it would be incredibly difficult…to identify trends like,…how the fact that rush hour traffic…between eight to nine A.M.…
and five to six P.M.,…particularly on weekdays,…drives the highest frequencies of accidents,…or that accident rates might be…different in the summer versus the winter,…or looking at auto versus pedestrian accidents.…So not only that,…but we've added real-time elements…to help impact actual real-life behavior…and potentially promote safer travel habits,…which is actually pretty cool.…
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