Model Online / On-Site
Duration
Modules 8 modules

About the Course

Microsoft Excel Advanced – Expert Level enables experienced Excel users to manage complex data analysis, dynamic reporting, and automation workflows at an expert level. The course covers array formulas, What-If analysis tools (Goal Seek, Solver, Scenarios), form controls, and Power Query data transformations.

Target Audience

  • Professionals with advanced Excel proficiency

  • Power users performing intensive data analysis and modeling

  • Finance, accounting, sales, operations, and management teams

  • Anyone aiming to master Excel at an expert level

Learning Outcomes

  • Master complex Excel functions and array formulas

  • Conduct advanced analytics and optimization on large datasets

  • Build dynamic, automated, and interactive reports using Form Controls

  • Automate external data ingestion and transformation with Power Query

  • Use Excel as an expert-level decision-support and modeling tool

Curriculum

Module 1 – Working with Excel Tables

  • Creating Dynamic Tables

  • Using Dynamic Table Features

  • Using the Total Row Feature

  • Creating Defined Names

  • Using the Name Manager
Module 2 – Tables and Data Management

  • Using the Data Ribbon

  • Sorting Tables and Custom Sorting (Sort / Custom Sort)

  • Applying Auto Filter

  • Filtering Tables with Advanced Filter

  • Data Consolidation (Data Consolidate)

  • Importing External Data (Import E.Data)

  • Subtotals (Subtotal)

  • Removing Duplicates (Remove Duplicates)

  • Converting Text to Columns (Text to Columns)

  • Flash Fill

  • Using Data Validation
Module 3 – Advanced Functions & Array Formulas

  • Lookup and Reference Functions

  • VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH

  • Logical Functions

  • IF, Nested IF, AND, OR, IFERROR

  • Text Functions

  • UPPER, LOWER, LEFT, RIGHT, CONCATENATE, MID, LEN, TEXT, SEARCH

  • Conditional Functions

  • SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, SUMPRODUCT

  • Database Functions

  • DSUM, DAVERAGE, DMAX, DMIN, DCOUNT

  • Information Functions (ISERROR, ISNA, ISBLANK, ISNUMBER)

  • Array Formulas
Module 4 – Advanced PivotTables and PivotCharts

  • Creating a PivotTable

  • Modifying a PivotTable

  • Refreshing Tables

  • Updating the Data Source Range

  • Using Report Filter Pages

  • Relationships Between Tables

  • Report Connections

  • Using Slicers and Timelines

  • Creating a PivotChart

  • PivotChart Options
Module 5 – Data Management and What-If Analysis

  • Analyzing with Goal Seek

  • Optimization Modeling with Solver

  • Using 3D Maps

  • Building Forward-Looking Projections with Scenario Manager

  • What-If Analysis
Module 6 – Form Controls

  • Using Buttons

  • Using Combo Boxes

  • Using Check Boxes

  • Using Spin Buttons

  • Using List Boxes

  • Using Option Buttons

  • Using Labels

  • Using Scroll Bars

  • Building Dynamic Interactive Tables with Form Controls
Module 7 – Working with External Data

  • Creating and Managing Queries

  • Creating an Excel Query

  • Creating a Web Query

  • Refreshing Queries
Module 8 – Using Power Query

  • Power Query Interface

  • Working with Data Sources

  • Importing Data with Power Query

  • Transforming Data Using the Power Query Editor

  • Creating Conditional Columns

  • Grouping Rows

  • Merging Queries

  • Appending Queries

  • Importing and Refreshing Data from Folders

Other Courses in This Category

  • Microsoft Word, PowerPoint and Project for Business Professionals

    Microsoft Word, PowerPoint and Project for knowledge workers and project teams: long-form corporate document templates, Slide Master and data-driven executive decks, WBS, critical path (CPM) and resource scheduling.

    MS Office Corporate
    Live Course
    5 modules
    View
  • Microsoft Excel Fundamentals Basic Excel

    Foundational Microsoft Excel training covering interface navigation, cell and table formatting, worksheet management, conditional formatting, charts, and core formulas.

    MS Office Corporate, Individual
    Live Course
    9 modules
    View
  • Microsoft Excel Intermediate Intermediate Excel

    Intermediate Microsoft Excel training covering table management, advanced sorting and filtering, conditional formatting, VLOOKUP/HLOOKUP, and conditional functions (SUMIF, AVERAGEIF, COUNTIF).

    MS Office Corporate
    Live Course
    10 modules
    View
  • Microsoft Excel Advanced Advanced Excel

    Advanced Microsoft Excel training covering XLOOKUP, INDEX/MATCH, SUMIFS, database functions, data validation, PivotTables and PivotCharts, workbook security, and recorded macros.

    MS Office Corporate
    Live Course
    8 modules
    View
  • Microsoft Project Fundamentals Basic MS Project

    Foundational Microsoft Project training covering project scheduling, Work Breakdown Structure (WBS), task dependencies (FS, SS, FF), resource assignment, and Gantt Chart tracking.

    MS Office Corporate
    Live Course
    8 modules
    View
  • Microsoft Project Advanced Advanced MS Project

    Advanced Microsoft Project training covering Critical Path analysis, resource leveling, cost and budget variance tracking, and Earned Value (EV, PV, AC) performance analysis.

    MS Office Corporate
    Live Course
    8 modules
    View

Get in touch