2015
Lynda
Chris Dutton
1:34
English

You don't need special software to create data visualizations. With some advanced functions, formatting, and filters, you can create these heat maps right in Microsoft Excel. The techniques shown in this course transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool for heat mapping.
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.

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

Download File Size:287.74 MB

---