Inhouse Seminar: Data Analysis with Excel

For several years, Excel has included the powerful tools “PowerPivot” and “PowerQuery”, which can make controllers’ everyday work much easier. In today’s controller practice, data analysis in Excel is often still based on formulas like SUMIF or VLOOKUP. However, this traditional method is quite time-consuming and error-prone, especially with large amounts of data and when updating data. Even normal pivot tables often reach their limits. In contrast, the new tools open up modern possibilities for processing and analyzing large amounts of data. Data can be quickly and reliably loaded from various sources, transformed and linked in a structured data model. The data model then allows multidimensional evaluations with pivot tables, pivot diagrams and cube formulas.

Your benefits

  • In the Data Analysis with Excel training you will learn about the strengths and weaknesses of different data processing methods such as Excel formulas, normal pivot tables as well as PowerPivot and PowerQuery.
  • You can use the functionality of PowerPivot and PowerQuery for your own use cases, for example, to combine data from different sources in a data model and evaluate it in pivot tables and pivot diagrams.
  • In addition, after the Data Analysis with Excel course, you can dynamically update the data source of charts and tables with pivot tables and cube formulas, and use interactive controls for different data dimensions and timelines for convenient data analysis.

Seminar content

  • Understand dynamic data tables and pivot tables and apply them selectively
  • Get to know PowerPivot and PowerQuery functionalities in Excel
  • Integrate external data from databases, Excel or text files
  • Building data models with relationships and hierarchies
  • Create pivot tables and pivot diagrams based on the data model
  • Calculate fields (measures) with DAX formulas in PowerPivot, e.g. for key figures
  • Correctly construct date tables and use them as time dimensions for data analyses
  • Simplify ad hoc analysis with data slices and timelines
  • Control PowerQuery loading processes dynamically with parameters
  • Use cube formulas to automate charts and dashboards

The seminar examples work in Microsoft Excel from version 2010 SP1 in connection with the add-ins “PowerPivot” and “PowerQuery”. Please use a notebook with your working version and installed add-ins as well as a mouse.

This seminar can be booked together with Dashboards with Excel in the same week to save on travel expenses and to familiarize yourself intensively with the topic.

Target group

The seminar Data Analysis with Excel is aimed at all controllers and other specialists and managers who want to prepare and analyze data professionally in Excel.