Excel 365 – Level 3 Power user

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