02 Intermediate Excel Course

Essential Functions
in Excel

This training program is designed to teach the toolsets that transform an Excel beginner to a high-productivity user, including LOOKUP functionality, IF functions, and how to work with large data ranges effectively.

  • Adds to the skills of users who can already build basic formulas and functions
  • Course content can be customised so you learn exactly what you need
  • Learn skills that will transform you into a productive Excel power-user
Request a Quote
About course

Course Details

Customisation
This training course can be customised at no additional cost to include topics from our other courses.
Course delivery method
This course can be delivered on-site at your premises to individuals or groups, or online through live facilitator-led remote-access learning to individuals or groups.
Course duration
Full course duration is 1 day, but training can be delivered in shorter blocks.
What You Will Learn

Topics covered

Absolute and Mixed
References ($A$1)

Understanding how and why we need to “lock” cells is a must before moving on to intermediate Excel use.

  • Absolute reference overview
  • Creating absolute references (for example $A$1)
  • Creating mixed references (for example A$1 and $A1)

Named Ranges

When using IF and LOOKUP functions, we’ll need to know how to handle large rages of data. Named ranges accomplish this.

  • Using named ranges as a “go to” tool
  • Naming tables and larger ranges
  • Creating dynamic named ranges with tables

IF and Logical Functions

IF functions are a cornerstone of decision-making formulas in Excel. We’ll write these formulas, then combine them into larger formula structures.

  • IF functions returning text and numeric values
  • Nested IF functions
  • AND / OR functions
  • IFERROR functions

LOOKUP Functions

LOOKUP Functions such as VLOOKUP, CHOOSE and INDEX/MATCH are powerful tools that allow us to look up and retrieve data from tables or sheets.

  • VLOOKUP - range and exact match
  • VLOOKUP - exact match within databases
  • CHOOSE
  • INDEX/MATCH

Excel Tables

We can turn data ranges into tables, which enables powerful database and list calculation tools to increase functionality.

  • Creating, naming and filtering Excel tables
  • Removing duplicates
  • Adding total rows

Subtotalling and Grouping

We cover 2 related areas – how to group (or combine) data columns for ease of use, and use Excel’s subtotal tools to calculate database totals.

  • Creating manual and automatic groups
  • Using the Subtotal tool
  • Creating nested subtotals

Advanced Filters

Only Advanced Filters have the ability to extract data using OR relationships, and the time-saving advantages can be immense.

  • Using the Advanced filter
  • Date and number ranges
  • Extracting unique values

Pivot Tables

This powerful visual tool allows you to group, filter, sort and calculate database information with ease. We look at the basics through to advanced tools

  • Summing with PivotTables using single and multiple fields
  • Filtering, both manually and with Slicers
  • Showing database subsets
  • Creating PivotCharts

Data Validation

We can create our own drop-down boxes to help users enter data correctly by choosing from available options and preventing them from entering incorrect data.

  • Numeric and date validation
  • Creating data lists
  • Providing guidance via input and error messages

Custom Formatting

If a phone number in a cell appears as 355551111, we can use Custom Formatting to change its appearance to (03) 5555 1111. And it does much more.

  • Numeric and text custom formats
  • Formatting numbers with text characteristics
  • Combining text with number formatting

Benefits

Learn to build well-structured LOOKUP, CHOOSE and INDEX/MATCH functions, so they’re easy to create, deploy and modify.
Understand how to create well-designed IF functions, then building on this to create “nested” functions (functions within functions).
Learn how to use named ranges to turn large datasets into easily controllable areas to help shortcut the building of complex formulas.
Move past basic filtering to create high-level filtering structures that automatically calculate, as well as perform both AND and OR-related functions.
Learn to build and manipulate PivotTables quickly, including using slicers to apply filter conditions to multiple pivot tables at the same time.

Full course outline

The bulleted items listed above comprise a condensed overview of course topics.

Download full course outline (PDF)

Prerequisites

This course is designed for Microsoft Excel users who can build basic workbooks. It is assumed that the user will know how to create basic formulas using SUM and AVERAGE, and who are able to perform basic sorting and filtering of lists.

Test your teams skills

Get a Free Training Needs Analysis

Request training needs analysis