Join Chris Dutton for an in-depth discussion in this video Getting familiar with the data, part of Excel Workshop: Building a Dynamic Heat Map.
- View Offline
- Okay, so now that we have our raw data,…let's dig in and see exactly what we're working with.…As you can see, in column A…we have a Crash Number field.…This is a unique identifier for each accident in the sample.…If I select the entire column A,…I can see that we have about 6,340 accidents in the sample.…Over the course of about four years.…We also have date and time in column B,…which is really the core variable…that we're going to be using to create this heat map.…
What this tells is exactly when…each accident occurred down to the minute.…We also have a Day Of Week field,…which tells us exactly which day of the week an accident…occurred, as well as Object 1 and Object 2 columns.…So, if I add a filter to Row 1…by going into the DATA menu, selecting Filter,…I can see exactly which objects…are included in the data set.…So, we're looking at accidents involving…anything from autos to bicycles,…to buses, even motorcycles and pedestrians.…
We also have location data.…We have Street Number, Street Name, and Cross Street…
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