FR
EN
Details
Goals
To be able to use the advanced functions of the Excel spreadsheet.
Prerequisite
Know the main functions of Excel (see Excel 365 - Level I course).
Contents
Review
- Formulas
- Functions
- Format as Table
Importing 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
- XLOOKUP vs VLOOKUP
- MINIFS, MAXIFS, SUMIFS
- YEAR, YEARFRAC
- IF vs IFS vs SWITCH
Pivot tables
- Simple pivot table
- Page layout
- Multiple calculations and "Show values as"
- Slicers and Timelines
- Pivot charts
- Conditional formatting in a Pivot Table
Data Model Demo
- Creating a Pivot Table based on the Data Model (more details in the Excel Data Model (PowerPivot) and Excel Data Model (PowerPivot) Advanced courses)
Dynamic formulas and array functions
- Simple formulas
- Array references (#)
- UNIQUE, SPELL
- FILTER, VSTACK, HSTACK, CHOOSECOLS....
- GROUPBY, PIVOTBY
- TRIMRANGE
Sign up
Next session(s)
To go further
These courses
may be of interest to you
Need a custom training?
None of the trainings fit your requirements? You have specific needs? Key Job trainers are versatile and flexible. Contact us!