3/15/24, 12:04 PM Excel School 2.0 Lesson Plan – Online Classroom – Chandoo.
org
Online Classroom – [Link]
Excel | VBA | Dashboards | PowerPivot & More…
Excel School 2.0 Lesson Plan
Hi Satyanarayana,
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
[Link] 1/5
3/15/24, 12:04 PM Excel School 2.0 Lesson Plan – Online Classroom – [Link]
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.
[Link] 2/5
3/15/24, 12:04 PM Excel School 2.0 Lesson Plan – Online Classroom – [Link]
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
[Link] 3/5
3/15/24, 12:04 PM Excel School 2.0 Lesson Plan – Online Classroom – [Link]
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
[Link] 4/5
3/15/24, 12:04 PM Excel School 2.0 Lesson Plan – Online Classroom – [Link]
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
Online Classroom – [Link] / Proudly powered by WordPress
[Link] 5/5
