Author
Chris Dutton
Released
11/30/2015Chris 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
Duration
Views
- [Voiceover] Hey everyone, I'm Chris Dutton and welcome to Building Excel Heat Maps. In this course we'll use cell functions and advanced formatting rules to transform raw data into a custom, dynamic heat map. We're gonna start by downloading a raw data set and importing it directly into Excel. I'll show you how to use date and time functions and nested logical operators to create custom fields and data dimensions. After that we'll use data validation rules and count if functions to create the source data for our heat map. And then finally, we'll use custom cell formats and advanced conditional formatting rules to bring our visualization to life.
Ready to get started? Let's dive in with Building Excel Heat Maps.
-
Introduction
-
Welcome46s
-
-
1. Project Setup
-
2. Date and Time Functions
-
3. Conditional Statements and Logical Operators
-
True Season6m 14s
-
Accident type5m 58s
-
Using ISBLANK3m 2s
-
-
4. Building a Basic Heat Map
-
Heat map framework6m 58s
-
COUNTIFS7m 31s
-
Color scale formatting2m 32s
-
Aggregate data SUM1m 54s
-
-
5. Adding Dynamic Filters to the Map
-
Data validation3m 16s
-
Accident type COUNTIFS3m 4s
-
SEASON filter6m 7s
-
-
6. Creating Formula-Based Formatting Rules
-
7. Wrapping Up
-
Adding the finishing touches2m 46s
-
Accident analytics2m 9s
-
-
Conclusion
-
Next steps37s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press 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.
Share this video
Embed this video
Video: Welcome