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 + 21% VAT

Location

Classroom Courses

Schedule

Guaranteed to run

Sessions in English
Contact us for more infoBook
Sessions in Dutch
24-25/02/2025Book
24-25/04/2025Book
19-20/06/2025Book
18-19/08/2025Book
23-24/10/2025Book
15-16/12/2025Book
Sessions in French
17-18/02/2025Book
17-18/04/2025Book
12-13/06/2025Book
11-12/08/2025Book
09-10/10/2025Book
04-05/12/2025Book

Share this course on

Book your training

Enter your information to confirm your booking.


    You might also be interested in

    Excel
    Excel 365 – Level 3 Power user
    Contact us for more info, 22 ...
    This 3-day course will train participants into becoming advanced Excel users by developing their skills regarding data handling, advanced functions,…
    Excel
    Excel 2019/365 Power Query Complete
    Contact us for more info, 03 ...
    Forget VBA and discover Power Query to automate your data collection. With this tool you can create complex queries without…
    Excel
    Excel 2019/365 – Level 1
    Contact us for more info, 16 ...
    Be able to create tables and manage the calculations, formatting and page layout of the tables.
    Excel
    Excel 2019/365 – Macro
    Contact us for more info, 10 ...
    Do you want to save time on routine tasks in Microsoft Excel? Macros are the answer. In this course the…

    Prerequisite test

    Take the test

    Looking for a tailor made solution?

    Get started with personal training