Excel for controllers

Brief introduction to the core features in Excel

• The importance of the cell approach versus the table approach in Excel for controllers.
• The "Format as table" feature and its advantages - also when using functions.
• Calculating with speaking names.

The genius of the pivot table when analyzing data

• Become a pivot table pro in 3 steps.
• The most important basic settings of the pivot table.
• The different layouts and their practical use in controlling.
• Typical calculations in day-to-day controlling, such as complex comparisons with the previous year.
• Grouping numbers, dates and texts.
• The hidden sorting and filter options in the pivot table.
• Create pivot charts.
• Practical example: ABC analysis.

 Excel applications in controlling

• Plan/actual comparisons.
• Tips and tricks for graphical optimization of data analysis and reporting.
• Practical examples and exercises.

The calculated column and its meaning when evaluating data

• The mathematical problem of rounding when calculating.
• The appropriate combination of functions and the pivot table using various practical examples such as evaluating a machine park or commission payments.
• The important if() function as a decision aid using various practical examples.
• The classic Sreference() and Wreference() functions and nested if() functions.
• The exact match and the interval search of the SVerweis() or XVerweis().
• Nested functions such as If() and Year() and Month() using the practical example of a different financial year.

The best tips and tricks

• Grouping instead of hiding.
• Comprehensive understanding of Excel protection, including unlocking protection without a password.
• Error-proneness, clarity and performance aspects of various Excel solutions in controlling. 
• Outlook on Power Query for importing and customizing external data.

Please bring your own laptop to the training . You can find more detailed installation instructions under Methods.

Brief introduction to the core features in Excel

• The importance of the cell approach versus the table approach in Excel for controllers.
• The "Format as table" feature and its advantages - also when using functions.
• Calculating with speaking names.

The genius of the pivot table when analyzing data

• Become a pivot table pro in 3 steps.
• The most important basic settings of the pivot table.
• The different layouts and their practical use in controlling.
• Typical calculations in day-to-day controlling, such as complex comparisons with the previous year.
• Grouping numbers, dates and texts.
• The hidden sorting and filter options in the pivot table.
• Create pivot charts.
• Practical example: ABC analysis.

 Excel applications in controlling

• Plan/actual comparisons.
• Tips and tricks for graphical optimization of data analysis and reporting.
• Practical examples and exercises.

The calculated column and its meaning when evaluating data

• The mathematical problem of rounding when calculating.
• The appropriate combination of functions and the pivot table using various practical examples such as evaluating a machine park or commission payments.
• The important if() function as a decision aid using various practical examples.
• The classic Sreference() and Wreference() functions and nested if() functions.
• The exact match and the interval search of the SVerweis() or XVerweis().
• Nested functions such as If() and Year() and Month() using the practical example of a different financial year.

The best tips and tricks

• Grouping instead of hiding.
• Comprehensive understanding of Excel protection, including unlocking protection without a password.
• Error-proneness, clarity and performance aspects of various Excel solutions in controlling. 
• Outlook on Power Query for importing and customizing external data.

Please bring your own laptop to the training . You can find more detailed installation instructions under Methods.