Excel snacks in controlling

Module 1

Excel snack: Basic knowledge for pivot reports

Pivot tables are an important tool for the ad hoc analysis of large amounts of data in Excel. With just a few mouse clicks, data can be analyzed from different perspectives, provided the appropriate data structure is available. In this module, you will learn how to create an optimal raw data structure for a pivot report and which basic working techniques are available for data analysis. Based on this, you will explore advanced functions to analyze large amounts of data even more flexibly in the future and present your results.

• Prepare simple lists and data tables manually or with Power Query for pivot analyses.
• Get to know the basic working techniques of pivot tables (arranging, filtering, sorting, calculating data, etc.).
• Create additional grouping characteristics in pivot tables.
• Process data from pivot tables in Excel.
• Use data slicing ("Slicer") as an interactive filter tool.
• Create a dashboard for an instant overview!
• Use pivot charts to visualize data.

Excel snack: Creating dynamic reports

Almost all reports created with Excel are subject to regular updating. One aspect of using spreadsheets is to set up calculation models in such a way that the data update can be carried out as independently as possible by the program. In addition to the use of specific calculation functions and control elements, an important basis for this is the systematic structuring of the workbook. The interaction of these three components for setting up dynamic reporting is explained.

• Automate the data import with Power Query / macros.
• Get to know the requirements for the systematic structure of workbooks.
• Converting data is a basic prerequisite for successful evaluation.
• Create reports with dynamic range functions (FILTER, XREFERENCE; INDIVIDUAL; SORT).
• Protect data selection with control elements against input errors (data validation).
• Automatic export and dispatch of reports using a macro.

Module 2

Excel snack: Automate calculations without programming knowledge

For a long time, there was only one tool for automating calculations in Excel or creating user-defined calculation functions in Excel: VBA. In recent years, however, a number of tools have been implemented in the spreadsheet that can be used to solve such tasks in a different way. The advantage for all users: No programming knowledge is required to achieve the goal. The new tools are presented using various practical examples.

• Working with dynamic functions: XREFERENCE, FILTER, SORT, UNIQUE, SEQUENCE.
• Automate filtering and sorting processes with dynamic matrix functions.
• Create user-defined functions in controlling with the help of LET().
• Use the LAMBDA function for recursive functions.
• Use conditional formatting and formulas (traffic light logic, threshold values, status displays).
• Use ChatGPT in Excel to automate tasks.

Excel snack: Data visualization in the company

The concise presentation of key figures is an important step in presenting results and preparing management decisions. You will acquire important know-how to communicate results in tabular form and in diagrams. Starting with Excel lists and data tables, you will learn about standard charts and tables as a basis for individual adaptations. Design rules, the selection of the most suitable chart type and format templates enable you to minimize the effort involved in presenting results.

• Use important laws of perception for the concise design of tables and diagrams.
• Create and configure standard diagrams with just a few mouse clicks.
• Highlight important data in tables and charts with conditional formatting (data bars, traffic lights, etc.).
• Waterfall diagrams: Ideal for displaying changes, e.g. from the start value to the end value.
• Minimize the effort involved in designing diagrams with color designs and diagram templates.
• Use table and cell format templates for the consistent design of tables.
• Use sparklines - make trends "visible" at a glance.
• Create dynamic charts based on PivotTables and link them in a dashboard.