Course package

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

In this 6-day intensive Microsoft Excel course series, you will learn to confidently apply extensive Excel knowledge in your professional life and position yourself as an Excel professional.
Duration 6 days
Price 3'200.–   exkl. MWST
Course documents Digital Digicomp courseware

Course facts

  • Creating your own tables efficiently with a wide variety of calculations
  • Understanding about percentage calculation, date and lookup functions
  • Consolidating multiple worksheets or workbooks
  • Skillful and efficient structuring of lists for further use, including filtering
  • Summarizing, analyzing, and reporting data using PivotTable reports
  • Visualizing data effectively using PivotCharts
  • Understanding of the Excel data model
  • Getting to know the necessary features to protect workbooks or sheets
  • Visualizing (key) figures and targeted use of the different chart types
  • Using special charts such as waterfall charts, funnel charts or map charts
  • Designing Excel forms
  • Understanding and handling of the most important ActiveX controls
  • Creating Excel forms and templates independently
  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
  6. Forms and templates in Excel (0.5 day)
    • Concept of the structure of a form
    • The different controls
      • Form controls
      • ActiveX controls
    • The most important properties of the controls
    • Structuring from the spreadsheet with the output cells
    • Evaluation of the selected option of a control
    • Structure of a form (own selection)
      • Offer form
      • Newsletter/registration form
    • Creating and saving Excel templates

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 6-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. 

Download

Questions

Choose your date

Further courses