Modeling and Data Analysis with Excel

A deep knowledge of Excel and modeling is essential to produce useful and efficient work with spreadsheets.

This course aims to present many advanced tools and functions that will bring students closer to a mastery of Excel.

Students must have a basic-to-medium Excel knowledge ; specifically, they should know:

  • Relative and absolute references
  • The use of names on cells or ranges
  • Boolean values and the If() function
  • Search functions such as Match(), Index(), Vlookup()
  • Conditional formatting using formulas
  • Basic knowledge on data bases and pivot tables.

A short review of some of these functions will be given during the first class.

Course Objectives

  • Advanced modeling with spreadsheets
  • Enlarge the scope of students’ view of Excel’s capabilities.

Course Content

  • Important functions such as Offset(), Indirect(), Sumif(), Row(), Rows(), Countif() and more
  • Relative names
  • Troubleshooting a spreadsheet
  • Error handling
  • Linking between sheets or workbooks, consolidation
  • Working with dates
  • Working with text
  • Advanced work with databases
  • Advanced work with pivot tables (Tableaux croisés dynamiques in French)
  • Array functions
  • Recording and modifying a macro.