Advanced Excel

Module 1: Intermediate Excel Functions

• Lookup and Reference Functions:
• VLOOKUP, HLOOKUP, INDEX, MATCH
• XLOOKUP (if applicable)
• CHOOSE, OFFSET

• Logical Functions:
• AND, OR, NOT, IF, IFS, nested IFs
• XOR, IFERROR

• Mathematical Functions:
• SUM, AVERAGE, COUNT, MAX, MIN, MEDIAN, MODE
• ROUND, CEILING, FLOOR, INT, TRUNC
• SQRT, POWER, EXP, LN, LOG

Module 2: Data Analysis and Visualization

• Pivot Tables:
• Creating and analyzing pivot tables
• Slicers and filters
• Calculated fields and items

• Data Validation:
• Input data validation
• Error messages

• Conditional Formatting:
• Highlight cells based on conditions
• Data bars, color scales, icon sets

• Charts and Graphs:
• Creating various chart types (line, bar, pie, scatter, etc.)
• Customizing charts and graphs

Module 3: Advanced Excel Features

• Macros and VBA:
• Introduction to VBA
• Recording and editing macros
• Writing custom VBA code
• Event procedures

• Power Query:
• Importing and transforming data
• Data cleaning and manipulation
• Creating custom queries

• Power Pivot:
• Creating data models
• Using DAX formulas
• Building relationships between tables

Module 4: Data Analysis Techniques

• What-If Analysis:
• Goal Seek
• Data Tables
• Scenario Manager

Module 5: Advanced Excel Formulas

• Array Formulas:
• Entering and using array formulas
• SUMPRODUCT, TRANSPOSE, MMULT

• Text Functions:
• CONCATENATE, LEFT, RIGHT, MID, FIND, REPLACE, SUBSTITUTE

• Date and Time Functions:
• DATE, TIME, NOW, TODAY
• Calculating differences and intervals

• Logical and Mathematical Operators:
• Comparison operators (>, <, =, >=, <=, <>)
• Logical operators (AND, OR, NOT)
• Arithmetic operators (+, -, *, /, ^)

Module 6: Advanced Excel Projects

• Real-world applications:
• Financial modeling
• Project management
• Data analysis

• Hands-on projects:
• Building complex spreadsheets
• Sales Dashboard

• Presentation and Communication:
• Effective storytelling with data

Book Now