Microsoft Excel 365 Advanced - Key Features & Business Intelligence

This MS Excel Advanced course explores some specialized capabilities of Excel using the data analysis and business intelligence features. Participants will use key functions and features including PivotTables and Power Pivots for analysis of large data sets as well as creating user defined functions, automating data imports, building table relationship and using Power Pivot advanced Data Analysis and expanding Excel's capability using Python.

Duration
1 day
Format
Instructor-led, in person or live online

What the course covers

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

Some key functions in Excel

  • Functions in Excel - Ifs, Switch, MaxIfs, MinIfs, Countifs, SumIfs, Iferror
  • Excel functions – TextJoin/TextSplit, Concat, vLookup / xlookup examples
  • Lambda functions, VStack/HStack
  • Writing User Defined Functions using VBA

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