This class exists in these courses :
• IÉSEG > IESEG Degree - Programme Grande École > Semester 1 > 2,00 ECTS
• IÉSEG > IESEG Degree - Programme Grande École > Semester 2 > 2,00 ECTS

Prerequisites

Basic knowledge of MS Excel :
- The different components of a workbooks
- Cell formatting
- Named range
- $ in a formula
- basic functions : IF, SUM, AVERAGE

Learning outcomes

At the end of the course, the student should be able:
To create a solid, robust and well-designed application for MS Excel. In addition, the following functions would be mastered : pivot table, sumproduct, sumifs, index, match, vlookup, powerpivot, powerquery, advanced techniques for interactive and dynamic charts and reports.

Create a VBA application displyaing a userform linked to the excel workbook

Course description

The following questions will be treated :
Excel tools and Functions :
- Advanced usage of data validation
- Sumifs
- Sumproduct
- Most powerful tips and tricks
- vlookup vs Index/Match
- Interactive and dynamic chart and report
- Conditional formatting
- Pivot table, PowerQuery and PowerPivot
Excel programming langage (VBA) :
- Variables,
- Loop,
- Conditions,
- Userforms

During the course, the structure of the workbook, the best practices and the methodological points will also be discussed.

Class type

Class structure

Teaching methods

• Interactive class
• Project work
• Seminar
• Tutorial

Assessment

Recommended reading

• Pivot table – Data crunching – Bill Jelen and Michael Alexander – Business solutions, 2013 -

• All the books from Walkenbach -

• Online video, either recorded during the course or before the course, will be provided - A full online VBA course will also be provided -

Internet resources

• IESEG Online

• XLerateur (French)