You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel. Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Advanced Excel is a comprehensive course that gives you a clear understanding of the most recent and advanced features in Microsoft Excel. This course is for anyone who works with complex data in MS-Excel to build charts, tables, and technical reports. It will be useful to all readers who regularly use MS-Excel to analyze data.

Advaced Excel Course Overview

Module 1: Excel Refresher & Core Concepts

• Cell referencing (relative, absolute, mixed)
• Data types and formatting
• Efficient worksheet navigation
• Named ranges and structured references

Module 2: Advanced Formulas & Functions

• Logical functions: IF, IFS, AND, OR, NOT
• Lookup & reference functions: VLOOKUP, HLOOKUP,
  
  XLOOKUP, INDEX + MATCH
• Text functions: TEXT, LEFT, RIGHT, MID, LEN,
  
  SUBSTITUTE, TRIM
• Date & time functions: TODAY(), NOW(), EDATE(),
  
  EOMONTH(), NETWORKDAYS()
• Math & statistical functions: ROUND, SUMIFS,
  
  COUNTIFS, AVERAGEIFS
• Array formulas & dynamic arrays: FILTER, SORT,
  
  UNIQUE, SEQUENCE, LET, LAMBDA

Module 3: Data Tools and Validation

• Data validation (drop-downs, custom rules)
• Remove duplicates, text-to-columns
• Flash Fill & Auto Fill tricks
• Consolidate and outline data

Module 4: Data Analysis with PivotTables

• Creating and customizing PivotTables
• Grouping data (dates, numbers, categories)
• Calculated fields and items
• Slicers and timelines
• Using PivotCharts

Module 5: Advanced Charting Techniques

• Combo charts
• Dynamic charts using named ranges or tables
• Sparklines
• Conditional formatting in charts
• Bullet charts, Gantt charts, KPI dashboards

Module 6: Power Query (Get & Transform)

• Introduction to Power Query
• Importing data from multiple sources
  
  (Excel, CSV, Web, etc.)
• Transforming and cleaning data
• Merging and appending queries
• Creating reusable queries

Module 7: Power Pivot and Data Modeling

• Introduction to Power Pivot and the Data Model
• Creating relationships between tables
• Calculated columns and measures
• DAX functions: CALCULATE, SUMX, FILTER, RELATED,
  
  ALL, VALUES
• Building advanced dashboards with Power Pivot

Module 8: Dashboard Design & Data Visualization

• • Principles of effective dashboard design
• • Using form controls (sliders, buttons, etc.)
• • Interactive dashboards with PivotTables & slicers
• • Real-time KPI tracking

Module 10: Excel Best Practices & Optimization

• Workbook protection and data security
• Preventing and handling errors
• Performance optimization tips

🧠 Capstone Project / Final Assessment

• Build a full interactive dashboard or automated report
• Use Power Query, PivotTables, and advanced formulas
• Real-world dataset analysis and presentation