EXPO Katowice S.A.

Excel for advanced users with Visual Basic elements

23.10.2023

Excel for advanced users with Visual Basic elements

The proposed workshop is three days of very intensive classes, providing advanced knowledge in the use of Microsoft Excel.

The workshop, which has been organized for many years, enjoys unflagging popularity among participants and is highly rated both in terms of its substantive level and accessible teaching techniques. The workshop program is constantly modified and adapted to the changing market needs.

When working with a Microsoft Excel spreadsheet every day, we do not always use its enormous analytical capabilities, processing large amounts of data, supporting decision-making or automating routine activities.

If you have questions like:
• How to analyze information from the company’s IT system in Excel?
• How to automate many repetitive activities in the sheet?
• How can pivot tables and charts help?
• How to merge and consolidate data from different sheets?
• When is it worth using Excel add-ins, including Solver?
• You will find the answer by participating in dedicated workshops Excel for advanced users

Target group

Excel for advanced with Visual Basic elements is intended for people who intensively use spreadsheets and have a good knowledge of the basics of using Excel.

Training methods

The course is conducted using modern, interactive methods, using a multimedia projector, with the active involvement of participants. All exercises are conducted based on examples taken from practice. Despite presenting complex substantive knowledge, the classes are conducted in an understandable way, ensuring that each course participant absorbs the knowledge provided (regardless of previous experience with Microsoft Excel).

Each participant receives a set of materials and electronic copies with all exercises conducted as part of the course and sample Microsoft Excel sheets.
Participants actively participate in the classes throughout the course, performing a number of practical exercises, and can apply the acquired skills in their professional work immediately after completing the training.

Training program

Day 1,2 and 3 - 9:00 - 15:30 - details

The workshop program includes, among others, the following topics:

Excel in the past and today – a short history of the program’s development, versions, differences and common features, Comparison of Excel versions 2003, 2007, 2010, 2013, 2016, 2019
Excel file types, file conversions
Optimal “personalization” of Excel for work
A look at Excel functions, categories, composition of functions (e.g. INDEX<->MATCH, OFFSET<->NON-BLANK))
External data, data types, data sources, building queries, saving and editing queries, analyzing external data
Data presentations, charts, composite charts, sparklines
Technologies for downloading data from external sources (Excel data sources, database queries, using the MS Query module, downloading data in XML format)
Import and analysis of data from text files to Excel.
Filtering, sorting and subtotal tools in data analysis. Elements of conditional formatting. Data validation (data validation)
Creating and modifying pivot tables based on different data sources, data grouping, custom data formatting in pivot tables
Advanced data analysis in a pivot table, ways of presenting and analyzing data, calculated fields, using the GETDATATABLE function, the role of a slicer in a pivot table, pivot charts
Conditional formatting in a pivot table
Use of local and global valid names in one workbook
Rules and principles for dealing with conflicts between names and other elements of Excel documents
Creating and applying names to ranges on several sheets
Dynamic names, cross names and their use in calculation sheets and charts
Conditional formatting, application variants
Sheet protection, workbook protection, data validation
Formulas, nested formulas, array formulas, numeric constants in arrays
Matrix functions, application examples
Use of conditional sums according to various criteria
Data tables, data analysis in table
Database functions, examples of practical applications, selection of criteria, alternative and conjunction of criteria
Formula inspection tools, formula evaluation, error checking, watch window
Automation of calculations using functions built by the user, using them in other sheets, creating add-ons.
Optimize solutions to complex problems using Solver
Speed up your work thanks to templates
Creating electronic forms in Excel, using control elements in the forms (lists, drop-down lists, buttons, knobs)
Using the Visual Basic module – Tools from the toolbar
Main elements of the VBA editor window
Basic security rules – security level, messages, warnings, macro viruses
Rules for creating macros during recording
Macro location and availability
Macros with relative references (performing actions from the active cell) and absolute references ((performing actions in the same cells)
Executing, interrupting and tracking program execution
Various launch methods (buttons, toolbar, commands in ribbon tabs)
Dividing long macros into procedures, scheduling recording for optimal macro playback, testing macros
Using the Visual Basic module to create functions used in advanced calculations, creating Excel add-ins
Using the Microsoft Visual Basic module to design advanced objects and build your own applications in Excel
Data consolidations, consolidation options, editing sheet links
Organization of work with Excel in a computer network: simultaneous work of many users on one sheet, combining sheets and workbooks via a computer network, merging data from many sheets
Building multi-variant models using scenarios
Graphical presentation of data using charts (complex charts, charts presenting multiple data series, trend analysis on a chart, custom charts, charts based on data from pivot tables, timeline charts), “chart dynamics in Excel”

Benefits of training

After completing the training, participants will be able to apply the acquired knowledge to creative analysis of the company’s activities and to build complex models thanks to which decision-making will be based on reliable and accurate data. Learning advanced techniques for using Microsoft Excel will make your everyday work easier and more effective. Additionally, a dedicated workshop allows you to adjust the duration of the training and change the program individually, according to the client’s needs.

Certificate

After the training, the participant will receive a certificate confirming participation in the training and acquired knowledge.

Coach

Janusz Gach

He has been providing training for years, explaining the intricacies of spreadsheets and accessibly showing the possibilities behind them. He specializes in Microsoft Excel, as well as other elements of the MS Office suite. His training courses are aimed at employees in the financial sector, as well as other office workers, engineers, salespeople of various professions. The trainer’s training courses and classes are used by all who wish to expand their practical use of the spreadsheet: both at a basic and more advanced level.

Current trainings

Excel Pivot Tables