Zomalex | Data and AI Training
Excel Intermediate Course
Who should attend
People who want to improve their Excel skills, especially in looking up and reconciling data and building pivot tables.
Learning Objectives
You will learn how to summarise data using two methods: pivot tables and SUMIFS formulas, and how to look up data flexibly with the XLOOKUP function and the INDEX / MATCH pattern. You will learn about Excel tables.
Course Content
- Look up data with VLOOKUP, XLOOKUP (a simpler and more flexible alternative to VLOOKUP), and the INDEX/XMATCH pattern.
- Summarise data with pivot tables. Create a pivot table, arrange the layout, sort the items, filter the data with slicers, group data, and present the data in an appealing manner with conditional formatting and formats.
- Summarise data with formulas and functions such as SUMIFS and COUNTIFS, and the new GROUPBY and PIVOTBY functions.
- A brief introduction to spill functions, such as UNIQUE, and their uses.
Course Length
1 day (short version) or 2 days (preferred, full version)
Pre-requisites
Completion of the Excel foundation course.
Snapshots from the course exercises
One of the Excel formulas labs