Interactive and dynamic dashboards with pivot in Excel
Evaluate data quickly and audit-proof
.svg)
Machine Learning Engineer
The dynamics of the dashboard
- The cell approach versus the table approach in Excel.
- As a data basis, the "Format as table" feature and its advantages, such as the dynamics in reporting when data is changed.
- The basic principle of the pivot table in 3 steps.
- The most important practical properties of the pivot table.
The data slice for the interactive control of dashboards
- The differentiation between the filter options and the data slices, e.g. with several filter criteria.
- Positioning, appearance and options of the data sections, e.g. to make interactive comparisons with previous years.
The extended possibilities of data analysis
- The different report layouts of the pivot table and their practical use.
- The calculations or "Show values as" e.g. for the presentation of previous year comparisons in absolute and relative deviations.
- Running totals, e.g. over months for actual figures and comparison with budget values.
- Automatic grouping of date hierarchies, e.g. to carry out monthly evaluations over years.
- I/O calculation by skillful use of calculated elements and balancing between manual grouping of data versus automatic assignment of hierarchies by SVerweis().
- Complex reports, e.g. plan & actual comparisons over the years by cleverly combining different pivot tables and functions.
- Calculated column in the underlying table versus calculated fields in the pivot table for the extended display of KPIs.
The extended possibilities of graphical representation of data
- Tips and tricks for the professional presentation of the dashboard.
- Complex pivot charts in interaction with the data slices and time axes.
- Areas of application of the most important diagram types in practice.
- Use conditional formatting and its special features in the pivot table to highlight important key figures - KPIs.
- Simple ABC analysis of customers and products.
- Possible protection concepts and possible distribution scenarios of the dashboard to different target groups.
Please bring a laptop with Excel version 2010 or higher installed to the training .
In your online learning environment, you will find useful information, downloads and extra services for this training course once you have registered.
In this training , an specialist specialist will help you understand how to use pivot tables and pivot charts to create complex, dynamic and interactive dashboards quickly and easily and without complex functions.
You will learn ...
- How to use pivot tables and pivot charts to analyze your data within seconds and in an audit-proof manner.
- why the pivot table and pivot charts are the most powerful analysis tool in Excel.
- how you can present your controlling results with a dynamic, interactive and comprehensive dashboard.
- how you can automatically reuse a dashboard once it has been set up for future data through the interaction of pivot tables, pivot charts, data slices and PowerQuery.
Presentation, practical examples and independent exercises on your own PC, sample solutions, tips and tricks.
Due to the workshop nature of the seminar, questions, tasks and your own suggestions for solutions are very welcome and practical relevance is guaranteed.
employees and professionals working in controlling, finance or other areas who want to build dynamic and interactive dashboards for their decision makers .
A good basic knowledge of MS Excel is required for the training . The training is not suitable for newcomers .
.svg)