Excel 2019/365 – Level 2

• 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