Excel workshop - 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
• 50+ keyboard shortcuts

Module 2: Relative References

• Understanding the concept of Relative & Absolute cells
• Preparing formula ranges across large datasets without errors
• Concept of Dynamic Arrays

Module 3: Lookups

• Understanding the ethos of lookups – ( VLOOKUP vs 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
• Understand modern functional programming based LET & LAMBDA 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
• Basics of M language
• Web scraping and real data cleaning using Power Query

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)