MS Excel 365: Data transformation and analysis with PowerQuery and PivotTables

 In this course you will learn to import and transform data from various sources into Excel so that it can be used for further analysis and reporting.

In the course we will learn the process of data import, data cleansing, data preparation with the PowerQuery editor and analytical compilation of data with Excel PivotTables.

In the interaction of PowerQuery and Pivot, data is collected, sorted, cleaned and analytically organised in an automatable and reproducible way.
 

General information

Duration 6 hours
  • Importing data with PowerQuery Editor (CSV, Excel files, query entire folders, websites) 
  • Clean data with PowerQuery Editor (remove duplicates, empty cells, filter, sort)  
  • Transforming data with PowerQuery Editor (data types, formatting, conversions) 
  • Combine data queries with PowerQuery Editor (append and merge) 
  • Creating and formatting PivotTables 
  • Data analysis with PivotTables (summarising values, displaying values as, grouping, filtering and data slicing)
 
Participants will have a basic understanding of spreadsheets using Microsoft Excel (entering data, creating tables, formatting cells, simple formulas and functions) and a basic understanding of data analysis (sorting, filtering, aggrandising). You need an Office365 account at the University of Zurich to participate.
 
This course is aimed at all members of the University of Zurich who would like to specifically deepen their Excel knowledge.
Participants will be able to 
  • import data from various data sources and formats into Excel. 
  • apply various techniques for data cleansing and transformation. 
  • repeat and automate queries. 
  • Relate data with PivotTables for analysis.
The learning content is delivered interactively in a mixture of presentation, demonstration and practical exercise.
The PowerQuery editor is a component of Excel and PowerBI. PowerBI is optimised for the creation of complex data models and their visualisation. In this Excel course you will learn the methods that you can also use in PowerBI.

Dates

Code Instructor Dates Available seats Venue
HS25-WEP-02 Brennwald Milena Tue 20 January 2026 (02:00pm - 05:00pm)
Thu 22 January 2026 (02:00pm - 05:00pm)
11 Universität Zürich Zentrum Register
FS26-WEP-01 Stolz Barbara Fri 06 March 2026 (09:00am - 12:00pm)
Fri 13 March 2026 (09:00am - 12:00pm)
Online Course Course registration begins on 1 February for the spring semester and on 1 September for the autumn semester.