en
nlfr
IT Training

Excel 2019/365 – Level 2

Be able to work with the main functions. Nest functions. Master powerful analytics tools. Create models.

Who should attend this course?

This course is intended for advanced professionals who need to work with complex accounting, financial or scientific applications of Excel.

Prerequisites

Know the basic features of Excel (Formulas, Functions)

  • Working with the most important functions.
  • Nesting functions.
  • Mastering powerful analysis tools.
  • Create models.

New Excel functionalities in the version

  • New functions: CONCAT, TEXTJOIN, XLOOKUP.
  • New charts: MAP, FUNNEL, WATERFALL, HIERARCHICAL.
  • Accurate selection and improved autocomplete.
  • What’s new in pivot tables.
  • Introduction to Power Query.
  • DIFFERENCES BETWEEN Excel WEB APP AND Excel DESKTOP APP
    • Sign in to the Office 365 portal 365.
  • CHOOSE THE RIGHT STORAGE DRIVE FOR YOUR WORKBOOKS
    • Classical : on a file server or the hard drive of a device.
    • 365 : Personal OneDrive / Shared OneDrive / SharePoint-Teams.
    • Version history.

Review tips & tricks to optimize calculation operations

  • Master the principle of calculated cells addressing (relative, absolute or mixed references).
  • Insert functions using the wizard or in text mode (insert text in a function, use comparison criteria, work with range names, …)
  • Be able to nest functions.

Work with function categories

  • Work with « statistical » functions (AVERAGE, MAX, MEDIAN, …).
  • Work with logical functions (COUNTIF, SUMIF, IF, AND, OR, …).
  • « Text » functions (LEFT, FIND, UPPER, CONCATENATE, …).
  • « Date and Time » functions (DATEDIF, NOW, TODAY, YEAR, MONTH, DAY,…).
  • « Matrix » functions : VLOOKUP + XLOOKUP.

Introduction to databases

  • Master the terminology specific to databases.
  • Learn to design a database file allowing efficient analysis of information.
  • Manage custom toolbars to analyse data.
  • Tips for maintaining data integrity.
  • Use range names

Simple analytics tools

  • Master the concepts of simple sorting, sorting key, hierarchical sorting.
  • Master the concept of simple filter.
  • Use outline mode to make data reading easier

Database content automation

  • Validate data as a list.
  • Use Simple and Calculated Conditional Formatting.
  • Create visuals with Sparkline charts.
  • Segments
  • AutoFill (FLASHFIELD ? → Flash Fill).

Data analysis from pivot tables

  • Master the fundamentals of pivot table design.
  • Hide items.
  • Use integrated calculation functions (average, number, max, min,…).
  • Show subtotals
  • Update data.
  • Group elements (manually, automatically).
  • Consolidate data from multiple tables.
  • New in 2019 [365]
    • Automatic relationship detection.
    • Automatic time grouping.

Introduction to power Query

  • Be able to switch from Excel to Power Query and vice versa.
  • Be able to import data from different sources into Power Query (Excel, csv, text).
  • DATA TRANSFORMATION OPERATIONS
    • Manage columns and rows.
    • Rename headers.
    • Sort/Filter.
    • Adapt data types to the analysis needs (+automatic detection).
    • Create a header row.

Tips and tricks / exercises

Practical information

Duration

2 Days

Languages

FR / NL

Price

€800,00 + 21% VAT

Location

Classroom Courses

Schedule

Guaranteed to run

Dutch courses
19/1 - 20/1Book
16/2 - 17/2Book
16/3 - 17/3Book
27/4 - 28/4Book
25/5 - 26/5Book
15/6 - 16/6Book
19/7 - 20/7Book
24/8 - 25/8Book
21/9 - 22/9Book
19/10 - 20/10Book
16/11 - 17/11Book
14/12 - 15/12Book
French courses
26/1 - 27/1Book
23/2 - 24/2Book
23/3 - 24/3Book
27/4 - 28/4Book
30/5 - 31/5Book
19/6 - 20/6Book
24/7 - 25/7Book
21/8 - 22/8Book
28/9 - 29/9Book
26/10 - 27/10Book
20/11 - 21/11Book
14/12 - 15/12Book

Share this course on

Book your training

Enter your information to confirm your booking.

    You might also be interested in

    ExcelExcel
    Excel 2019/365 Power Pivot
    12/1, 09/2, 09/3, 20/4, 17/5 ...
    By the end of this course, you will be able to determine which of the two tools, PivotTables and PowerPivot,…
    ExcelExcel
    Excel 2019/365 Power Query – Level 1
    18/1, 08/3, 10/5, 05/7, 11/1 ...
    Forget VBA and discover Power Query to automate your data collection. This tool allows you to create complex queries without…
    ExcelExcel
    Excel 2019/365 Power Query – Level 2
    10/2, 21/4, 14/7, 15/9, 16/1 ...
    Thanks to a functional and non-developmental interface, Power Query facilitates the work of those who need to analyse data from…
    ExcelExcel
    Excel 2019/365 Power Query Complete
    23/2 - 24/2, 11/5 - 12/5, 06 ...
    Forget VBA and discover Power Query to automate your data collection. With this tool you can create complex queries without…

    Prerequisite test

    Take the test

    Looking for a tailor made solution?

    Get started with personal training