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.
This course will work with Microsoft Excel for Windows. Participation with the Excel version for Mac is possible in principle, but is made more difficult by the fact that not all functions are available and the menu display differs in places. 

The PowerQuery editor is also a component of 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.


Code Referents Dates Available seats Place
HS24-WEP1 Stolz Barbara 04.10.2024 - 11.10.2024 (09:00 - 12:00 o'clock)
Universität Zürich Irchel Course registration begins on 1 February for the spring semester and on 1 September for the autumn semester.
HS24-WEP2 Stolz Barbara 13.01.2025 - 20.01.2025 (09:00 - 12:00 o'clock)
Online Course Course registration begins on 1 February for the spring semester and on 1 September for the autumn semester.