Efficient Data Management using Excel (Level 2): Distilling Information from Data

Duration: 2 Days

Course Overview

This intermediate-level Excel course is designed to empower professionals with the skills to transform raw data into meaningful insights. Participants will learn how to clean, organize, analyze, and summarize data using Excel’s powerful built-in tools. Through hands-on exercises and real-world examples, learners will gain confidence in using formulas, PivotTables, conditional formatting, and data validation to make data-driven decisions.

Whether you’re working with sales figures, customer feedback, or operational metrics, this course will help you distill complex datasets into clear, actionable information.

On-line from
your chosen location

At our dedicated
training facility

On-site at
your premises

Target Audience

Professionals who work with data regularly, such as:
Business analysts, Project managers, Sales and marketing professionals, Finance and operations staff,
HR professionals managing reports or dashboards.

Anyone who:
Can confidently navigate Excel and use basic formulas (e.g., SUM, AVERAGE, IF)
Wants to learn how to analyze, summarize, and visualize data more effectively
Needs to clean and prepare data for reporting or decision-making
Is looking to automate repetitive tasks using formulas or PivotTables

COURSE OBJECTIVES

Examples covered in this course:

• Identify differences in lists
• Develop dynamic tables of data
• Extract and replace text from an item in a list
• Design cascading data validation lists
• Calculate the number of distinct values
• Report on data from a list
• Combine data from another table
• Correct a poorly designed dataset
• Cleanse a dataset
• Audit the changes made to a dataset
• Automate applying changes to a dataset and multiple files
• Design reports
• Automate reports updating from a dataset
• Design adaptable charts that automatically update and are easy to manage

Course Content

Module 5: Implementing Functions to Extract Data

• Identify a piece of data with MATCH, VLOOKUP, INDEX, XLOOKUP
• Extract subsets of data with FILTER, UNIQUE, TRANSPOSE, SORT

Module 6: Importing and cleansing data with Power Query

• Navigate the Power Query interface
• Import and transform data using Power Query
• Identify data errors
• Relate and combine datasets

Module 7: Analysing Data with PivotTables

• Gain an insight into data
• Quickly restructure data
• Group and aggregate the data
• Design a data model
• Introduction to PowerPivot

Module 8: Visualising Data with Charts

• Quickly identify key points from data
• Create charts from Tables and PivotTables
• Manipulate the look and feel of a chart
• Implement SPILL ranges into a chart

COURSE PREREQUISITES

It Is Important you to have knowledge of Level 1 that is:

• Working with mixed references
• Know how to Implement and read functions
• Nest a function within a function
• Apply error handling functions
• Create a data set correctly
• Define and use Names
• Design easily to manage and adaptable calculations
• Automate calculation references being updated
• Implement a Data Validation list with formulas
• Define Conditional Formatting with formulas

You will receive a full set of course notes
and all supporting materials for your course.

Hard Copy Delivered to your premises or Downloaded to a chosen device.