Excel – Foundation

Who is this course for? This course is for people who would like to improve their Excel skills and learn about the capabilities and functions in more modern versions of Excel.

Learning Objectives: Attendees will be more productive and be able to take advantage of the modern features and functions in Excel.

Course Length and Price: 2 days, £2,900 ex VAT

Pre-requisites: Basic familiarity with Excel.

Course Content:
Import data into Excel from different data sources e.g., flat CSV files, databases, web pages with Excel’s “Get & Transform Data” capabilities.

Create and use an Excel table; why organising our data into tables helps us to be more productive and the advantage of using Excel tables over an Excel range.

Create and use a pivot table; add a slicer, create calculated fields, connect to external data sources, refresh datasets and change data sources.

Use conditional formatting techniques, such as data bars, background colours, icons, to make tables and pivot tables more appealing and insightful. Create sparklines for time-series data.

Data validation and data entry; for example, how to create a list of items to use as a picklist and to identify and remove duplicates.

Functions that spill: what they are, how to use them and look at situations where they are useful. These include the FILTER, SEQUENCE, UNIQUE, SORT, TRANSPOSE functions.

The XLOOKUP function, why it is good replacement to the notorious VLOOKUP function.

Linked Data Types – with case studies to why they are useful.

Cube Functions – what they are and their advantages over pivot tables to build a polished Excel report.

Below are some images from the lab exercises on the course.

Excel XLOOKUP examples
The XLOOKUP function is a simpler and more flexible alternative to VLOOKUP.
Excel tab showing two tables
Compare SUMIFS() and pivot table approaches to summarise data.
Excel tab showing fictional dataset
Excel’s FILTER functions allows us to view a subset of data from a table.
Excel tab showing Star Wars characters
Excel linked data types and the FILTER function analyse Star Wars characters