Excel 2019/365 – Level 2

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
  • Using conditional formatting with values and formulas
  • 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
  • Hiding elements
  • Using the integrated calculations (average, count, max, min…)
  • Sub-totals
  • Updating data
• Grouping items (manually, automatically)
• Consolidating data from multiple tables

Creating customized charts

Working with templates

Recording macros

Tips and tricks / exercises