Excel – Advanced

Who is this course for? This course is for people who would like to import large or complex data into Excel, clean and transform it and analyse the data.

Learning Objectives: Attendees will be able to use two major components of modern Excel, Power Query and Power Pivot, to import, clean and analyse data in much more productive ways than was previously possible.

Course Length and Price: 2 days, £2,900 ex VAT

Pre-requisites: The course assumes that attendees have a good foundation in Excel: for example: navigation, formulas, Excel tables and pivot tables.

Course Content:
Import, clean and shape data with Power Query in Excel
Import from different types of data sources including relational databases such as SQL Server, APIs (which are becoming more common), web pages, other Excel files and CSV files.
Clean data. Filter rows and duplicates, detect and handle missing and bad values, get an overview of data quality with column profiling, split and format columns.
Transform data so that it is in the correct shape for analysis on Excel sheets. Pivot, unpivot and transpose a dataset, merge or append several datasets.

Use the Excel Data Model (Power Pivot) to analyse data
The Excel data model is based on tabular data, so we’ll start with a recap of fundamentals of tabular data: tables, rows, columns, column data types, uniqueness, cardinality, null values, primary and foreign keys and relationships, types of relationships. , dimensions and fact table and the star schema pattern. In a lab exercise we will

  • create a data model by relating several tables
  • improve the data model; hide fields, add hierarchies, set formats.
  • write calculations on the data model using the DAX language: for example, basic calculations: e.g. sums, counts; ratios e.g. percentage of total; and time intelligence calculations e.g. year-to-date

Use and Create Excel Linked Data
We will take advantage of the inbuilt linked data types to import geographic and financial data into Excel. Then we create a linked dataset in Power Query and use it in formulas in Excel. (Optionally) we will create organisational data types in Power BI and use in Excel

Below are some images from the lab exercises on the course.

Football League table in Excel Power Query Query Editor
Excel’s Power Query Query shapes football match data into a summary results (league table).
Excel PowerPivot diagram view
Excel’s PowerPivot arranges several tables to allow sophisticated analysis and avoid VLOOKUPS.