From Zero to Hero with Microsoft Excel («MEZH»)

• Creating your own tables efficiently with a wide variety of calculations
• Understanding about percentage calculation, date and lookup functions
• Consolidating multiple worksheets or workbooks and structuring lists efficiently
• Summarising, analysing and evaluating data with PivotTable reports
• Visualising data effectively with PivotCharts and understanding the Excel data model
• Becoming familiar with the features required to protect workbooks or worksheets
• Visualising (key) figures and using the different chart types effectively
• Using special charts such as waterfall, funnel and map charts
• Designing Excel forms and understanding the most important ActiveX controls

1 Onboarding (1 hour)

2 Efficient calculations with Excel (2 days)

• Introduction and repetition
• Cell references
• Working with functions
• Rounding results (incl. rounding by five)
• The IF function
• The AND and OR functions
• Using names
• Consolidating tables
• Date functions and time calculations
• LOOKUP functions
  • VLOOKUP function
  • HLOOKUP function
• Charts (short introduction)
• Data visualization with sparklines
• Conditional formatting

3 Business charts with Excel (0.5 day)

• The most important business chart types
  • Columns / Bars / Lines
  • Pie / Scatter / Bubble
  • Radar / Area maps (only with Office 2019/365)
  • Waterfall / Funnel / Composite
• Create chart
• Customize chart
• Statistical elements
• Secondary axis
• Dynamic data ranges
• Tips and tricks
  • Customize data point symbol
  • Chart templates
  • Start of line at value axis
  • Dynamically highlight bars
  • High-low lines and series lines

4 PivotTables and Data Analysis with Excel (1 day)

• Sorting data
• Filtering data
• PivotTables and PivotCharts
  • Create a PivotTable report
  • Filtering Pivot Data
  • Apply slicers and timelines
  • Sorting and grouping data
  • Calculated fields
  • GETPIVOTDATA function
  • Consolidate data from multiple tables (PowerQuery)
  • PivotCharts
• External data

5 Excel for Power Users (2 days)

• Introduction and repetition
• Use of names
• Lookup functions, including VLOOKUP, HLOOKUP, MATCH and INDEX
• Text functions, e.g. LEFT, RIGHT, FIND, MID, TRIM
• Other important functions, including COUNT IF(S), SUM IF(S), INDIRECT, OFFSET
• Catch errors in functions, e.g. IFERROR, ISERROR
• Statistical evaluations, e.g. RANK, STDEV.P, and FREQUENCY
• Cube functions
  • Excel data model
  • OLAP Tools
  • CUBEVALUE, CUBESET, CUBEMEMBER, etc.
• Conditional formatting with functions
• Data validation (validity)
• Protect data

This course is aimed at individuals who have a basic knowledge of Microsoft Excel, but would like to learn extensively about the Excel application in order to efficiently deal with calculations, visualizations, forms and templates. Also recommended for people who want to take the official Microsoft Office Specialist (MOS) Excel Expert certification.

In order for this intensive and comprehensive Excel course to be a success, the following course or equivalent knowledge is required:

Following this 5.5-day Excel course, it is possible to take the Microsoft Office Specialist (MOS) Excel Expert exam (MO-201). Approximately 3 months of practical application of the course content is recommended prior to taking the exam.