Excel 365 - Level 3 Power user

Know the basic features of Excel (Formulas, Functions)

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.

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

Classroom Courses