Microsoft Excel Level 2

Overview

In this course we assume the knowledge from our Level 1 course. This course covers data analysis using Tables and Pivot Tables. It will use functions used frequently when analysing data such as Lookups and the M365 #SPILL functions of Sort and Filter. This course will also give you an introduction to Power Query.

Course Objectives

In this course, participants will learn how to efficiently convert data ranges into tables, facilitating organized data management. They will also gain the skills to perform in-depth data analysis by harnessing the power of Pivot Tables. Additionally, students will explore advanced functions using LOOKUPS and IFS as well as the new M365 #SPILL formula.

Content

Formatting

Tips and Tricks
Custom Formatting Options
Conditional Formatting
Transpose Data Option

Calculations

Auditing Tools
Name A Cell & A Cell Range
Name Manager
Cell Referencing Options
Revision of Basic Daily Functions
CleanUp Functions such as TRIM, UPPER & LOWER
Splitting Data Options incl. FlashFill

Dynamic Ranges

Creating Tables From A Range Of Cells
Naming A Table
Table Style Options incl. Total Row
Use of Table Names in Functions
Referring To Tables In Other Sheets

Advanced Functions

IF Statements
Nested IF and IFS (365)
VLOOKUP & HLOOKUP Functions
XLOOKUP Function

Working with Lists

SORT and FILTER #SPILL formulas
Advanced Conditional Formatting
Subtotal Command & Aggregate Function

Functions

Countif
Countifs
Sumif
Sumifs

Pivot Tables

Creating and Formatting Pivot Tables
Calculations in a Pivot Tables
Slicers and Timelines
Working with Dates

Power Query

GET & TRANSFORM
Getting started with the Power Query Editor interface
Split Columns
Format & TRIM
Unpivot Columns
Append multiple sheets

Download

Buy Now

You will learn how to

Custom format cells
Create conditional formatting rules to help present your data
Create and use named cells and ranges
Use text functions to increase your productivity when working on imported data
Create and use Tables in Excel
Create and edit IF and nested IF (IFS) functions
Create and edit LOOKUP functions
Protect your data and your workbooks
Work with Pivot tables to analyse your lists
Use of Power Query
… and much more

Audience

Essential to have a good understanding of Excel before attending this course. If you are about to start a new role, or are looking for a new position, this course is ideal. Software is provided in the Classroom and Live Virtual options.

Duration

2 Day or 4 x 3 Hour Sessions

Contact Us

We’ll reply quickly.

Enquiry

I understand that my personal data is being processed in accordance with the privacy notice and accept the terms and conditions of use.