Excel 2019/365 - Level 2

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