Session 1: Advanced Excel Fundamentals - Overview of Expert Excel Capabilities
- Efficient Navigation & Shortcuts
- Customizing the Excel Interface (Ribbon, Quick Access Toolbar)
- Named Ranges & Structured References
Session 2: Data Validation & Advanced Formatting- Advanced Data Validation Techniques (Custom Formulas)
- Conditional Formatting (Formulas, Icon Sets, Data Bars)
- Dynamic Formatting with INDIRECT and other functions
Session 3: Data Cleaning & Preparation- Using TEXT functions for Data Cleanup
- Remove Duplicates, TRIM, CLEAN, and Flash Fill
- Power Query Basics for Importing & Cleaning Data
Session 4: Advanced Logical & Lookup Functions- IF, IFS, AND, OR, XOR, IFERROR
- VLOOKUP vs. INDEX-MATCH vs. XLOOKUP
- Nested & Dynamic Lookup Functions
Session 5: Text, Date, and Array Formulas- TEXT, MID, LEFT, RIGHT, SEARCH, SUBSTITUTE
- DATE, EDATE, NETWORKDAYS, WEEKNUM
- Introduction to Array Formulas (Dynamic Arrays: FILTER, SORT, UNIQUE, SEQUENCE)
Session 6: Advanced Formulas in Action- Problem-solving with complex nested formulas
- Named formulas & reusable logic
- Formula auditing & error tracing
| Session 7: Pivot Tables & Pivot Charts
- Creating & Customizing Pivot Tables
- Grouping, Calculated Fields/Items
- Slicers, Timelines, and Pivot Charts
Session 8: Dashboards & Interactive Reports
- Principles of Effective Dashboards
- Dynamic Charts with Dropdowns/Slicers
- Linking data with named ranges and formulas
Session 9: Charts Mastery & Data Storytelling
- Advanced Chart Types: Combo, Waterfall, Gantt, Bullet, Sparklines
- Interactive Charts with Form Controls
- Storytelling with Data using Excel
Session 10: App Deployment & Publishing
- Connecting to External Data Sources
- Query Editor: Merge, Append, Transform
- Parameters & Data Shaping
Session 11: Power Pivot & DAX
- Introduction to Data Models
- Relationships between Tables
- Basic DAX: CALCULATE, SUMX, RELATED
Session 12: Macros & Final Project
- Introduction to VBA & Macros
- Recording & Editing Macros
- Final Project: Building a Mini-Dashboard or Business Tool
- Q&A and Certification/Feedback
|
