Excel 2019/365 – Level 2

Tips & tricks review 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 function categories

Working with “”Statistical functions”” (AVERAGE, MAX, MEDIAN, …)

Working with “”Logical functions”” (COUNTIF, SUMIF, IF, AND, OR, …)

Using “”Text functions”” (LEFT, FIND, UPPER, CONCATENATE, …)

Using “”Date and Time functions”” (DATEDIF, NOW, TODAY, YEAR, MONTH, DAY, …)

Using “”Matrix functions”” : VLOOKUP + XLOOKUP

Calculate with named cell ranges

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 the Table tool

Review of simple analysis tools

Being able to sort: simple sorting, sorting criteria and first key sort order

Using Group and Outline to improve the visibility of data

Automating database contents

Data validation as lists

Using Conditional formatting (simple or calculated)

Creating visual effects with Sparkline charts

Slicers

FLASHFILL

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

Showing subtotals

Updating data

Grouping elements (manually, automatically)

Consolidating data from multiple tables

2019 new features

Relationship auto-detection

Automatic time grouping

Introduction to Power Query

Switching back and forth between Excel and Power Query

Importing data from different sources in Power Query (Excel, csv, texte)

Transforming data :

Managing columns and rows to keep

Renaming headers

Sorting / filtering

Adapting data types to analysis needs (+ automatic detection)

Creating a header row

Practical exercises + Tips & Tricks