Microsoft Office Excel Advanced

Course Description

This Microsoft Excel advanced course will extend your knowledge into some of the more specialized and advanced capabilities of Excel by using the data analysis and business intelligence features. You will learn how to use the advanced features such as What-If analysis and Scenario planning, PivotTables and Power Pivots for analysis of large data sets. Using Get and Transform functions are always popular sections on the course, as well as a first look at creating Macros using the powerful point and click features available in Microsoft Excel.
1 day


Experienced MS Excel users who have completed an intermediate course or have a similar level of knowledge.

Learning Outcomes

Having completed the training, the attendees will be able to:

Use the new features in MS Excel
Use a PivotTable, PivotCharts & PowerPivot to analyse large data sets
Understand Data analysis and Business intelligence features in Excel
Create what if analysis and scenarios to create Best case, Worst Case of Most Likely projections
Use advanced functions
Create macros

What’s new in Excel

New templates, charts, Quick Analysis & Dynamic Arrays

Analysing Data using PivotTables

Changing the layout and format of the PivotTable
Calculations and analysis in Pivot Tables
Using Slicers to filter the PivotTable data
Connecting Slicers to multiple Pivot Tables
Creating a MS Excel Dashboard using Slicers
Using PivotChart drill down buttons

Data Analysis and BI

Introduction to Power Pivot
Using the Power Pivot for Business Analysis and reporting
Using Get and Transform
Power Query and data models
Power Maps

What If Analysis

The Analysis ToolPak
Creating scenarios to investigate best case and worst case
Using Goal seek to prepare forecasts or get the desired results
Data Input Tables to calculate multiple results

New functions in Excel

Ifs, Switch, MaxIfs, MinIfs, Countifs, SumIfs, Iferror
Dynamic Arrays - Sequence, Sort, Filter, Sortby, Unique and Randarray

Smart Lookup

Advanced Excel Tasks

Creating Click and point Macros

Advanced ExcelExcel 2016Microsoft ExcelMicrosoft Excel 2016MS ExcelPivot TablesExcel 365Excel 2019Dynamic ArraysOffice 365Excel 2021LAMBDA