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)