Join Chris Dutton for an in-depth discussion in this video Accident type, part of Building a Dynamic Heat Map in Excel.
- All right, so the next thing that we need to do here…is categorize what type of accident we're looking at.…If we look at the raw data, we have two columns,…column N and O…which define the objects involved in the accident.…If you expand the filter you can see…which values these columns can take.…We've got accidents involved in…automobiles, bicycles, buses, mopeds, even pedestrians,…so we've got a whole number of values…that those columns can take.…What we're going to do is we're going to use…a conditional formula…to read those values and determine how to categorize…what type of accident occurred.…
So let's go ahead and add some columns…to the left of of column P.…We'd insert three columns.…I'm just pressing F4 here…which repeats the last action that I just took.…I'm going to call column P Auto,…column Q, Bicycle,…and R, Pedestrian.…These are the three accident types…that I want to categorize.…Why don't we just start by using a combination of…IF and OR in column P…to categorize our auto accidents.…
Basically, what I'm trying to do here is I'm going to say,…
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
Skill Level Intermediate
Data Visualization for Data Analystswith Bill Shander1h 31m Beginner
1. Project Setup
2. Date and Time Functions
3. Conditional Statements and Logical Operators
4. Building a Basic Heat Map
5. Adding Dynamic Filters to the Map
6. Creating Formula-Based Formatting Rules
7. Wrapping Up
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.