en
nlfr
IT Training

Excel 365 – Level 3 Power user

This 3-day course will train participants into becoming advanced Excel users by developing their skills regarding data handling, advanced functions, pivot tables, v-look ups, complex charts and using the integrated Power tools (Power Query, PowerPivot). The goal is to strengthen their data analysis capabilities, automate complex tasks and optimize their productivity in a professional environment.

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)

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

€ 1 200 + 21% VAT

Location

Classroom Courses

Schedule

Guaranteed to run

Sessions in English
Contact us for more informationBook
Sessions in Dutch
08 - 10/1/2024Book
04 - 06/3/2024Book
06 - 08/5/2024Book
01 - 03/7/2024Book
02 - 04/9/2024Book
04 - 06/11/2024Book
Sessions in French
05 - 07/2/2024Book
15 - 17/4/2024Book
15 - 17/6/2024Book
05 - 07/8/2024Book
07 - 09/10/2024Book
09 - 11/12/2024Book

Share this course on

Book your training

Enter your information to confirm your booking.

    You might also be interested in

    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 – Level 2
    Contact us for more info, 18 ...
    Be able to work with the main functions. Nest functions. Master powerful analytics tools. Create models.
    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