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.
1. Project Setup
- Downloading the dataset
- Getting familiar with the data
- Project goal and creating a roadmap
2. Date and Time Functions
3. Conditional Statements and Logical Operators
- True Season
- Accident type
- Using ISBLANK
4. Building a Basic Heat Map
- Heat map framework
- 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