Advanced Excel Learning Roadmap
Stage 1: Core Functions & Formulas
Goals: Master logical, lookup, and text functions. Learn nested formulas and dynamic arrays.
Topics:
- IF, IFS, AND, OR, IFERROR
- VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP
- TEXT, LEFT, RIGHT, MID, CONCAT, TEXTJOIN
- FILTER, SORT, UNIQUE, SEQUENCE
Exercises:
- Create a student grade sheet using nested IF
- Build a product lookup tool with INDEX + MATCH
- Filter and sort customer data dynamically
Resources:
- ExcelJet: https://exceljet.net/
- Leila Gharani YouTube: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA
- Microsoft Functions List: https://support.microsoft.com/en-us/excel
Stage 2: Data Cleaning & Validation
Goals: Automate cleaning repetitive data. Set rules and constraints on input.
Topics:
- Remove duplicates, Flash Fill
- Text to Columns
- Data Validation (lists, custom formulas)
- Named ranges
Exercises:
- Clean a list of emails and extract domains
- Validate input for form fields (e.g., phone numbers)
Resources:
- ExcelIsFun Channel: https://www.youtube.com/user/ExcelIsFun
- Chandoo.org Data Cleaning: https://chandoo.org/wp/excel-data-cleaning/
Stage 3: PivotTables & Charts
Goals: Summarize data interactively. Create dynamic charts.
Topics:
- Create PivotTables and PivotCharts
- Use slicers, timelines, filters
- Combine charts, use secondary axes
Exercises:
- Create a sales dashboard with slicers
- Track monthly expenses with a PivotChart
Resources:
- MyExcelOnline PivotTable Course: https://www.myexcelonline.com/free-pivot-table-webinar/
- Microsoft Support - PivotTables: https://support.microsoft.com/en-us/excel
Stage 4: Power Query & Power Pivot
Goals: Automate ETL processes. Perform data modeling and advanced calculations.
Topics:
- Import and transform data using Power Query
- Merge and append tables
- Build data models with relationships
- DAX formulas
Exercises:
- Combine multiple CSVs into one cleaned table
- Create a data model linking orders, customers, and products
Resources:
- Leila Gharani Power Query Playlist:
https://www.youtube.com/playlist?list=PLmHVyfmcRKyyuGHhM-QugOzJY4V1NldGZ
- Radacad DAX & Power Pivot Tutorials: https://radacad.com/blog
Stage 5: Dashboards & Interactivity
Goals: Create professional dashboards. Use dynamic named ranges and controls.
Topics:
- Interactive charts, dynamic ranges
- Form controls (dropdowns, checkboxes)
- Camera tool, conditional formatting
Exercises:
- Build a financial KPI dashboard
- Create a project status tracker with visual cues
Resources:
- Excel Campus Dashboard Series:
https://www.excelcampus.com/charts/create-interactive-dashboard/
- Chandoo Dashboard Examples: https://chandoo.org/wp/excel-dashboards/
Stage 6 (Optional): Macros & VBA
Goals: Automate tasks with recorded or written code.
Topics:
- Record and run macros
- Write basic VBA procedures
- Automate form submissions or reports
Exercises:
- Create a macro to clean and format raw data
- Automate sending Excel reports
Resources:
- Wise Owl VBA Playlist:
https://www.youtube.com/playlist?list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs
- Automate Excel VBA Tutorials: https://www.automateexcel.com/vba/
