- Home
- Participant Homepage
- Course Offers
- Current Course Programme
- Operating Systems & Programming
- Digitale Selbstverteidigung: Einführung in die IT-Sicherheit für Anwender:innen
- Linux: Bash Workshop (TheAlternative.ch)
- Linux: Introduction to Open Source Software (TheAlternative.ch)
- Python: Machine Learning for Beginners
- Git: Continuous Integration und Deployment in GitLab@UZH
- Python: Basics
- Python: Automatisierung, Web-Scraping, Bildbearbeitung
- Python: Intermediate
- Microsoft Power Automate: Digitalisierung erster Prozesse
- Science IT: Linux Command Line
- Image Editing, Illustration and Presentation
- Collaboration, Social Media and Webpublishing
- CMS: Create accessible websites
- Social Media and Science Communication
- Images for your Website
- CMS Introduction Magnolia
- Creating and Publishing Web Pages
- Basic Introduction to JavaScript
- UZH365: Create an Intranet with SharePoint
- UZH365: Basics of collaboration in the cloud
- UZH365: Effective communication with teams Telephony
- UZH365: Microsoft Outlook (im Web) Grundlagen
- Microsoft Planner: Task management with Kanban
- UZH365: Outlook Desktop Productivity Training
- UZH365: SharePoint Basics
- TOPdesk: Hands-on Essentials
- Data Science
- QGIS: Spatial data analysis and map creation
- Python: Introduction to Natural Language Processing (NLP)
- Python: For the Digital Humanities
- Einführungskurs in das Statistikpaket SPSS
- Introduction to Programming with MATLAB
- Qualitative Datenanalyse mit MAXQDA
- Python: Data Analysis Essentials
- PowerBI Data Analysis and Visualisation
- R: Basic Introduction
- R: Crash Course in Statistics using R
- R: Reporting using Quarto & R Markdown
- R: tidyverse for Data Science
- Databases, Spreadsheet
- E-Learning & Examination
- Scientific Computing
- Text Processing & Publishing
- Knowledge Management
- UZH365: Digital Communication and Collaboration
- IT Courses from other Organisational Units of the UZH
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)
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.
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.
Dates