Microsoft Excel 365 Advanced - Key Features & Business Intelligence

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