Learn Advance Excel As A Professional
Course Contents
Module 1: Advanced Excel Fundamentals
- Recap of Basic Excel Concepts
- Best Practices for Excel Efficiency
- Customizing the Excel Interface
- Keyboard Shortcuts for Power Users
- Working with Large Data Sets
Module 2: Advanced Formulas & Functions
- Logical Functions: IF, AND, OR, IFERROR, IFS
- Lookup & Reference: VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH
- Text Functions: TEXT, LEFT, RIGHT, MID, TRIM, CONCAT, TEXTJOIN
- Date & Time: TODAY, NOW, DATEDIF, NETWORKDAYS, EDATE
- Statistical Functions: COUNTIFS, SUMIFS, AVERAGEIFS
- Dynamic Array Formulas: FILTER, SORT, UNIQUE, SEQUENCE
Module 3: Data Tools & Data Cleaning
- Data Validation (Dropdowns, Custom Rules)
- Text to Columns
- Remove Duplicates
- Flash Fill
- Power of Find & Replace
- Advanced Sorting and Filtering
Module 4: Charts & Data Visualization
- Creating and Formatting Charts
- Combo Charts
- Dynamic Charts with Named Ranges
- Sparklines
- Conditional Formatting with Formulas
- KPI Indicators using Conditional Formatting
Module 5: Pivot Tables & Pivot Charts
- Creating Pivot Tables from scratch
- Grouping, Filtering, and Slicing Data
- Value Field Settings (Summarise & Show Values As)
- Pivot Charts
- Slicers & Timeline Filters
- Calculated Fields in Pivot
Module 6: Advanced Data Analysis Tools
- What-If Analysis (Goal Seek, Scenario Manager, Data Table)
- Solver Tool
- Subtotals & Grouping
- Consolidation of Data
- Outlining & Data Grouping
Module 7: Excel Dashboard Development
- Principles of Dashboard Design
- Designing Interactive Dashboards
- Linking Charts & Tables with Slicers
- Using Form Controls (Drop-downs, Checkboxes)
- Combining PivotTables, Charts & Formulas
- Dashboard Project with KPI Metrics
Module 8: Power Query (Get & Transform)
- Introduction to Power Query
- Importing Data from Multiple Sources (Excel, Web, Text, etc.)
- Transforming Data: Split, Merge, Replace, Fill, Unpivot
- Combining & Appending Queries
- Creating Custom Columns
Module 9: Power Pivot & Data Model
- Introduction to Data Models
- Creating Relationships Between Tables
- Calculated Columns & Measures
- Introduction to DAX (Data Analysis Expressions)
- Using DAX Functions: CALCULATE, RELATED, SUMX
Module 10: Excel Automation with Macros (VBA Introduction)
- Understanding Macros & VBA
- Recording & Running Macros
- Assigning Macros to Buttons
- Basics of VBA Editor
- Writing Simple VBA Scripts