Excel 2019/365 - Level 2

Users with a solid foundation in Excel who wish to go further in data analysis.

Be familiar with Excel’s basic features (formulas, functions, sorting and filtering).

Deepen advanced calculation and analysis functionalities.

NEW FEATURES OF EXCEL 2019 COVERED IN THIS TRAINING

• New functions: CONCAT, TEXTJOIN, XLOOKUP
• Improved precision selection and enhanced semi-automatic data entry
• New features in PivotTables
• Introduction to Power Query
• DIFFERENCES BETWEEN Excel WEB APP and Excel DESKTOP APP
• Signing in to the Office 365 portal
• Choosing the right storage drive for workbooks
• Traditional: file server or local hard drive
• 365: Personal OneDrive / Shared OneDrive / SharePoint-Teams
• Version history
• Collaboration: working together on the same workbook

REVIEW OF TIPS & TRICKS TO OPTIMIZE CALCULATION OPERATIONS

• Mastering cell referencing principles (relative, absolute & mixed references)
• Entering functions in text mode (including text in functions, using comparison criteria, working with named ranges, etc.)
• Nesting functions

HANDLING FUNCTION CATEGORIES

• Working with Statistical functions (AVERAGE, MAX, MEDIAN, etc.)
• Working with Logical functions (COUNTIF, SUMIF, IF, AND, OR, etc.)
• Text functions (LEFT, FIND, UPPER, CONCATENATE, etc.)
• Date and Time functions (DATEDIF, NOW, TODAY, YEAR, MONTH, DAY, etc.)
• Array functions: VLOOKUP + XLOOKUP
• Performing calculations using named cell ranges

INTRODUCTION TO DATABASE CONCEPTS

• Mastering database-specific vocabulary
• Learning how to design a database file that enables efficient data analysis
• Managing custom toolbars for data analysis
• Tips to maintain data integrity
• Using the Table tool

REVIEW OF BASIC ANALYSIS TOOLS

• Mastering simple sorting concepts, sort keys, and hierarchical sorting
• Mastering simple filtering concepts
• Using outline mode to improve data readability

AUTOMATING DATABASE CONTENT

• Data validation using lists
• Using simple and formula-based conditional formatting
• Creating visuals with Sparkline charts
• Slicers
• Automatic fill (FLASH FIELD)

ANALYZING DATA WITH PIVOTTABLES & POWER PIVOT

• Mastering the fundamental concepts required to design PivotTables
• Using built-in calculation functions (average, count, max, min, etc.)
• Displaying subtotals
• Refreshing data
• Grouping items (manual and automatic)
• Consolidating data from multiple tables
• New features [365]
• Automatic relationship detection
• Automatic time grouping

INTRODUCTION TO POWER QUERY

• Switching between Excel and Power Query
• Importing data from various sources into Power Query (Excel, CSV, text)
• Data transformation operations:
• Managing columns and rows to keep
• Renaming headers
• Sorting and filtering
• Adapting data types to analysis needs (including automatic detection)
• Creating a header row

PRACTICAL HANDS-ON EXERCISES + TIPS & TRICKS

• Advanced calculation functions
• Complex data analysis
• PivotTables
• Introduction to Power Pivot
• Introduction to Power Query (data preparation and transformation)

This level strengthens analytical and automation capabilities, particularly when working with larger volumes of data.

Classroom Courses