Excel - Recorded Masterclass - Second edition

Speaker: Chinmaya Amte (Ex-EY, Speaker at 5 IIMs)

Disclaimers / To-Dos

The participants need to have access to a laptop with a Microsoft 365 subscription. You can check out the link here: www.office.com. You can get 1-month free access based on a credit card. This is needed for formulas like TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.
Copilot in Excel requires additional licenses from Microsoft, so you may not have a hands-on experience for this module.
Mac and work laptops may have restrictions on the installation of add-ins, etc.

Day 1

Module 1: Data Cleaning

Working with raw B-school student data to derive insights
SUBSTITUTE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, TEXT TO COLUMNS
AVERAGE, MIN, MAX, SMALL, LARGE and use of wildcards

Module 2: Relative References

Understanding the concept of Relative & Absolute cells
Preparing formula ranges across large datasets without errors
Using the efficiency hack – ALT + ENTER to make multiple lines in Excel

Module 3: Lookups

Understanding the ethos of lookups – (INDEX MATCH, XLOOKUP)
Common lookup problems (e.g., “text slightly different”)
How to do Multi-lookup in Excel
Multiple-criteria / 3-variable-based lookups

Module 4: Python in Excel

How to type Python codes inside Excel cells
How to clean and analyze data using Python within Excel
Understanding the basics of data structures

Day 2

Module 5: Modern Excel Functions

Discover the beauty of Matrix and how to build scalable automated models
Learn FILTER, SEQUENCE, SORT, TEXTJOIN and latest GROUPBY functions
SUMIFS and LAMBDA-based MAP & SCAN functions

Module 6: AI and Excel

How to use ChatGPT inside Excel
Learning Copilot in Excel
Deterministic vs Probabilistic approach for AI vs Excel

Module 7: VBA and Macros

What is VBA? What are Macros?
Playbook for how to download and use Add-ins
Automation in Excel

Module 8: Power Query

Understanding the ETL tool and M language
Web scraping and real data cleaning using Power Query
Demo of data presentation using Excel

Module 9: Real-Life Case Studies

Team exercise on case studies asked in job interviews and faced by consultants in their day-to-day jobs
Bonus practice projects that can be added to your CV
Discussion-based reflection on sample cases (not hands-on)