Half Day Courses

From 9.30am to 12.30pm in our training room at Perth Airport or online via Teams.

COURSE OUTLINE

Ideal if you are new to Excel or need to brush up your skills.

Exploring Excel

  • Creating a new workbook
  • Navigating the ribbon
  • Features of a sheet

 Basic Workbook Skills

  • Selecting cells & ranges
  • Working with rows & columns
  • Working with sheets

Basic Formulas

  • Understanding formulas
  • Adding, subtracting, multiplying & dividing formulas

Basic Functions

  • Autosum, average, count, max, min

Formatting data and cells 

  • Formatting numbers and text
  • Colouring data and cells
  • Wrapping, shrinking & merging cells & using indents
  • The format painter & cell styles / applying borders

 Introduction to Conditional Formatting 

  • Highlight cell rules & top / bottom rules
  • Data bars / colour scales / icon sets

 Working with Data

  • Sorting Data
  • Filtering Data

Copying and Moving Data

  • Copy & paste /cut & paste
  • Working with data series
  • Drag & drop / undo & redo / find & replace

 Printing

  • Accessing print options & choosing what to print
  • Previewing and printing
  • Page Setup / Orientation / Scaling / Margins

COURSE OUTLINE

Helpful if you already use Excel but want to be able to do more with it.

Exploring Excel

  • Customising the ribbon
  • Using templates

Basic Formulas

  • Adding, subtracting, multiplying & dividing

Basic Functions

  • Autosum, average, count, max & min

Managing Workbooks

  • Formatting & worksheet techniques
  • Page set-up and print options

Working with Data

  • Sorting (2 or more fields) / custom sorts
  • Auto & Advanced filtering
  • Filling data quickly

Summarising Data

  • Using subtotal
  • Using 3D formulas
  • Using the consolidate function

Tables

  • Creating and editing tables

Data Validation

  • Drop down menus
  • Basic data validation & error messages

Absolute cell referencing & named cells

  • Absolute
  • Named cells

Introduction to the IF Function

  • IF Statements
  • Introduction to the IFERROR function

Conditional Formatting

  • Basic conditional formatting

Basic Pivot Tables

  • Introduction

 

COURSE OUTLINE

Helpful if you are an experienced user but want to know more about formulas and functions for data analysis.

Summarising Data

  • Using subtotals
  • Using 3D formulas
  • Using the consolidate function

 Absolute & Mixed Cell Referencing

  • Using the $ for cell or range of cells

Formulas & Functions

Logical Functions

  • IF, Nested IF, IFS
  • Switch function
  • IF Error
  • Using AND / OR within functions
  • CountIF/SumIF/AverageIF
  • SumIFS/CountIFS

Lookup Functions

  • Review of V & H Lookups
  • XLookup

Date & Time Functions

  • Workday & Networkday functions
  • Year & Year Frac functions

Advanced Conditional Formatting

  • New rule with AND / OR / IF functions
  • Highlighting entire rows
  • Using dropdown lists

 Working with Pivot Tables

Analysing Data

  • Creating a pivot table & pivot field
  • Summary functions
  • Sorting data & calculated fields
  • Grouping & ungrouping pivot table data
  • Creating a pivot table chart
  • Slicers in pivot tables
  • Summarising multiple worksheets with different data