Microsoft Office Excel Advanced

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