Excel 2016/2019/365 - Level 2

Handling the important functions. Nesting functions. Using the powerful data analysis tools. Creating automated templates and macros.

• Review tips & tricks to optimize calculation operations
• Manipulating categories of functions
• Automating worksheets by using Formulas and Functions
• Introduction to databases
• Analyzing data by using Pivot tables
• Creating customized charts
• Working with templates
• Recording macros
• Tips and tricks
• Exercises

Review tips & tricks to optimize calculation operations

• Managing the principle of calculated cell addresses (Absolute, relative and mixed references)
• Inserting functions by using the Assistant or by inserting manually (inserting text in a function, using criteria and equations, using range names, …)
• Nesting functions

Manipulating categories of functions

• Working with "Statistical functions" (Average, Max, Median, ...)
• "Logical" functions

          o   Using conditional formatting with values and formulas
          o   Using logical functions (Countif, Sumif, If, And, Or, …)

• Using "Text" functions 
  (Left, Find, Upper, Concatenate, …)
• Using Date and Time functions 
  (Now, Today, Year, Month, Day, …)

Automating worksheets by using Formulas and Functions

• Validating data in lists
• Using and optimizing the "Vlookup" function

Introduction to databases

• Using the vocabulary which is specific for databases
• Learning to create a database file which enables an efficient analysis of the information
• Managing personalized toolbars to analyse data
• Tips to guarantee data integrity
• Using range names

Simple analysis tools

• Being able to sort: simple sort, sort criteria and first key sort order
• Using the autofilters
• Using Group and Outline to improve the visibility of data

Analyzing data by using Pivot tables

• Knowing the features which are needed for the creation of pivot tables

          o   Hiding elements
          o   Using the integrated calculations (average, count, max, min…)
          o   Displaying subtotals
          o   Updating data

• Grouping items (manually, automatically)
• Consolidating data from multiple tables

Creating customized charts

Working with templates

Recording macros

Tips and tricks

Exercises