Excel Expert – Syllabus

MODULE 1 – ADVANCED EXCEL FUNCTIONS

• Advanced IF and Nested Functions

• IFS, IFERROR and IFNA

• SUMIFS, COUNTIFS and AVERAGEIFS

• Advanced Logical Functions

• AGGREGATE

• SUBTOTAL

• Dynamic Array Functions

• FILTER

• SORT and SORTBY

• UNIQUE

• SEQUENCE

• RANDARRAY

MODULE 2 – ADVANCED LOOKUP AND REFERENCE

• Advanced XLOOKUP

• INDEX and MATCH

• XMATCH

• Two-Way Lookup

• Multiple-Criteria Lookup

• Dynamic Lookup Techniques

• Lookup Across Worksheets

• Lookup Across Workbooks

MODULE 3 – ADVANCED DATA CLEANING

• Data Quality and Data Preparation

• Removing Duplicates

• Cleaning Text Data

• TRIM, CLEAN and SUBSTITUTE

• Text Splitting and Combining

• Flash Fill

• Text to Columns

• Handling Missing Data

• Error Handling

MODULE 4 – POWER QUERY

• Introduction to Power Query

• Importing Data

• Connecting External Data Sources

• Data Transformation

• Changing Data Types

• Removing and Replacing Values

• Split and Merge Columns

• Filtering Data

• Grouping Data

• Merge Queries

• Append Queries

• Refresh and Update Data

MODULE 5 – ADVANCED PIVOTTABLES

• Advanced PivotTable Design

• Grouping and Custom Grouping

• Calculated Fields

• Value Field Settings

• Show Values As

• Running Totals

• Percentage Analysis

• Advanced Filtering

• Slicers

• Timelines

• PivotTable Refresh

MODULE 6 – ADVANCED PIVOTCHARTS

• Creating Advanced PivotCharts

• Chart Selection

• Combination Charts

• Dynamic Chart Techniques

• Interactive Charts

• Slicers with Charts

• Dashboard Chart Design

MODULE 7 – INTERACTIVE EXCEL DASHBOARDS

• Dashboard Planning

• Dashboard Layout Design

• KPI Cards

• Interactive Charts

• Dynamic Reports

• Interactive Filters

• Slicers and Timelines

• Dashboard Navigation

• Professional Dashboard Formatting

MODULE 8 – DATA ANALYSIS AND WHAT-IF ANALYSIS

• Goal Seek

• Scenario Manager

• Data Tables

• Forecast Sheet

• Trend Analysis

• Break-Even Analysis

• Business Decision Analysis

MODULE 9 – POWER PIVOT AND DATA MODELLING

• Introduction to Power Pivot

• Data Models

• Importing Multiple Tables

• Creating Relationships

• Data Model Management

• Basic DAX Concepts

• Calculated Columns

• Measures

• Business Data Analysis

MODULE 10 – EXCEL AUTOMATION

• Introduction to Excel Automation

• Macro Concepts

• Recording Macros

• Running Macros

• Editing Basic Macro Code

• Relative and Absolute References

• Automating Repetitive Tasks

• Practical Automation Exercises

MODULE 11 – ADVANCED MIS REPORTING

• Management Information Systems

• Sales MIS

• Purchase MIS

• Inventory MIS

• HR MIS

• Payroll MIS

• Financial MIS

• Performance Reports

• Management Summary Reports

MODULE 12 – EXPERT PROJECTS

• Advanced Sales Dashboard

• Business Performance Dashboard

• Inventory Analysis System

• Employee Payroll Dashboard

• Financial Analysis Dashboard

• Advanced MIS Reporting Project

• Power Query Data Transformation Project

FINAL ASSESSMENT

• Advanced Excel Practical Examination

• Objective Examination

• Advanced Formula Assessment

• Power Query Task

• Dashboard Development Task

• Data Analysis Project

• Expert Excel Project