Building a Dynamic Heat Map in Excel

Introduction

• Welcome
• What you should know before watching this course
• Using the exercise files

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

7. Wrapping Up

• Adding the finishing touches
• Accident analytics
• Extension: Power Map

Conclusion

• Next steps