Download our guide to end user applications

Microsoft Excel - Intermediate

Length 1 day

Course overview

View dates &
book now

Why study this course

Learn how to work smarter in Excel, improve your efficiency, and impress with your results. You will learn how to better organise, analyse, and present your day-to-day data using Excel spreadsheets. Create complex formulas, use a range of functions, and develop dynamic charts and reports. We also introduce pivot tables, conditional formatting, defined names, and data validation. The course covers key concepts relevant to the workplace.

This course builds on what was learned in Microsoft Excel - Beginner. After completing this course, you may be interested in our Advanced course or one of our Excel Specialist courses.

Note: Excel 2016, Excel 2019, Excel 2021, and Excel 365 are all desktop application versions of Microsoft Excel. Excel 2016, Excel 2019, and Excel 2021 are the perpetual, bought-outright, stand-alone versions of the software; Excel 365 is the subscription-based version. Anyone working with any of these versions will be able to successfully complete this course. Throughout this course you will be using the desktop app; the course does not cover use of the web/online app.

Nexacu Public Schedule

Nexacu is part of the Lumify Group, offering you the largest public schedule of end user applications and professional development training in Australia, New Zealand, and the Philippines. You can now access the schedule of courses and book, by clicking on the button below.

Book now!

Request Course Information

What you’ll learn

At the completion of this course you should be able to:

Modify Excel options
Understand and use formula cell referencing
Use the fill operations available to fill a data series
Create and use defined names in a workbook
Use common worksheet functions
Create more complex formulas and functions
Apply a range of number formatting techniques to worksheet cells
Apply conditional formatting to ranges in a worksheet
Use a variety of data validation techniques
Use data linking to create more efficient workbooks
Use goal seeking to determine the values required to reach a desired result
Work with tables in Microsoft Excel
Understand and create simple PivotTables
Use a range of elements and features to enhance charts

Microsoft Apps at Lumify Work

Lumify Work has been delivering effective training across all Microsoft products for over 30 years. We are proud to be both Australia's and New Zealand’s first Microsoft Gold Learning Solutions Partner and the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers (MCTs) in ANZ. Join more than 5,000 students who attend our quality Microsoft courses every year.

View all Microsoft Apps at Lumify Work courses

Who is the course for?

This course is designed for existing users of Microsoft Excel who would like to expand their knowledge and learn more of the techniques associated with creating better and more productive workbooks.

Course subjects

Setting Excel Options

Understanding Excel Options
Personalising Excel
Setting the Default Font
Setting Formula Options
Understanding Save Options
Setting Save Options
Setting the Default File Location
Setting Advanced Options

Formula Referencing

Absolute Versus Relative
Referencing Relative Formulas
Problems With Relative Formulas
Creating Absolute References
Creating Mixed References

Filling Data

Understanding Filling
Extracting With Flash Fill
More Complex Flash Fill Extractions
Extracting Dates and Numbers

Defined Names

Understanding Defined Names
Defining Names From Worksheet Labels
Using Names in Typed Formulas
Applying Names to Existing Formulas
Creating Names Using the Name Box
Using Names to Select Ranges
Pasting Defined Names Into Formulas
Defining Names for Constant Values
Creating Names From a Selection
Scoping Names to a Worksheet
Using the Name Manager
Documenting Defined Names

Essential Functions

Worksheet Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
The VLOOKUP Function
Using Counting Functions
The ROUND Function
Rounding Up and Rounding Down
Manipulative Functions
The MOD Function
The TODAY Function
The NOW Function
The DATE Function
The PMT Function

Complex Formulas

Scoping a Formula
Long-Hand Formulas
Preparing for Complex Formulas
Creating the Base Formula
Adding More Operations
Editing a Complex Formula
Adding More Complexity
Copying Nested Functions
Switching to Manual Recalculation
Pasting Values From Formulas
Documenting Formulas

Number Formatting Techniques

Applying Alternate Currencies
Applying Alternate Date Formats
Formatting Clock Time
Formatting Calculated Time
Understanding Number Formatting
Understanding Format Codes
Creating Descriptive Custom Formats
Custom Formatting Large Numbers
Custom Formatting for Fractions
Padding Numbers Using Custom Formatting
Aligning Numbers Using Custom Formats
Customising the Display of Negative Values

Conditional Formatting

Understanding Conditional Formatting
Formatting Cells Containing Values
Clearing Conditional Formatting
More Cell Formatting Options
Top Ten Items
More Top and Bottom Formatting Options
Working With Data Bars
Working With Colour Scales
Working With Icon Sets
Understanding Sparklines
Creating Sparklines
Editing Sparklines
Creating Custom Rules
The Conditional Formatting Rules Manager
Managing Rules
Clearing Rules

Validating Data

Understanding Data Validation
Creating a Number Range Validation
Testing a Validation
Creating an Input Message
Creating an Error Message
Creating a Drop Down List
Using Formulas as Validation Criteria
Circling Invalid Data
Removing Invalid Circles
Copying Validation Settings

Data Linking

Understanding Data Linking
Linking Between Worksheets
Linking Between Workbooks
Updating Links Between Workbooks
Using Names to Link Between Workbooks

Goal Seeking

Understanding Goal Seeking
Using Goal Seek

Worksheet Tables

Understanding Tables
Creating a Table From Scratch
Working With Table Styles
Inserting Table Columns
Removing Table Columns
Converting a Table to a Range
Creating a Table From Data
Inserting or Deleting Table Records
Removing Duplicates
Sorting Tables
Filtering Tables
Renaming a Table
Splitting a Table
Deleting a Table

PivotTables

Understanding Pivot Tables
Recommended Pivot Tables
Creating Your Own PivotTable
Defining the PivotTable Structure
Filtering a PivotTable
Clearing a Report Filter
Switching PivotTable Fields
Formatting a PivotTable
Understanding Slicers
Creating Slicers
Inserting a Timeline Filter

Chart Elements

Understanding Chart Elements
Adding a Chart Title
Adding Axis Titles
Repositioning the Legend
Showing Data Labels
Showing Gridlines
Formatting the Chart Area
Adding a Trendline
Adding Error Bars
Adding a Data Table

GoTo Special

Finding Constants
Finding Formulas
Finding Blanks

Supplementary Content

SUMIF Functions
Special Pasting
Finding and Replacing
Text Functions
Financial Functions
Grouping and Outlining
Summarising and Subtotalling
Chart Object Formatting

Prerequisites

This course assumes the user has completed Microsoft Excel - Beginner or has the equivalent skills and knowledge about the basics of Excel.

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.