Advanced Excel Full Syllabus

Module 1: Introduction to Advanced Excel

• Understanding the Excel Interface & Shortcuts
• Customizing the Ribbon and Quick Access Toolbar
• Excel Settings & Options

Module 2: Advanced Formulas & Functions

Logical & Conditional Functions

• IF, Nested IF, AND, OR
• IFERROR, IFNA, IFS
• CHOOSE, SWITCH

Lookup & Reference Functions

• VLOOKUP, HLOOKUP
• XLOOKUP (Latest Excel versions)
• INDEX, MATCH, INDIRECT
• OFFSET, TRANSPOSE

Text Functions

• CONCATENATE, TEXTJOIN
• LEFT, RIGHT, MID
• LEN, FIND, SEARCH, SUBSTITUTE, REPT
• PROPER, UPPER, LOWER, TRIM

Date & Time Functions

• TODAY, NOW
• DATE, YEAR, MONTH, DAY
• EDATE, EOMONTH
• NETWORKDAYS, WORKDAY

Mathematical & Statistical Functions

• SUMIF, SUMIFS
• COUNTIF, COUNTIFS
• AVERAGEIF, AVERAGEIFS
• ROUND, ROUNDUP, ROUNDDOWN
• RANK, PERCENTILE, QUARTILE

Financial Functions

• PMT, FV, PV
• NPV, IRR
• RATE, IPMT, CUMIPMT

Module 3: Data Analysis & Visualization

Data Validation & Cleaning

• Remove Duplicates
• Data Validation (Dropdown lists, Input messages, Error alerts)
• Text to Columns, Flash Fill

Sorting & Filtering

• Custom Sorting
• Advanced Filters
• Removing Duplicates

Conditional Formatting

• Highlighting duplicate values
• Data Bars, Color Scales, Icon Sets
• Custom formulas in conditional formatting

Pivot Tables & Pivot Charts

• Creating and Modifying Pivot Tables
• Slicers and Timelines
• Calculated Fields & Items
• Creating Pivot Charts

Advanced Charts & Graphs

• Combo Charts
• Waterfall Chart, Histogram, Pareto
• Gantt Chart, Heat Map
• Sparklines

Module 4: Power Query & Power Pivot

• Introduction to Power Query
• Transforming and Cleaning Data
• Merging and Appending Queries
• Introduction to Data Model
• Using DAX (Data Analysis Expressions)
• Relationships and Measures in Power Pivot

Module 5: Automation with Macros & VBA

Introduction to Macros

• Recording and Running Macros
• Assigning Macros to Buttons

VBA (Visual Basic for Applications)

• Introduction to VBA
• Variables, Data Types, and Operators
• Loops (For, Do While, Do Until)
• Conditional Statements (If-Else, Select Case)
• Working with Worksheets, Ranges, and Cells
• Creating User Forms
• Error Handling in VBA

Module 6: Advanced Data Analysis & Forecasting

• What-If Analysis (Goal Seek, Data Tables, Scenario Manager)
• Solver Tool for Optimization
• Forecasting Techniques (TREND, LINEST, Moving Average)

Module 7: Collaboration & Security

• Protecting Worksheets & Workbooks
• Data Encryption & Digital Signatures
• Tracking Changes & Comments
• Sharing & Merging Workbooks

Module 8: Excel Integration & Automation

• Importing Data from External Sources
• Linking Excel with Word, PowerPoint, and Access
• Connecting Excel with SQL Databases
• Automating Reports with Power Automate