Insights from Data using Excel consists of 3 hours of idea-packed short lessons and real-life case studies. All lessons are less than 5 minutes each.
Introduction (15 minutes)
Introduction to the course |
Introduction to sort, filter and pivot tables, personal spending example |
Preparing for exploration: personal finance example |
Quiz 1 |
Exercise |
Useful techniques before sorting and filtering |
Sorting and filtering (30 minutes)
Installing filter buttons and sorting |
Filtering by categories |
Filtering by numerical values |
Combining filters |
Challenge #1: Find at least 2 glitches in the table |
Challenge answer |
Review and summary of sort and filter |
PivotTables (40 minutes)
Building a PivotTable |
Exploring a PivotTable |
Quiz2 |
Turning continuous values into categories with grouping |
Filtering and a couple try-it-yourself pivot tables |
Consultant time log example with filtering |
Case Study challenge #2: summarize time log |
Consultant case study challenge solution: customer |
Analyzing categories. Store purchase analysis |
Using a PivotTable for clustering |
Quiz 3 |
“Summarized by” choices and how to use them |
Data representation matters |
Business case studies: defaulting, marketing, segmentation (35 minutes)
Who is most likely to default at this bank? |
Structuring the data for a PivotTable |
Using PivotTable average to find categories of interest |
Case Study Challenge #3: Find a high-default category |
Challenge solution |
Case Study challenge #4: Best marketing month |
Challenge |
Challenge solution |
Case Study Challenge #5: Personalized sales campaign |
Challenge |
Challenge solution |
Challenge #6: Previous contacts |
Challenge solution |
Use functions to organize your data for exploration (30 minutes)
Introduction: Issues of real-world data and missing data |
Changing categories to numbers or different names |
Introducing the function VLOOKUP |
The power of Autofill for formulas |
Filling a table by looking up in another |
Putting numbers in categories (binning) using VLOOKUP |
Using a calculated column |
Unstacking data |
Summary (5 minutes)