– Participants should have an intermediate level in Excel including
* named range
* basic knowledge of Index, Match, SUM, IF formula
* pivot tables
– Participants should have a basic level of VBA (recording a macro, variables, loop, conditions)
At the end of the course, the student should be able to:
– use Excel for advanced analysis using following tools:
* advanced use of spreadsheet functions (sumifs, index/match,sommeprod, matricial functions)
* analytical tools in Excel (Solver, Pivot table, Powerpivot, DAX)
* intermediate and advanced use of Visual Basic for Applications
These competencies and/or skills contribute to following learning objectives
– 3.B Propose creative solutions within an organization
– 3.C Organize change management processes
– 4.A. Appraise the performance of a team
– 4.B Compose constructive personal feedback and guidance
– 4.C. Convey powerful messages using contemporary presentation techniques
– 5.D. Make effectual organizational decisions
– Advanced use of excel functions
* Quick overview of the basics : Sumifs, Index/Match,Sumproduct, matricial functions, conditional formatting
* Combination of functions in power-formulas
* Reporting & Dashboards
– Analytical tools in Excel
* Pivot Tables
* PowerQuery
* Powerpivot and DAX
– Intermediate and advanced use of VBA
* Quick overview of the basics : variables, loops and conditions.
* Intermediate : arrays, user-defined functions, userforms.
* Advanced : add-ins, class modules, events procedures, ribbon.
– During the course, we will also cover best practices for designing robust applications using spreadsheets.
