Excel 2013/2016 - Advanced

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…)
    

  •  Displaying subtotals
    

  • Updating data

• Grouping items (manually, automatically)
  

• Consolidating data from multiple tables
  

Creating customized charts

Working with templates

Recording macros

Tips and tricks

Exercises