Excel 365 - Level 3 Power user

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

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 FEATURES COVERED IN THIS TRAINING

• New functions: CONCAT, TEXTJOIN, XLOOKUP
• New charts: Funnel, Waterfall, Hierarchical
• Improved precision selection and enhanced semi-automatic data entry
• New features in PivotTables
• Power Pivot
• 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

REVIEW OF TIPS & TRICKS TO OPTIMIZE CALCULATION OPERATIONS

• Mastering the principles of addressing calculated cells (relative, absolute, and mixed references)
• Entering functions using the function wizard or in text mode (including inserting text into functions, using comparison criteria, working with named ranges, etc.)
• Nesting functions

REVIEW OF BASIC ANALYSIS TOOLS

• Mastering the concepts of simple sorting, sort keys, and hierarchical sorting
• Mastering basic filtering concepts
• Using outline mode to make data easier to read

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

INTRODUCTION TO DATABASES

• Mastering database-specific terminology
• Learning how to design a database file that enables efficient data analysis
• Managing custom toolbars for data analysis
• Tips for maintaining data integrity
• Using the Table tool and named ranges

AUTOMATING DATABASE CONTENT

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

ANALYZING DATA USING PIVOTTABLES & POWER PIVOT

PIVOTTABLES: ANALYSIS OF A SINGLE FILE

• Mastering the fundamental concepts required to design PivotTables
• Using built-in calculation functions (average, count, max, min, etc.)
• Displaying subtotals
• Refreshing data
• Grouping items (manually and automatically)
• Consolidating data from multiple tables

POWER PIVOT: CENTRALIZED ANALYSIS OF MULTIPLE FILES

• File storage strategy (Teams workspace)
• Power Pivot analysis (in parallel with PivotTables)
• Introduction TO power Query
• Prepare / clean data in order to make it easier to use with PivotTables or Power Pivot

DATA TRANSFORMATION / ENHANCEMENT OPERATIONS

• Managing columns and rows to keep
• Renaming headers
• Sorting / filtering
• Adapting data types to analysis needs (including automatic detection)
• Creating a header row
• Pivoting / unpivoting tables
• Removing duplicates

PRACTICAL HANDS-ON EXERCISES + TIPS & TRICKS

• Includes the key concepts from Level 1 and Level 2
• Concise, efficiency-focused approach
• Less pedagogical for complete beginners
• Better suited to autonomous and operational profiles

This format is an alternative to Levels 1 & 2, condensing the training into 3 days instead of 4.

Classroom Courses