Description
Objectives
At the end of this training, the participant will be able to use the advanced functions of the Excel spreadsheet.
Programme
Review
Formulas
Functions
Tables and Data
Apply Format as Table
Import data from external sources
Basic use of Power Query (more details in the “Get & Transform Data (Power Query)” course)
Formulas in tables
Structured references in tables
Simple functions in tables
Advanced Functions
XLOOKUP vs VLOOKUP
Conditional functions: MINIFS, MAXIFS, SUMIFS
Date functions: YEAR, YEARFRAC
Logical functions: IF vs IFS vs SWITCH
Pivot Tables
Create a simple Pivot Table
Page layout and customisation
Multiple calculations and “Show Values As”
Slicers and Timelines
Pivot Charts
Conditional formatting in Pivot Tables
Data Model demo
Create a Pivot Table based on the Data Model (more details in “Excel Data Model (Power Pivot)” and “Advanced Power Pivot” courses)
Dynamic Arrays and Formulas
Simple formulas
Array references (#)
Key functions: UNIQUE, SORT, FILTER, VSTACK, HSTACK, CHOOSECOLS
Advanced functions: GROUPBY, PIVOTBY, TRIMRANGE
Conditions
Support de cours
-
Des supports de cours seront disponibles après les modules respectifs
-
Die Kursunterlagen werden nach den jeweiligen Modulen zur Verfügung gestellt
-
Course materials will be made available after the respective modules
Certificate
At the end of the training, participants will receive a certificate of attendance issued by the House of Training.
Location
L-1611 Luxembourg
Luxembourg
Sessions and schedules
Would you like to stay up to date on our training offer?
Subscribe to our newsletter