Short Course: Excel

This four-module course is designed to bring your Microsoft Excel skills to the next level. 

 

Delivery

Broadcast_Icon40.jpg
Live Delivery - Closed Course (In Company Learning)

This is a four-module program delivered exclusively to your team at a time and schedule that suits your team's needs. Modules are delivered remotely through live lectures through the Zoom Webinars platform.

Closed programmes allow companies to use their own datasets ensuring learning is true to real life examples. Live lectures allow for class participation and Q&A sessions with each lecturer. They are recorded so that, if a team member misses a session, the recording will be available. Class sizes are capped to 16 participants. 

 

Module 1: Data Cleansing in Excel

Cleansing data can be an extremely time-consuming and laborious task. Excel has a range of tools and functions which can help cleanse and prepare data more efficiently.  Replacing values, removing duplicates and separating data into multiple columns are just some of the common tasks which will be covered in this session. You will also learn how to use Text Functions to help cleanse data and use Query Editor to access some additional cleansing tools, not available on the standard Excel menu.

Learning Outcomes
  • Know how to split columns, replace values and remove duplicates from large datasets
  • Use a range of shortcut keys and commands to speed up selection, editing and navigating a large dataset
  • Use Query Editor to profile data and to cleanse a dataset
  • Use a range of Text functions such as LEFT, MID, TRIM, CONCAT
Who's this for?

Current Excel users wanting to gain a better understanding of how to work with and prepare large datasets for analysis in a time-efficient manner

Duration
Half a day.

Module 2: Lookup Functions and Pivot Tables

Two of the most powerful and popular tools in Excel are lookup functions and pivot tables. This course will teach you how to write a VLOOKUP function and also an XLOOKUP (Office 365/Excel 2019 only). You will learn how to build and customise pivot tables so you can provide an insightful summary of large datasets

Learning Outcomes
  • Know how to write a VLOOKUP function
  • Understand the VLOOKUP arguments, including TRUE/FALSE
  • Understand why you might need an XLOOKUP instead of VLOOKUP
  • Create a pivot table from a large dataset
  • Customise pivot table design and layout
  • Use slicers and timelines to filter a pivot table
Who's this for?

Current Excel users who want to learn more about how pivot tables can be used and to understand more about the VLOOKUP/XLOOKUP Functions

Duration
Half a day.

Module 3: Advanced Excel: Using Functions to Create Dynamic Worksheets

Excel functions are a great way to enrich a dataset by generating additional information from the data. On this course you will learn how to work with a wide range of functions and how to nest them together to further enhance the data.

You will also learn how to create more dynamic worksheets by combining functions with features such as Named ranges, Format as Table and Conditional Formatting Rules.

Learning Outcomes
  • Use Named Ranges to enhance formula writing and create dynamic drop down lists
  • Nest INDEX, MATCH and VLOOKUP to perform 2d lookups
  • Use IF Function combined with AND/OR/ISBLANK/ISTEXT
  • Explore Date/Time functions such as  DATE, NETWORKDAYS, YEAR,MONTH, TODAY()
  • Use NEW Office 365 Functions: FILTER, UNIQUE, SORT
  • Create Custom Rules for Conditional Formatting
Who's this for?

Excel users, who are already familiar with basic formula and function writing and want to enrich their understanding of what is available and how to write nested functions. Users should be familiar with Absolute Referencing (locking cells with $), Basic IF Function and basic VLOOKUP

Duration
Half a day.

Module 4: Visualizing Data using Excel Dashboards

Dashboards are visually appealing and a great way to present a series of charts to an audience. With a few design tricks and tips, you can build an engaging and interesting dashboard in Excel to provide insight into the key patterns and trends in your data.

On this course you will learn how to create and format charts, applying Best Practice. You will then bring the charts together into a stunning dashboard for presentation

Learning Outcomes
  • Create visuals from datasets using a range of chart types
  • Apply Best Practice to chart design (chart type, formatting)
  • Build a dashboard using charts, pivot tables, slicers and timelines
Who's this for?
Excel users who want to visualize patterns, trends and key insights for viewing by an audience.
Duration
Half a day.
Enquire Now