Duration 4 days – 28 hrs
Overview
The Microsoft Excel Master Class is a comprehensive 3-day hands-on training designed to transform participants into proficient Excel users, capable of handling complex data analysis, reporting, and automation tasks. Covering both intermediate and advanced topics—including formulas, lookups, pivot tables, dashboards, and basic macros—this course equips learners with the tools and techniques needed to solve real-world business problems and boost productivity using Excel.
Objectives
- Utilize Excel’s advanced features for managing and analyzing large datasets.
- Implement time-saving functions, shortcuts, and automation through macros.
- Build complex formulas and logical conditions to extract actionable insights.
- Create dynamic dashboards using PivotTables, charts, and data validation tools.
- Leverage Power Query and Power Pivot to connect and process data from multiple sources.
- Apply conditional formatting and data validation rules for data accuracy and clarity.
- Automate repetitive tasks with basic Macros and understand macro security practices.
Audience
- Administrative staff and data analysts
- Finance, accounting, and operations professionals
- Team leaders and business managers
- Professionals looking to enhance Excel reporting and automation skills
- Anyone aiming to become an advanced Excel user
Pre- requisites
- Basic to intermediate knowledge of Microsoft Excel
- Familiarity with standard Excel tasks like formulas, formatting, and navigation
Course Content
Time-Saving Tools & Data Management 1
- Useful Excel shortcuts
- Flash Fill automation
- Selecting cells based on content
- Replicating/clearing formats
- Custom number formatting
- Advanced Filter
- Rearranging cell values
Data Management 2 & References
- Grouping rows/columns
- Auto outline and Nested Subtotal
- Print setup techniques
- Data entry restrictions
- Creating dropdown lists
- Workbook/worksheet protection
- Constant, relative, absolute references
- Naming cell ranges
- Concatenation (joining values, text, formulas)
Statistical & Table Functions
- AVERAGE, SUMPRODUCT, MAX, MIN, LARGE, SMALL
- SUBTOTAL and AGGREGATE functions
- Conditional SUM/COUNT (single & multiple conditions)
- Creating and using Excel Tables
- Structured References and Slicers
Lookup Functions
- VLOOKUP and HLOOKUP
- INDEX-MATCH dynamic lookups
- Two-way Lookup
- Lookups with multiple criteria
- XLOOKUP
Logical Functions
- Logical expressions (TRUE/FALSE)
- IS functions
- IF, Nested IF, and IFS
- Handling multiple conditions in IF statements
Text & Date Functions
- Formatting combinations (date, text, numbers)
- Extracting/subdividing text
- Character counting and testing cell content
- TODAY, NOW, DAY, MONTH, YEAR
- Dynamic and relative dates
- EOMONTH function
Reference Functions, Formatting & Charts
- CHOOSE and INDIRECT functions
- Conditional Formatting based on content, scale, rank
- Highlighting duplicates/uniques
- Creating and customizing charts
PivotTables and PivotCharts
- Inserting and configuring PivotTables
- Compound labels and dynamic data sources
- Adding PivotCharts and Slicers
Power Query, Power Pivot & Auditing
- Importing and consolidating data using Power Query
- Combining tables using Power Pivot
- Auditing formulas: dependent/precedent cells, circular references, error evaluation
Formula-Based Validation & Conditional Formatting
- Data validation based on formulas
- Cell formatting rules driven by values in other cells
- Dynamic dropdowns using INDIRECT
- Assigning formula names
Dashboards
- Preparing data for dashboard use
- Inserting visuals and charts
- Creating navigation/action buttons
- Dashboard layout and design best practices
Mail Merge & Basic Macros
- Mail Merge for generating multiple documents
- Introduction to Macros and Excel automation
- Enabling Developer Tab
- Recording Macros (Absolute vs Relative)
- Assigning and editing Macros
- Understanding Macro security
