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.
- Advanced modeling with spreadsheets
- Enlarge the scope of students’ view of Excel’s capabilities.
- 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.