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
- [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.
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.