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.