The webinar covers a wide spectrum of Excel skills, from basic functions to advanced techniques like Dynamic Arrays and Power Query. Participants learn data visualization, cleaning, and analysis, along with practical applications such as financial modeling and web scraping. Through real-life case studies, they gain proficiency in Excel, enhancing efficiency and problem-solving abilities.
Date : 22nd & 23rd June 2024, Saturday & Sunday
Timings : 10 am – 02:00 pm GST
Mode : Zoom
Register your interest here for a callback
Overview
Module 1
- Text to Columns and Textsplit (Theory).
- =MIN(), =MAX(), =SMALL(), =LARGE() functions & Microsoft support Page Trick.
- Wildcards in Excel (Asterisks *).
- 100+ Keyboard shortcuts to improve speed & efficiency
- Data Cleaning using =SUBSTITUTE, =UNICHAR, =UNICODE
- Data Visualisation & Auto - update chart using Excel Tables (Dual Axes Charts).
- Custom Formats & Conditional Formatting in Excel.
- =RIGHT(), =FIND, =ISERROR, =NOT functions
- =AVERAGE(), =COUNTIF() with Text Inputs
- Average Function & Trick to learn any function in Excel from support page.
- Better Analyst needs approach to clean data to perform analysis/mathematics.
Module 2
- Concept of Date in Excel and calculating last working day of notice period for Mr. Robot using Date functions (EOMONTH,(EOMONTH, EDATE, ISNUMBER).
- Understanding relative & absolute cell referencing in Excel and the new way to spill formulas using Dynamic Arrays based on matrix multiplication & CSE Formulas.
- VLOOKUP and its Limitations + how to be a better analyst by adding notes & comments by know knitty - gritty of Excel.
- EMI Computation in Excel for your new iPhone & target repayment with Goal Seek (Introduction to Solver - Theory)
- Find & Replace Hacks.
- Paste Special Dialogue Box (CTRL + ALT + V or ALT + E + S).
- Understanding standard errors in Excel #SPILL, #VALUE, #REF etc.
- Serial Numbering using Flash Fill & SEQUENCE & ROW Function.
Module 3
- SUMIFS, COUNTIFS, LEN with Ampersand Functions.
- Using Pivot Tables (2D) to Analyse the workforce (organisational structure) with Slicers
- Dynamic Arrays with the new concept of Spill & # and Excel Tables
- Perform Multi-Lookups using new FILTER & TEXTJOIN function.
- Approximate lookups using the New XLOOKUP functions.
- Remove Duplicates, =UNIQUE
Module 4
- Web Scraping Data using Power Query.
- Exploring Power Query Editor for transformation of data which is not possible in traditional excel.
- Theory on Financial Modelling with case study on Income Tax Modelling & most exhaustive resource compilation to learn modelling.
- Leveraging the Machine Learning Algorithm in Excel (Flash Fill).
- Concept of (*/+) i.e AND OR Logic in Excel.
Module 5
- Making custom Keyboard Shortcuts using Macros.
- Activating Developer Tab and exploring custom add-ins in Excel
- Google Sheets (Introduction) & History of MS Excel since 1980.
- Application of AI in Excel
Module 6 : Take Home Case Studies
- Application of all topics learnt via live practical real life scenario based case studies
Attendees will master Excel from basics to advanced techniques like Dynamic Arrays and Power Query, covering data visualization, cleaning, and analysis. Practical applications such as financial modeling and web scraping will be explored through real-life case studies, enhancing problem-solving skills for diverse analytical challenges.
About the Trainer
Chinmaya Amte
Chinmaya Amte, a Big 4 associate specializing in valuation and modeling, brings 5+ years of diverse finance experience. A Mumbai University commerce graduate, he's a LinkedIn influencer with 50K+ followers, renowned for training and speaking engagements at top institutes like IIMs, ISB Hyderabad, and more. He has trained 9K+ participants, including international students, and has been a guest speaker on Analytics & Corporate Finance using MS Excel at prestigious institutions like EY, IIT - Guwahati, and various colleges at Mumbai University and Delhi University. Passionate about Excel, Analytics, Networking, and teaching.
KAGR is an Education platform for financial literacy. Partners with schools, universities, and businesses. Offers training on finance, AI, creator economy. Also provides corporate training to MNCs on financial modeling, analysis, advanced Excel, AI for finance, Power BI, and more.
