Questions? Email us at [email protected]
JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!
Microsoft® Excel Stage 3 Training Course
Take your skills up MORE than a few notches!
Learn Lookup functions, Tables and Pivot Tables
Duration: 4 hours
Excel Combine, Analyse and Report - Stage 3
About this course
This half-day, high-end intermediate level course has been designed to introduce the features that assist you in combining, analysing and reporting on large data lists.
Topics include super helpful tips on preparing data for use in VLOOKUP formulas, creating dynamic ranges using Tables, and producing Pivot Table reports.
Is this course suitable for you or your team?
This course is suitable for anyone who is already knowledgeable in the topics covered in our Excel Essential Skills course and our Excel Organising and Analysing Data course, and is the next step to becoming a proficient user of Excel. This course is definitely for you if you want to:
- master using VLOOKUP and XLOOKUP functions to combine and cross-reference data
- learn how Tables allow you to reference dynamic ranges - no more having to reset your ranges!
- learn how to quickly consolidate and analyse data into meaningful Pivot Table reports
Learning outcomes
This hands-on course takes you through the steps you need to master in order to be a 'lookup' guru, use Tables and Table references to save you time and avoid potential errors and to confidently build meaningful reports using Pivot Tables.
Course pre-requisites
You would benefit from having an understanding of the skills covered in our and our Excel Organising and Analysing Data course. However, this isn't mandatory.
What you will learn...
Become a Lookup WHIZ! Not only will you learn how to create VLOOKUP and XLOOKUP functions, you will learn how to identify common problems that prevent these functions from working. Note: XLOOKUP is only available with a Microsoft 365 subscription
TOPICS COVERED
Cross-referencing data using the VLOOKUP function
Avoid the things that cause a VLOOKUP not to work
Learn the functions that are commonly used with VLOOKUP
VLOOKUP vs XLOOKUP
XLOOKUP features
Data can be quirky! You'll learn how to fix problems by preparing your data so that it will work with a lookup function. AND you'll learn the tips, tricks, hacks and complementary functions that will have you creating functions that rock!
TOPICS COVERED
Identify and remove duplicate records
Change numbers formatted as text back to numbers
Join the content of several cells into one cell
Split a single column of data into several columns
Quickly remove unwanted text and unwanted spaces inside of cells
The Table feature in Excel offers so many time-saving benefits. Create dynamic, multi-functional lists so that you never have to reset your ranges again. Learn how to use Tables to name these dynamic ranges and insert them into your formulas. AND how to use Tables and Pivot Tables together to save time resetting ranges. So great!
TOPICS COVERED
What are Tables and why use them?
Manage and analyse data ranges using the Table feature
Use dynamic Table ranges in formulas
Use dynamic Table ranges with Pivot Tables
You may have heard about them...seen them...even attempted to create one. Pivot Tables aren't that scary when you are shown the best way to create them. Learn how to consolidate your data quickly into POWERFUL reports. It changes your life!
TOPICS COVERED
Check your data is fit for pivoting
Create a new Pivot Table
Understand the Pivot Table cache
Change the layout and formatting of the report
Control how the data, totals and subtotals are displayed and summarized
Sort, filter and rank the data
Insert Slicers and Timelines
Move items into logical groups for ease of summarizing and performing data analysis
Group dates by month, quarter and year
Create calculations within the Pivot Table area
Display values as percentages of totals
Work with multiple data cache
What you will get...
A copy of our super popular step-by-step workbook along with file downloads for every exercise we cover together. Perfect to follow along with during the training, and to refer to after the training.
Capped class size to ensure you have the hands-on time required and your questions answered.
Need proof of your Continuing Professional Development (CPD)? On completion of the workshop, by request, we will send you a “Certificate of Attendance” confirming your participation and the duration of the training session.
For online sessions
We use the latest version of Zoom or Microsoft Teams to deliver this course live online. If you would like to follow along with your trainer please ensure you have the desktop version of Microsoft Excel available. Having a webcam and headphones available ensures you can enjoy interacting with your trainer and fellow participants. Please ensure your browser software is up to date too. For the best experience we would recommend using Microsoft Chromium Edge or Google Chrome as your browser.
Details on how to connect to the session, along with your workbook and exercise files will be emailed to you prior to your session. Having a printed copy of the workbook handy during your learning will be extremely beneficial to your learning.
$ Prices for Online Live Workshops
Any questions, please contact us at [email protected].
This course is available as a LIVE Online session, or as a self-guided Online course
Need a quote for a group booking?
Please contact us today.
This training is for the Windows Desktop version of Excel and may not be suitable for Mac users
Keen to do all 3 Stages of Excel?
Talk to us about booking an
'Excel Combo' and save!
People who booked this course also booked:
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand... [read more]
"This was a great course"
I will save 1 to 2 hours a day and additional time for my team as I will be running them through some of the content. Thank you!! This was a great course.
Bridget Armstrong
Logistics
"I learnt lots!"
The accessible language used and explanations was what I liked most about the course. I spend most of my day in Excel so the quick tips will save me hours of time a week. So impressed with how the whole group went at a steady pace despite different levels. Sharyn, you're a star! Very enjoyable and I learnt lots!
Rachel Alcorn
Human Resources
"Your training style is wonderful"
Thank you so much once again for the training yesterday. Feedback from the team is all very positive. Your training style is wonderful, and even the members of the team who probably considered themselves as capable Excel users had their minds blown a number of times.. which is awesome!
Craig Williams
Country Manager - New Zealand