Excel 365 - Level II

At the end of this training, the participant will be able to use the advanced functions of the Excel spreadsheet.

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

At the end of the training, participants will receive a certificate of attendance issued by the House of Training.