Professional Training

Prerequisites

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

Learning Outcomes

Having completed the training, participants will be able to:

Create Dashboards using Camera Tool
Understand and use various new Functions in Excel, e.g. Lambda
Write their own Functions
Create Tables, PivotTables, Pivot Charts & Slicers using Automatic file imports
Build relationships between Excel Tables
Use PowerPivot to analyse large data sets
Understand Data analysis and Business intelligence features in Excel
Use advanced Data Analysis functions in DAX
Begin using Python in Excel

Create Dashboards

Named Ranges, Copy and Paste options, Camera Tool
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/TextSplit, Concat, vLookup / xlookup examples
Lambda functions, VStack/HStack
Create a user defined function in Excel

Analysing Data using PivotTables

Automating data import from csv files
Using Pivot Charts and pivot tables from data tables
Using Slicers to filter the PivotTable data
Refreshing the data sources

Data Analysis and BI

Using Power Query M programming to customise data importing
Introduction to Power Pivot
Creating relationships between tables
Using the Power Pivot for Business Analysis and reporting
Business Intelligence, DAX for Business Analysis and reporting

Using Python in Excel

Utilise the power of Python in Excel
Use Python Data analytics capabilities