Course Outline
Part I. Squeeze more from Excel
Overview of tools on the Data tab
- Access to external data - do you really need to visit the bank's website every day to get to know the current exchange rate CHF?
- Defining connections to external data (Access, Web, Text, XML, ...)
- Sorting multi-level - the rules and the proper sorting options
- Efficient Advanced filtering - how to create Strainers having access to the filter criteria
- Fast text-to-column
- Delete duplicate data
- Forcing input the correct data - how to ensure that data were specific format
- Simulation Analysis - how to prepare a professional presentation of possible scenarios
- Simulation Analysis - how to estimate the result of the formula
- Grouping and autokonspekty - how to roll up the rows and columns and show different levels of detail
PivotTable and PivotChart
- Calculated fields - how to add to the PivotTable field that is not on the sheet
- Computational elements in the table
- Grouping data and create professional-looking statements
Part II. Automation ie VBA.
Macros
- Recording and editing macros: Silence on the set - is recording
- Where to store macros - where best to write macros
Introduction to procedural programming - the necessary basis
- Sub and Function - how to invoke them and what they are
- Data Types - what variables are needed and whether it is worth it to declare
- The conditional statement If ... Then .... ElseIf .... Else .... End If
- Case statement and the accompanying trap
- Loop for ... next, loop ... each
- Loops for ... loop while, until
- Instructions loop break (exit)
Visual Basic in action
- Download and upload data to a spreadsheet (Cells, Range)
- Download and upload data to the user (InputBox, MsgBox)
- The extent and lifetime of variables
- Operators and their priorities
- Useful module options
- Securing code - code protection from tampering and preview
- Key Excel objects: Application, Workbook, Workbooks, Worksheet, Worksheets,
- ThisWorkbook, ActiveWorkbook, ActiveCell, Selection, Range, Cells, ...
Debugging
- Immediate window
- Locals window
- The processing step - but what to do when something has stopped working
- Watches
- Call Stack
Error handling
- Types of errors and ways to avoid
- Capturing and handling run-time errors, which is why properly written code can sometimes not work
- Construction: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Requirements
At least average knowledge of MS Excel.
Testimonials
Examples of improvements
Python for Excel Course
Access to efficient VMs, showing Flask in an accessible way
Python for Excel Course
This is probably the first training in which the trainer constantly showed potential problems when starting to write code and taught how to deal with them. These meaningless errors discouraged me from learning programming in Python on my own. Now I feel like I have a much stronger foundation for exploring new ideas :)
Python for Excel Course
many exercises
Julia - Angelika Czapka, LKQ Polska Sp. z o. o.
Analysing Financial Data in Excel Course
can use in the work
Sebastian, 弗马斯精密锻件(大连)有限公司
Excel Basics Course
some new functions i did knew before, such as power query; fomat cell -alignment-fill
Bella - Sebastian, 弗马斯精密锻件(大连)有限公司
Excel Basics Course
new facts
Angelika Czapka, LKQ Polska Sp. z o. o.
Excel Modelling Course
learned new things
Daria Pawlak - Angelika Czapka, LKQ Polska Sp. z o. o.
Excel Modelling Course
Practical tips
Angelika Czapka, LKQ Polska Sp. z o. o.
Excel Modelling Course
I learned new skills and information
fatma AlKetbi - Dubai Government Human Resources
Excel 2019 - Intermediate Course
The trainer was informative and has a smooth and easy way of teaching
Gihan El Shafei - Dubai Government Human Resources
Excel 2019 - Intermediate Course
Trainers are very knowledgeable and patient in ensuring students could follow the lecture.
Trudy Staples Girardin - Indigenous Services Canada
Excel to Power BI Course
I learned so much about Power BI, the trainers were patient and very knowledgeable.
Krystina Merkley - Indigenous Services Canada
Excel to Power BI Course
One hands-on exercise that is super relevant to work.
Fannie Mae
R Programming for Excel Course
The applicability of the content
EPFL HBP PCO
Analysing Financial Data in Excel Course
The tips for many of the functions that the trainer presented, which we can easily remember and implement in our future work
Emilija Stoilova - EPFL HBP PCO
Analysing Financial Data in Excel Course
Patience of Costas, adaptability to our various knowledge levels and work requirements. Possibility to ask questions.
Beatrice PLATEAU - Ameropa
Excel to Power BI Course
The guy was really helpful and knowledgeable and i really learned a lot in two days.
Francesca Maggiolo - Bettzeit GmbH
Excel Basics Course
Examples from different area of VBA programing.
UBS Business Solutions Poland Sp. z o.o.
Visual Basic for Applications (VBA) in Excel - Advanced Course
The instructors knowlege
US Bank
Visual Basic for Applications (VBA) in Excel - Advanced Course
Content and the way it was provided.
Marcin Samborski
Visual Basic for Applications (VBA) in Excel - Advanced Course
short excersises
Mateusz Podsiadlo
Visual Basic for Applications (VBA) in Excel - Introduction to programming Course
Training agenda was well thought out
Przemek Zalasinski
Visual Basic for Applications (VBA) in Excel - Introduction to programming Course
Kamil was exceptionally patient and very helpful in figuring out solutions to real needs. He was also very honest about if he didn't know something from the top of his head, which enabled us to quickly jump on in the training and we didn't lose time.
Christine Weiler
VBA For Access & Excel Course
The explanation way and including tips on the best practices in VBA/Access, encouraging via exercise to think more by ourselves on how to solve the problem rather than giving ready solutions
Daria Rudin
VBA For Access & Excel Course
examples and the way he explained