Building a Dynamic Heat Map in Excel

Description

In this course, you will learn :

• The techniques demonstrated in this course transform Excel from a basic spreadsheet programme into a dynamic and powerful heat mapping analytics tool.
• Chris Dutton provides hands-on examples to demonstrate why certain functions—DATEVALUE, VLOOKUP, COUNTIF, and others—are so important for data mapping.
• He also demonstrates how to use conditional color-scale formatting to map your data matrix and how to use dynamic filters to examine the same data with different criteria.
• The course concludes with a few finishing touches to make your heat map even more useful and engaging, as well as a sneak peek at what the Power Map addin can do.

Syllabus :

1. Project Setup

• Downloading the dataset
• Getting familiar with the data
• Project goal and creating a roadmap

 

2. Date and Time Functions

• DATEVALUE
• Categorization
• WEEKDAY
• VLOOKUP
• EOMONTH
• YEARFRAC

 

3. Conditional Statements and Logical Operators

• True Season
• Accident type
• Using ISBLANK

 

4. Building a Basic Heat Map

• Heat map framework
• COUNTIFS
• Color scale formatting
• Aggregate data SUM
• Quick QA: Identifying and eliminating inaccurate data

 

5. Adding Dynamic Filters to the Map

• Data validation
• Accident type COUNTIFS
• SEASON filter

 

6. Creating Formula-Based Formatting Rules

• Using the NOW and TODAY functions
• Formatting with ROW and COLUMN functions
• Highlighting a current moment in time

Show more