Join Chris Dutton for an in-depth discussion in this video Quick QA: Identifying and eliminating inaccurate data, part of Building a Dynamic Heat Map in Excel.
- So, one thing that's great about…using a heat map like this…is that not only does it help to visualize trends…and stories in the data,…but it can also help to identify issues or errors…with the raw data itself.…So looking at the map I notice something…a little bit suspicious here,…which is that the frequency of accidents occurring…during the midnight hour feels a little bit high,…especially compared to one a.m. or eleven p.m.…So what I can do is just jump back to the accident data.…
And let's just filter where hour equals 0,…and where minute equals 0.…And what this will do is show us the number…of observations in our sample that are time stamped…at exactly midnight to the dot.…And as you can see, there's a disproportionate number…of observations that are time stamped at exactly midnight.…So my hypothesis here is that any accidents…that don't have a known accident time…are being categorized with a 00 time stamp,…and not accurately reflecting accidents that took place…exactly at midnight.…
So I think our best bet here will be to eliminate…
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.