Advanced excel professional training programme

Module 1 – Introduction to Microsoft Excel

• Introduction to Microsoft Excel
• Excel versions
• Excel interface and navigation
• Workbook and worksheet concepts
• Rows, columns, cells and ranges
• Creating and saving workbooks
• Entering and editing data
• Excel terminology

Module 2 – Excel Fundamentals

• Creating and formatting tables
• Font and cell formatting
• Number formats
• Borders and alignment
• Fill series
• Copy, Cut and Paste
• Paste Special
• Transpose
• Insert, delete, hide and unhide rows/columns
• Freeze Panes
• Split Windows
• Relative and absolute references
• Named ranges

Module 3 – Basic Excel Functions

• SUM
• AVERAGE
• COUNT
• COUNTA
• MAX
• MIN
• MEDIAN
• MODE
• ROUND
• SUBTOTAL
• Basic mathematical and statistical calculations

Module 4 – Data Validation & Conditional Formatting

• Understanding Data Validation
• Creating drop-down lists
• Date validation
• Number validation
• Input messages
• Error alerts
• Custom validation rules
• Conditional Formatting
• Highlight rules
• Duplicate values
• Data bars
• Colour scales
• Icon sets
• Formula-based conditional formatting

Module 5 – Advanced Excel Formulas

• IF
• Nested IF
• IFS
• AND / OR
• IF with AND/OR
• SUMIF
• COUNTIF
• AVERAGEIF
• SUMIFS
• COUNTIFS
• AVERAGEIFS
• IFERROR
• IFNA
• PMT
• Financial functions
• Formula auditing and debugging

Module 6 – Lookup Functions

• LOOKUP
• VLOOKUP
• HLOOKUP
• XLOOKUP
• INDEX
• MATCH
• INDEX + MATCH
• Multiple-condition lookups
• Approximate and exact matching
• Handling lookup errors

Module 7 – Working with Lists & Tables

• Convert range to Excel Table
• Table formatting
• Structured references
• Sorting data
• Multi-level sorting
• Filtering data
• Advanced filtering
• Removing duplicates
• Subtotals
• Grouping and ungrouping
• Working with large datasets

Module 8 – What-If Analysis & Advanced Excel Tools

• Goal Seek
• Scenario Manager
• Data Tables
• What-If Analysis
• Consolidating data
• Iterative calculations
• Watch Window
• Quick Analysis Tool
• Formula auditing
• Trace Precedents
• Trace Dependents
• Error checking

Module 9 – Advanced Data Visualization

• Principles of data visualization
• Choosing the right chart
• Column and Bar charts
• Line charts
• Pie charts
• Combo charts
• Custom chart templates
• Formatting charts
• Advanced chart elements
• Treemap
• Sunburst
• Histogram
• Waterfall
• Box & Whisker
• Pareto chart
• Sparklines

Module 10 – PivotTables & PivotCharts

• Introduction to PivotTables
• Creating PivotTables
• Rows, Columns, Values and Filters
• Grouping dates
• Grouping numbers/ranges
• Calculated fields
• Changing calculations
• Show Values As
• Percentage analysis
• Running totals
• Sorting PivotTables
• Filtering PivotTables
• Slicers
• Multi-select slicers
• Timelines
• PivotCharts
• External data sources

Module 11 – Advanced Excel Reporting

• Management reporting
• MIS reports
• KPI reporting
• Sales reports
• Employee reports
• Financial reports
• Variance analysis
• Month-on-month analysis
• Target vs Actual
• Top/Bottom analysis
• Dynamic reporting
• Interactive reports

Module 12 – Power Query

• Introduction to Power Query
• Importing Excel/CSV data
• Connecting multiple files
• Data types
• Removing errors
• Removing duplicates
• Filtering and sorting
• Split Column
• Merge Column
• Replace Values
• Conditional Column
• Custom Column
• Group By
• Merge Queries
• Append Queries
• Pivot Column
• Unpivot Column
• Data cleaning
• Refreshing queries

Module 13 – Power Pivot & Data Modeling

• Introduction to Power Pivot
• Importing multiple tables
• Creating relationships
• Primary and foreign keys
• One-to-many relationships
• Data Model
• Calculated Columns
• Measures
• RELATED
• SUMX
• Basic DAX
• Power Pivot reporting