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

publish

Languages

FR / NL

Price

€ 800 + 21% VAT

Location

Classroom Course

Schedule

Guaranteed to run

Sessions in English
Contact us for more infoBook
Sessions in Dutch
18 - 19/1/2024Book
15 - 16/2/2024Book
14 - 15/3/2024Book
18 - 19/4/2024Book
30 - 31/5/2024Book
13 - 14/6/2024Book
25 - 26/7/2024Book
29 - 30/8/2024Book
19 - 20/9/2024Book
17 - 18/10/2024Book
14 - 15/11/2024Book
12 - 13/12/2024Book
Sessions in French
25 - 26/1/2024Book
22 - 23/2/2024Book
21 - 22/3/2024Book
25 - 26/4/2024Book
23 - 24/5/2024Book
20 - 21/6/2024Book
18 - 19/7/2024Book
22 - 23/8/2024Book
26 - 27/9/2024Book
24 - 25/10/2024Book
21 - 22/11/2024Book
12 - 13/12/2024Book

Share this course on

Book your training

Enter your information to confirm your booking.

    You might also be interested in

    ExcelExcel
    Excel 365 – Level 3 Power user
    Contact us for more informat ...
    This 3-day course will train participants into becoming advanced Excel users by developing their skills regarding data handling, advanced functions,…
    ExcelExcel
    Excel 2019/365 – Level 1
    Contact us for more info, 23 ...
    Be able to create tables and manage the calculations, formatting and page layout of the tables.
    ExcelExcel
    Excel 2019/365 – Macro
    Contact us for more info, 12 ...
    Do you want to save time on routine tasks in Microsoft Excel? Macros are the answer. In this course the…
    ExcelExcel
    Excel 2019/365 Power Pivot
    Contact us for more info, 11 ...
    By the end of this course, you will be able to determine which of the two tools, PivotTables and PowerPivot,…

    Prerequisite test

    Take the test

    Looking for a tailor made solution?

    Get started with personal training