Excel School 2.0 Lesson Plan

Hi ,

Use this Lesson Plan to learn Excel School Lessons. Just follow the lessons in the order they are listed.
You can find download links inside the lessons.

Note: This is for Excel School or Excel School Dashboards.

Say hello

Before jumping in to the lessons, take a minute to say hello to rest of the classmates. Click here.

Module 0 – Getting started

The purpose of this module is to help you understand and use modern Excel (2016, Office 365 or Excel 2019) better. Even if you have been using Excel for a while, I suggest completing this module. It is quick and easy one. You might learn some very cool things too.

  • Watch duration: 2 hours
  • Practice duration: 2 hours
  • Lessons: 10
  1. Introduction to Excel
  2. Working with data
  3. Cleaning up data
  4. Using Power Query – Quick intro
  5. Writing formulas
  6. Creating pivot tables
  7. Making charts
  8. Using conditional formatting
  9. Working with filters
  10. Using shortcuts & customization options

Module 1 – Working with Data, Tables & Power Query

Now that you have a good understanding of where everything is in modern Excel, let’s get some data in and understand how to work with it. This module has a mix of beginner and advanced lessons. Feel free to skip advanced lessons until you are ready.

  • Watch duration: 4.5 hours
  • Practice duration: 5 hours
  • Lessons: 7
  1. Bad data to good data – journey – Power Query
  2. Creating and using Excel Tables
  3. Filters & Slicers on Tables
  4. Introduction to conditional formatting
  5. [Advanced] Conditional formatting formulas + goal tracker app NEW
  6. [Advanced] Bad data example 2 – Unpivoting data
  7. [Advanced] Bad data example 3 – merging multiple files

Module 2 – Analyzing data with formulas

What good is data if you cannot analyze it. Excel offers 100s of formulas and operations to creatively mix and manipulate your data. This module explores the beautiful, powerful and insightful world of formulas. Some of lessons are advanced. So feel free to skip them until you are ready.

  • Watch duration: 5.5 hours
  • Practice duration: 6 hours
  • Lessons: 15
  1. Referencing data – cell, named, structural
  2. IF formula in Excel
  3. Statistics & summaries
  4. Conditional sums, counts
  5. Lookups – VLOOKUP, XLOOKUP, INDEX+MATCH, HLOOKUP
  6. [NEW] Dynamic Array Functions in Excel – FILTER, SORT, UNIQUE, SEQUENCE etc.
  7. [Adv] Advanced Lookups in Excel
  8. Working with dates in formulas
  9. Text formulas
  10. [Adv] Sentiment analysis with Excel – case study
  11. [Adv] Exploratory Data Analysis – Hotel Reviews case study

Module 3 – Data analysis with Pivot Tables

You can also use Pivot tables for data analysis. Pivot Tables offer a convenient, quick and graphical way to explore your data and answer questions. When combined with slicers, pivot charts and multi-table data models, Pivot Tables can be truly powerful and awesome.

  • Watch duration: 4 hours
  • Practice duration: 4 hours
  • Lessons: 8
  1. Introduction to Pivot tables & advanced tips
  2. Grouping data in Pivots
  3. Calculating things in Pivots
  4. [Adv] GETPIVOTDATA – talking to pivots
  5. Formatting pivot tables, charts and slicers
  6. Answering business questions – Pivot table case study
  7. [New] Sparklines + Pivot Tables – trick
  8. [NEW] Dynamic Business Dashboard with Pivot Tables alone

Module 4 – Making charts & graphics

Now that your data analysis is complete, let’s make some pretty, functional and elegant graphs in Excel. Modern Excel offers several powerful & handy charts. Learn all about charting engine in Excel, how to create slick interactive charts, how to answer common business questions in this module.

  • Watch duration: 4 hours
  • Practice duration: 5 hours
  • Lessons: 8
  1. How to pick right chart for your data?
  2. Introduction to Excel charting
  3. Anatomy of an Excel chart
  4. Working with new charts in Excel 2016 / Office 365
  5. [Adv] Conditional charts
  6. [Adv] Budget vs. Actual charts
  7. [Adv] Then vs. Now charts
  8. Sparklines, in-cell charting in Excel

Module 5 – Productivity & Advanced Excel

Learn how to up your Excel game with this module. Several hand-picked tips, techniques and ideas for you to play with and combine them with other concepts learned in Modules 0 to 4.

  • Watch duration: 3.5 hours
  • Practice duration: 3.5 hours
  • Lessons: 9
  1. Data validation in Excel
  2. Form controls in Excel
  3. Working with shapes & pictures
  4. Making chart & workbook templates
  5. Unpivoting complex data – Power Query case study NEW
  6. [Advanced] VLOOKUP Multiple Matches trick NEW
  7. Cascading Drop-downs in Excel
  8. Customizing Excel for productivity
  9. Chandoo’s favorite time saving features of Excel

Module 6 – Excel Dashboards

Dashboards are one page business reports. They are used for sharing key ideas & information with executives, decision makers or customers. Learn how to create interactive, visual and powerful dashboards in this module.

Note: Lessons in this module are long and intense. Please clear your schedules before committing for best results.

  • Watch duration: 12 hours
  • Practice duration: 16 hours
  • Lessons: 9
  1. Our first dashboard – dynamic, interactive, automated and wow
  2. Dashboard creation process
  3. [Adv] Sales Performance Dashboard
  4. [Adv] Employee Vacation Tracker & Dashboard
  5. Employee Training Tracker
  6. [Adv ++] Hotel KPI Dashboard
  7. [Bonus] Web Analytics Dashboard with Excel
  8. [NEW] Awesome Sales Dashboard with Excel
  9. Dynamic Business Dashboard with Pivot Tables alone
  10. [NEW] One formula dashboard with Excel’s LET function