EXCEL

seminar objective

The refresh seminar is aimed at participants who want to get back into Excel or get a compact overview of how to use practice-relevant Excel functions. With the help of solution-oriented tasks, you will learn how to use these functions and can discuss the functions that are important for you in depth.

Content

• Tips and tricks, practical editing functions

• Create data series

• Creating formulas and range names

• Sort and filter

• Smart tables and conditional formatting

• Consolidation with references and 3D references

• Consolidate by positions and categories

• Practice relevant text, date, logical and statistical functions

• Functions If and VLookup

• SumIf(s) and CountIf(s) functions

• Joining tables with Vlookup, ref, column and row

• Matrix Functions, Trend and Frequency

• Goal Seek

• Evaluations with pivot tables

• Creation of recording macros

seminar objective

In this basic seminar you will learn all the important techniques for working with Excel. You will learn how to visualize spreadsheets efficiently and attractively in tables and with the help of basic diagrams. Relative and absolute formulas offer the possibility to perform extensive calculations in Excel.

Content

• Enter and edit data

• Create months and days of the week automatically

• Design spreadsheets with lines and colors

• Use table styles

• Use number, currency and date formats

• Print extensive tables

• Calculations with formulas and functions

• Relative and absolute cell references

• If and reference functions

• Create charts

• Tips and tricks when using Excel

seminar objective

As part of the seminar, you will learn practical functions for using Excel in your company. Learn about the tools for data analysis and preview different situations (what-if) or evaluate large amounts of data with pivot tables. Finally, you will receive a practical introduction to automation with macros and VBA.

Content

• Advanced Features

• Create formulas with range names

• Create array formulas

• Advanced features(Text, Statistics, Lookup, Consolidation Functions...)

• Data analysis with conditional formatting, multiple operations,Scenarios and Goal Seek)

• Create pivot tables and charts

• Advanced Charting Techniques

• Introduction to automation with recording macrosand VBA programming

seminar objective

As part of the seminar, you will receive a well-founded introduction to working with date and text functions, logic, lookup, reference, statistical and selected mathematical functions. The examples are developed and discussed on the basis of possible applications.

Content

• Working with relative and absolute formulas

• Calculations with array formulas

• Nested Functions

• Formulas with range names

• Goal Seek

• Date, time and text functions

• Logic functions and conditions in functions

• Find values with references(Sreference, reference, index, comparison, indirect, address,column, row)

• Statistical functions(Mean values, frequencies, measures of dispersion, correlation andregression analysis, trend calculation)

seminar objective

In this seminar, you will learn how to visualize your data using Excel charts. In addition to the classic charts such as column or pie charts, you will receive information on bubble and waterfall charts, among other things. Learn how to customize charts to your liking.

Content

• Insert chart and select range of values

• Color design options

• A custom chart background

• Format Chart Axes

• Add labels and other chart elements

• Combine chart types

• Show trend lines, averages and error bars

• Special chart types, e.g. B. Histogram, scatter, bubble and waterfall chart

• Tips and Tricks

seminar objective

As part of the PowerBI (Business Intelligence) tools integrated in Excel, you will learn not only brief information about PowerQuery, but also the possibilities of being able to evaluate mass data from any source (Access, Excel, text files, SQL database ...) conveniently with PowerPivot. PowerQuery uses a data model integrated in Excel.

Content

• PowerQuery basics

• PowerPivot Basics

• Import data sources into PowerPivot

• Create relationships between tables

• Views in the data model

• Calculations in PowerPivot

• Calculated columns and fields (measures)

• Useful DAX functions

• Create hierarchies

• Create and include date table

• Data evaluation with KPI (Key Performance Indicator)

• Perspectives and comments

seminar objective

In this seminar you will receive a well-founded introduction to the automation of Excel with Visual Basic for Applications (VBA) in order to be able to create complex Excel applications independently. Programming knowledge is not required.

Content

• Basics of programming with Visual Basic for Application

• Data import with VBA from Excel workbooks and text files

• List folder contents and select files

• Access workbooks, spreadsheets and ranges of cells

• Collect and combine ranges of cells from multiple workbooks

• Use user-defined forms (UserForms)

• Automated creation of folders and workbooks

• Save and backup files

• Write data to text files

• Tips for increasing performance and troubleshooting

seminar objective

The aim of the VBA workshop is to convey additional functions or to discuss questions that arise when dealing with VBA on a daily basis.

Content

• Discussion of open questions in dealing with VBA

• Development of possible solutions to existing tasks

• Development of user forms (Userforms)

• Import and export external data from Access and text files

• Data access to the SQL server with ADO access objects

• Use user-defined forms (UserForms)

• Write and read data into the registry

• Pass on and integrate function libraries in AddIns

• Creating formula bars

• …