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 newer functions and features including 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
€275.00
 

Prerequisites

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
Create Dashboards using Camera Tool.
Understand and use new Functions in Excel
Create Tables, use PivotTable, PivotCharts & Slicers.
Use PowerPivot to analyse large data sets
Understand Data analysis and Business intelligence features in Excel
Use advanced Data Analysis functions in DAX
Automate some Tasks using Macros/VBA

What’s new in Excel

Named Ranges, Copy&paste options,Camera Tool, Notes,Comments
Creating a MS Excel Dashboard, KPI example
Using Quick Analysis, Summations, Data bars, Rules, Charts, Tables


New functions in Excel

New functions in Excel - Ifs, Switch, MaxIfs, MinIfs, Countifs, SumIfs, Iferror
Excel functions – TextJoin, Concat, vLookup / xlookup examples

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
Using Pivot Charts

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
Business Intelligence, DAX for Business Analysis and reporting

Visual Basic for Applications

Visual Basic Editor
Recording macros
Creating user functions using Visual Basic for Applications
Lambda Functions
Advanced ExcelMicrosoft ExcelMS ExcelPivot TablesExcel 365Dynamic ArraysOffice 365Excel 2021LAMBDA