Select Page

Microsoft Excel

These coaching sessions are designed to be hands on, practical, relevant and specific to each person and their particular requirements.

Premium Skill sheets

Professional Coaches

Personalized learning

Regularly updated content

Over Topics to choose from

Learn at your own pace

Online Delivery

Tests and quizzes

 Free Support after course

Certificate on Completion

Live online sessions are virtual instructor-led sessions conducted online from your location.  You will be sent an invitation to join the session. A computer with an internet connection and a browser.

You will have an opportunity to have your own virtual classroom displayed as comfort of own home or at work.

You will be sent an invitation to join the session online.

Entry requirements

Learners must have basic literacy and numeracy skills.

Computer requirements

Learners will need access to a computer and the internet.

Minimum specifications for the computer are:

Windows
  • Microsoft Windows XP, or later
  • Modern and up to date Browser (Microsoft edge , Firefox, Chrome, Safari)

MAC/iOS

  • OSX/iOS 6 or later
  • Modern and up to date Browser (Firefox, Chrome, Safari)

All systems

  • Internet bandwidth of 1Mb or faster

Learners will also need access the following applications

  • Adobe Acrobat Reader

 

Business Outcomes Covered in these courses

  • Microsoft 365
  • Charts
  • Data Anaylsis
  • Formulas and Functions
  • Create Professional Reports and Dashboards

To inquire about Live Online Courses, please Call +61 2 6243 4862

Excel

has numerous tools to manage data, present data in charts, aggregate data using PivotTables and automation tools..

Courses 

Introduction Topics

Excel Introduction

Learning Outcomes:

  • Getting to Know Microsoft Excel
  • Creating a New Workbook
  • Working With Workbooks
  • Selecting Ranges
  • Copying Data
  • Filling Data
  • Moving Data
  • Special Pasting
  • Formulas and Functions
  • Formula Referencing
  • Font Formatting
  • Cell Alignment
  • Row and Column Formatting
  • Number Formatting
  • Printing
  • Creating Charts
 

Topics will be selected from the following list

Getting to Know Microsoft Excel
Starting Microsoft Excel
The Excel Screen
How Microsoft Excel Works
Using the Ribbon
Using Ribbon Key Tips
Minimising the Ribbon
Understanding the Backstage View
Accessing the Backstage View
Using Short Cut Menus
Understanding Dialog Boxes
Launching Dialog Boxes
Understanding the Quick Access Toolbar
Adding Commands to the QAT
Understanding the Status Bar
Exiting Safely From Microsoft Excel

Creating a New Workbook
Understanding Workbooks
Creating a New Workbook
Typing Text
Typing Numbers
Typing Dates
Typing Formulas
Saving a New Workbook
Easy Formulas
Checking the Spelling
Making Basic Changes
Printing a Worksheet
Safely Closing a Workbook

Working With Workbooks
Opening an Existing Workbook
Navigating a Workbook
Navigating Using the Keyboard
Using Go To
Practice Exercise
Practice Exercise
The Open Dialog Box
Editing in a Workbook
Understanding Data Editing
Overwriting Cell Contents
Editing Longer Cells
Editing Formulas
Clearing Cells
Deleting Data in a Cell
Using Undo and Redo

Selecting Ranges
Understanding Cells and Ranges
Selecting Contiguous Ranges
Selecting Non Contiguous Ranges
Using Special Selection Techniques
Selecting Larger Ranges
Selecting Rows
Selecting Columns
Viewing Range Calculations
Creating an Input Range

Copying Data
Understanding Copying in Excel
Using Fill for Quick Copying
Copying From One Cell to Another
Copying From One Cell to a Range
Copying From One Range to Another
Copying Relative Formulas
Copying to a Non-Contiguous Range
Copying to Another Worksheet
Copying to Another Workbook

Filling Data
Understanding Filling
Filling a Series
Filling a Growth Series
Filling a Series Backwards
Filling Using Options
Creating a Custom Fill List
Modifying a Custom Fill List
Deleting a Custom Fill List

Moving Data
Understanding Moving in Excel
Moving Cells and Ranges
Moving Data to Other Worksheets
Moving Data to Other Workbooks

 

 

Special Pasting
Understanding Pasting Options
Pasting Formulas
Pasting Values
Pasting Without Borders
Pasting as a Link
Pasting as a Picture
Transposing Ranges
Copying Comments
Copying Validations
Copying Column Widths
Performing Arithmetic With Paste Special
Copying Formats With Paste Special
The Paste Special Dialog Box

Formulas and Functions
Understanding Formulas
Creating Formulas That Add
Creating Formulas That Subtract
Formulas That Multiply and Divide
Understanding Functions
Using the Sum Function to Add
Summing Non-Contiguous Ranges
Calculating an Average
Finding a Maximum Value
Finding a Minimum Value
More Complex Formulas
What if Formulas
Common Error Messages

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

 

 

Font Formatting
Understanding Font Formatting
Working With Live Preview
Changing Fonts
Changing Font Size
Growing and Shrinking Fonts
Making Cells Bold
Italicising Text
Underlining Text
Changing Font Colours
Changing Background Colours
Using the Format Painter
Applying Strikethrough
Subscripting Text
Superscripting Text

Cell Alignment
Understanding Cell Alignment
Aligning Right
Aligning to the Centre
Aligning Left
Aligning Top
Aligning Bottom
Aligning to the Middle
Rotating Text
Indenting Cells
Wrapping and Merging Text
Merging and Centring
Merging Cells
Unmerging Cells

Row and Column Formatting
Approximating Column Widths
Setting Precise Columns Widths
Setting the Default Column Width
Approximating Row Height
Setting Precise Row Heights
Hiding Rows and Columns
Unhiding Rows and Columns

Number Formatting
Understanding Number Formatting
Applying General Formatting
Formatting as Currency
Formatting Percentages
Formatting as Fractions
Formatting as Dates
Using the Thousands Separator
Increasing and Decreasing Decimals

Printing
Understanding Printing
Previewing Before You Print
Selecting a Printer
Printing a Range
Printing an Entire Workbook
Specifying the Number of Copies
The Print Options

Creating Charts
Understanding the Charting Process
Choosing the Chart Type
Creating a New Chart
Working With an Embedded Chart
Resizing a Chart
Dragging a Chart
Printing an Embedded Chart
Creating a Chart Sheet
Changing the Chart Type
Changing the Chart Layout
Changing the Chart Style
Printing a Chart Sheet
Embedding a Chart Into a Worksheet
Deleting a Chart

 

Excel Online

Learning Outcomes:

  • understand what Office Online is and how to access it
  • understand and access Office Online to create, save and edit common Microsoft Office documents
  • understand Excel Online and how it can be used
  • work with more advanced features of Excel Online
 

Topics will be selected from the following list

Introduction to Microsoft Online

  • Working in the Cloud
  • Understanding Microsoft Online
  • Office Online and Microsoft 365
  • Accessing Microsoft Online From the Home Page

Starting With Microsoft Online

  • Accessing OneDrive
  • The OneDrive Screen
  • Uploading Files
  • Opening Files From OneDrive
  • Editing OneDrive Files
  • Deleting Files
  • Creating a New Document
  • The Office Online Screen
  • Understanding the Backstage
  • Saving a Document
  • Reading View vs Editing View
  • Opening an Existing Document
  • Working Collaboratively
  • Accessing Desktop Version in Office Online

Starting With Excel Online

  • The Excel Online Screen
  • Selecting in Excel Online
  • Applying Basic Font Formatting
  • Applying Alignment
  • Applying Number Formatting
  • Understanding Data Editing
  • Inserting and Deleting Cells
  • Understanding Formulas
  • Editing Formulas in a Workbook
  • Working With Excel Online
  • Inserting Tables
  • Inserting Charts
  • Working With Chart Labels and Axes
  • Creating Surveys
  • Sharing Surveys
  • Editing Surveys
  • Inserting Comments
  • Working With Comments

 

 

Fast Tips and Tricks

Learning Outcomes:

  • AutoCalculate
  • AutoFit
  • AutoFill & FlashFill
  • Custom Fill lists
  • Ctrl * to select regions
  • Freeze windows
  • Drag & drop to copy cells & sheets
  • Group sheets
  • Format Painter
  • Conditional formatting
 

 

Formulas for everyday Calculations

Learning Outcomes:

  • Formulas
  • Copy formulas with AutoFill
  • Functions in Excel
  • SUM Function AutoSum
  • AVERAGE Function
  • MAX Function
  • Absolute cell references
  • Creating formulas that reference cells in other worksheets
  • Linking workbooks
 

Topics will be selected from the following list

Filling Data and Copy Formulas

  • Filling Data and Copy Formulas
  • Understanding Filling
  • Filling a Series
  • Filling a Growth Series
  • Filling a Series Backwards
  • Filling Using Options
  • Creating a Custom Fill List
  • Modifying a Custom Fill List
  • Deleting a Custom Fill List
  • Extracting With Flash Fill
  • More Complex Flash Fill Extractions

Formulas and Fucntions

  • Formulas and Functions
  • Understanding Formulas
  • Creating Formulas That Add
  • Creating Formulas That Subtract
  • Formulas That Multiply and Divide
  • Understanding Functions
  • Using the SUM Function to Add
  • Summing Non-Contiguous Ranges
  • Calculating an Average
  • Finding a Maximum Value
  • Finding a Minimum Value
  • Creating More Complex Formulas
  • What if Formulas
  • Common Error Messages

Formula Referencing

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

Quick Viewing and Printing Tips

Learning Outcomes:

  • Worksheet views
  • Freezing windows
  • Page setup
  • Headers and footers
  • Printing options
  • Magnification
  • Group and Outline
 

 

Collaboration and Shared Workbooks

Learning Outcomes:

  • Protecting data
  • Sharing a workbook via a network
  • Sharing a workbook on OneDrive
  • Using Track Changes to review shared edits
 

Topics will be selected from the following list

Protecting Data

  • Understanding Data Protection
  • Providing Total Access to Cells
  • Protecting a Worksheet
  • Working With a Protected Worksheet
  • Disabling Worksheet Protection
  • Providing Restricted Access to Cells
  • Password Protecting a Workbook
  • Opening a Password Protected Workbook
  • Removing a Password From a Workbook

 

Sharing Workbooks

  • Sharing Workbooks via the Network
  • Sharing Workbooks via OneDrive
  • Saving to OneDrive
  • Sharing Workbooks
  • Opening Shared Workbooks
  • Enabling Tracked Changes
  • Accepting or Rejecting Changes
  • Disabling Tracked Changes
  • Adding Worksheet Comments
  • Navigating Worksheet Comments
  • Editing Worksheet Comments
  • Deleting Comments

Track Changes

  • Enabling the TrackChanges in Excel Feature
  • How do ‘Track Changes’ work in Excel?
  • Getting a List of All the Changes
  • Accepting/Rejecting Changes
  • Disabling Track Changes in Excel
  • Difference Between Comments and Track Changes

Intermediate Topics

Excel Intermediate

Learning Outcomes:

  • Formula Referencing
  • Formula Techniques
  • Logical Functions
  • Number Formatting Techniques
  • Conditional Formatting
  • Applying Borders
  • Working With a Worksheet
  • Worksheet Techniques
  • Finding and Replacing
  • Page Setup
  • Sorting Data
  • Filtering Data
  • Charting Techniques
  • Chart Text Formatting
 

Topics will be selected from the following list

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

Formula Techniques
Scoping a Formula
Developing a Nested Function
Creating Nested Functions
Editing Nested Functions
Copying Nested Functions
Using Concatenation
Switching to Manual Recalculation
Forcing a Recalculation
Pasting Values From Formulas

Logical Functions
Understanding Logical Functions
Using IF to Display Text
Using IF to Calculate Values
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT

Number Formatting Techniques
Using Alternate Currencies
Formatting Dates
Formatting Time
Creating Custom Formats

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

Applying Borders
Understanding Borders
Applying a Border to a Cell
Applying a Border to a Range
Applying a Bottom Border
Applying Top and Bottom Borders
Removing Borders
The More Borders Options
Using the More Borders Option

 

Working With a Worksheet
Understanding Worksheets
Changing Worksheet Views
Worksheet Zooming
Viewing the Formula Bar
Viewing the Gridlines
Viewing the Ruler
Inserting Cells
Deleting Cells
Inserting Columns
Inserting Rows
Deleting Rows and Columns
Switching Between Worksheets

Worksheet Techniques
Inserting and Deleting Worksheets
Copying a Worksheet
Renaming a Worksheet
Moving a Worksheet
Hiding a Worksheet
Unhiding a Worksheet
Copying a Worksheet to Another Workbook
Moving a Worksheet to Another Workbook
Changing Worksheet Tab Colours
Grouping Worksheets
Hiding Rows and Columns
Unhiding Rows and Columns
Freezing Rows and Columns
Splitting Windows

 

Finding and Replacing
Understanding Find and Replace Operations
Finding Text
Finding Cell References in Formulas
Replacing Values
Using Replace to Change Formulas
Replacing Within a Range
Finding Formats
Finding Constants Using Go to Special
Finding Formulas Using Go to Special
Finding the Current Region
Finding the Last Cell

Page Setup
Understanding Page Layout
Using Built in Margins
Setting Custom Margins
Changing Margins by Dragging
Centring on a Page
Changing Orientation
Specifying the Paper Size
Setting the Print Area
Clearing the Print Area
Inserting Page Breaks
Using Page Break Preview
Removing Page Breaks
Setting a Background
Clearing the Background
Settings Rows as Repeating Print Titles
Clearing Print Titles
Printing Gridlines
Printing Headings
Scaling to a Percentage
Fit to a Specific Number of Pages
Strategies for Printing Larger Worksheets

Sorting Data
Understanding Lists
Performing an Alphabetical Sort
Performing a Numerical Sort
Sorting on More Than One Column
Sorting Numbered Lists
Sorting by Rows

Filtering Data
Understanding Filtering
Applying and Using a Filter
Clearing a Filter
Creating Compound Filters
Multiple Value Filters
Creating Custom Filters
Using Wildcards

Charting Techniques
Understanding Chart Layout Elements
Adding a Chart Title
Adding Axes Titles
Positioning the Legend
Showing Data Labels
Showing a Data Table
Modifying the Axes
Showing Gridlines
Formatting the Plot Area
Adding a Trendline
Adding Error Bars
Adding a Text Box to a Chart
Drawing Shapes in a Chart

 

 

Graphical Analysis and Charts

Learning Outcomes:

  • Understanding chart types
  • Creating a chart
  • Editing a chart
  • Formatting chart elements
  • Chart Object Formatting
  • Understanding Power Maps
 

Topics will be selected from the following list

Creating Charts

  • Understanding the Charting Process
  • Choosing the Right Chart
  • Using a Recommended Chart
  • Creating a New Chart From Scratch
  • Working With an Embedded Chart
  • Resizing a Chart
  • Repositioning a Chart
  • Printing an Embedded Chart
  • Creating a Chart Sheet
  • Changing the Chart Type
  • Changing the Chart Layout
  • Changing the Chart Style
  • Printing a Chart Sheet
  • Embedding a Chart Into a Worksheet
  • Deleting a Chart

Power Maps

  • Creating a Power Map Tour
  • Working With Location Fields
  • Working With Mapping Confidence
  • Working With Height and Category Fields
  • Filtering Data
  • Navigating a Data Map
  • Changing the Look
  • Working With Layers
  • Working With Scenes
  • Working With Scene Options
  • Working With Time Settings
  • Viewing and Editing a Tour

Chart Elements

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

Chart Object Formatting

  • Understanding Chart Formatting
  • Selecting Chart Objects
  • Using Shape Styles
  • Changing Column Colour Schemes
  • Changing the Colour of a Series
  • Changing Line Chart Colours
  • Using Shape Effects
  • Colouring the Chart Background
  • Understanding the Format Pane
  • Using the Format Pane
  • Exploding Pie Slices
  • Changing Individual Bar Colours
  • Formatting Text
    Formatting With WordArt
  • Changing WordArt Fill
  • Changing WordArt Effects

Chart Types

  • Creating a Column Chart
  • Creating a Line Chart
  • Creating a Pie Chart
  • Creating a Bar Chart
  • Creating an Area Chart
  • Creating a Scatter Chart
  • Understanding Other Chart Types

Databases and Lists

Learning Outcomes:

  • Introduction to Database Tables
  • Rules for databases
  • Sorting data
  • Sorting by multiple columns
  • Filtering a list
  • Automatic Subtotals
  • Importing data
  • Cleaning up imported data
  • Parsing data into columns
  • Removing duplicates
 

Topics will be selected from the following list

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

Sorting Data

  • Understanding Lists
  • Performing an Alphabetical Sort
  • Performing a Numerical Sort
  • Sorting on More Than One Column
  • Sorting Numbered Lists
  • Sorting by Rows

Filtering Data

  • Understanding Filtering
  • Applying and Using a Filter
  • Clearing a Filter
  • Creating Compound Filters
  • Multiple Value Filters
  • Creating Custom Filters
  • Using Wildcards

Importing and Exporting

  • Understanding Data Importing
  • Importing From an Earlier Version
  • Understanding Text File Formats
  • Importing Tab Delimited Text
  • Importing Comma Delimited Text
  • Importing Space Delimited Text
  • Importing Access Data
  • Working With Connected Data
  • Unlinking Connections

Subtotalling

  • Creating Subtotals
  • Using a Subtotalled Worksheet
  • Creating Nested Subtotals
  • Copying Subtotals
  • Using Subtotals With AutoFilter
  • Creating Relative Names for Subtotals
  • Using Relative Names for Subtotals

 

Logical Functions and Conditional Formatting

 

Learning Outcomes:

  • Introduction to Database Tables
  • Rules for databases
  • Sorting data
  • Sorting by multiple columns
  • Filtering a list
  • Automatic Subtotals
  • Importing data
  • Cleaning up imported data
  • Parsing data into columns
  • Removing duplicates
 

Topics will be selected from the following list

 

Logical Functions

Understanding Logical Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT
NestedIFs

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

Text Functions

Learning Outcomes:

  • Using PROPER
  • Using UPPER
  • Using LOWER
  • Using CONCATENATE
  • Using LEFT
  • Using RIGHT
  • Using MID
  • Using LEN
  • Using SUBSTITUTE
  • Using TEXT
  • Using VALUE
 

 

Advanced Topics

Excel Advanced

Learning Outcomes:

  • Setting Excel Options
  • Chart Object Formatting
  • Labels and Names
  • Protecting Data
  • Summarising and Subtotalling
  • Data Linking
  • Data Consolidation
  • Pivot Tables
  • PivotTable Techniques
  • PivotCharts
  • Goal Seeking
  • Grouping and Outlining
  • Solver
  • Recorded Macros
  • Recorder Workshop
 

Topics will be selected from the following list

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

Chart Object Formatting
Understanding Chart Object Formatting
Selecting Chart Elements
Using Shape Styles to Format Objects
Changing Column Colour
Changing Pie Slice Colour
Changing Bar Colours
Changing Chart Line Colours
Using Shape Effects
Filling the Chart Area and the Plot Area
The Format Dialog Box
Using the Format Dialog Box
Using Themes

Labels and Names
Understanding Labels and Names
Creating Names Using Text Labels
Using Names in New Formulas
Applying Names to Existing Formulas
Creating Names Using the Name Box
Using Names to Select Ranges
Pasting Names Into Formulas
Creating Names for Constants
Creating Names From a Selection
Scoping Names to the Worksheet
Using the Name Manager
Documenting Range Names

Protecting Data
Understanding Data Protection
Providing Total Access to Cells
Protecting a Worksheet
Working With a Protected Worksheet
Disabling Worksheet Protection
Providing Restricted Access to Cells
Password Protecting a Workbook
Opening a Password Protected Workbook
Removing a Password From a Workbook

 

Data Linking
Understanding Data Linking
Linking Between Worksheets
Linking Between Workbooks
Updating Links Between Workbooks

Data Consolidation
Understanding Data Consolidation
Consolidating With Identical Layouts
Creating an Outlined Consolidation
Consolidating With Different Layouts
Consolidating Data Using 3D SUM Functions

Pivot Tables
Understanding Pivot Tables
Creating a PivotTable Shell
Dropping Fields Into a PivotTable
Filtering a PivotTable Report
Clearing a Report Filter
Switching PivotTable Labels
Formatting a PivotTable Report
Understanding Slicers
Creating Slicers
Summarising and Subtotalling
Creating Subtotals
Using a Subtotalled Worksheet
Creating Nested Subtotals
Copying Subtotals
Using Subtotals With AutoFilter
Creating Relative Names for Subtotals
Using Relative Names for Subtotals

PivotTable Techniques
Using Compound Fields
Counting in a PivotTable Report
Formatting PivotTable Report Values
Working With PivotTable Grand Totals
Working With PivotTable Subtotals
Finding the Percentage of Total
Finding the Difference From
Grouping in PivotTable Reports
Creating Running Totals
Creating Calculated Fields
Providing Custom Names
Creating Calculated Items
PivotTable Options
Sorting in a PivotTable

PivotCharts
Creating a PivotChart Shell
Dragging Fields for the PivotChart
Changing the PivotChart Type
Using the PivotChart Filter Field Buttons
Moving PivotCharts to Chart Sheets

Goal Seeking
Understanding Goal Seek Components
Using Goal Seek
Practice Exercise
Practice Exercise
Grouping and Outlining
Understanding Grouping and Outlining
Creating an Automatic Outline
Working With an Outline
Creating a Manual Group
Grouping by Columns

Solver
Understanding How Solver Works
Installing the Solver Add-In
Setting Solver Parameters
Adding Solver Constraints
Performing the Solver Operation
Running Solver Reports

Recorded Macros
Understanding Excel Macros
Setting Macro Security
Saving a Document as Macro Enabled
Recording a Simple Macro
Running a Recorded Macro
Relative Cell References
Running a Macro With Relative References
Viewing a Macro
Editing a Macro
Assigning a Macro to the Toolbar
Running a Macro From the Toolbar
Assigning a Macro to the Ribbon
Assigning a Keyboard Shortcut to a Macro
Deleting a Macro
Copying a Macro

Recorder Workshop
Preparing Data for an Application
Recording a Summation Macro
Recording Consolidations
Recording Divisional Macros
Testing Macros
Creating Objects to Run Macros
Assigning a Macro to an Object

 

Mastering LOOKUPs

Learning Outcomes

  • Learn how to create and edit VLOOKUP and other Lookup formulas to extract data from Excel lists and tables based on variable conditions and criteria
  • understand and use formula cell referencing to create more complex formulas
  • use a range of lookup and reference functions
  

Topics will be selected from the following list

Absolute Cell References

  • Using Relative instead of Absolute Cell References
  • Using Absolute Cell References in Formulas

Range Names

  • User Friendly Workbooks
  • Managing Range Names
  • The Name Manager
  • Defining a Name Range
  • Rules for Naming Ranges
  • Naming Ranges With the Name Box
  • Naming Multiple Ranges
  • Selecting Named Cells
  • Formulas with Range Names
  • Deleting Range Names

The LOOKUPs Functions

  • Understanding Data Lookup Functions
  • Using VLOOKUP
  • Using HLOOKUP
  • Using INDEX
  • Using MATCH

 

PivotTables and PivotCharts

Learning Outcomes

  • Understand Pivot Tables
  • Create a Pivot Table
  • Rearrange field items in a Pivot Table
  • Create a Pivot Chart
  

Topics will be selected from the following list

PivotTables

  • Understanding PivotTables
  • Recommended PivotTables
  • 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

PivotTable Features

  • Using Compound Fields
  • Counting in a PivotTable
  • Formatting PivotTable Values
  • Working With PivotTable Grand Totals
  • Working With PivotTable Subtotals
  • Finding the Percentage of Total
  • Finding the Difference From
  • Grouping in PivotTable Reports
  • Creating Running Totals
  • Creating Calculated Fields
  • Providing Custom Names
  • Creating Calculated Items
  • PivotTable Options
  • Sorting in a PivotTable

PivotCharts

  • Inserting a PivotChart
  • Defining the PivotChart Structure
  • Changing the PivotChart Type
  • Using the PivotChart Filter Field Buttons
  • Moving PivotCharts to Chart Sheets

 

Data modelling with Scenarios, Goal Seek and Solver

Learning Outcomes:

  • Performing simple ‘What if’ modelling
  • Using Scenario Manager
  • Using Goal Seek
  • Using Solver
 

Topics will be selected from the following list

Goal Seeking
  • Understanding Goal Seeking
  • Using Goal Seek
Scenarios
  • Understanding Scenarios
  • Creating a Default Scenario
  • Creating Scenarios
  • Using Names in Scenarios
  • Displaying Scenarios
  • Creating a Scenario Summary Report
  • Merging Scenarios
Solver
  • Understanding How Solver Works
  • Installing the Solver Add
  • Setting Solver Parameters
  • Adding Solver Constraints
  • Performing the Solver Operation
  • Running Solver Reports

Automation with Macros

Learning Outcomes:

  • Understand macros
  • Record a simple macro
  • Run a macro
  • Edit a macro in the VBA Editor
 

Topics will be selected from the following list

Recorded Macros

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro With
  • Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to the Toolbar
  • Running a Macro From the Toolbar
  • Assigning a Macro to the Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro

Recorder Workshop

  • Preparing Data for an Application
  • Recording a Summation Macro
  • Recording Consolidations
  • Recording Divisional Macros
  • Testing Macros
  • Creating Objects to Run Macros
  • Assigning a Macro to an Object

Understanding Excel VBA

  • Programming in Microsoft Excel
  • VBA Terminology
  • Understanding Objects
  • Viewing the Excel 2010 Object Model
  • Using the Immediate Window
  • Working With Object Collections
  • Setting Property Values
  • Working With Worksheets
  • Using the Object Browser
  • Programming With the Object Browser

Linking and Consolidation

Learning Outcomes:

  • Data Linking
  • Data Consolidation
  • Pivot Consolidation
 

Topics will be selected from the following list

Data Linking

  • Understanding Data Linking
  • Linking Between Worksheets
  • Linking Between Workbooks
  • Updating Links Between Workbooks

Data Consolidation

  • Understanding Data Consolidation
  • Consolidating With Identical Layouts
  • Creating a Linked Consolidation
  • Consolidating From Different Layouts
  • Consolidating Data Using the SUM Function

Pivot Consolidation

  • Understanding Pivot Consolidation
  • Using Pivot Wizard

Excel Analysis and Dashboards

Data Modelling

  • Starting a dataset in Excel
  • Multiple Tables
  • Data Modelling

Get & Transform

  • Understanding Get & Transform
  • Understanding the Navigator Pane
  • Creating a New Query From a File
  • Creating a New Query From the Web
  • Understanding the Query Editor
  • Displaying the Query Editor
  • Managing Data Columns

Get & Transform (cont’d)

  • Reducing Data Row
  • Adding a Data Column
  • Transforming Data
  • Editing Query Steps
  • Merging Queries
  • Working With Merged Queries
  • Saving and Sharing Queries
  • The Advanced Editor

Power Pivot

  • Understanding Relational Data
  • Common Sense Data Modelling
  • Enabling Power Pivot
  • Connecting to a Data Source
  • Working with The Data Model
  • Working with Data Model Fields
  • Changing A Power Pivot View

Power Pivot (cont’d)

  • Creating A Data Model PivotTable
  • Using Related Power Pivot Fields
  • Creating A Calculated Field
  • Creating A Concatenated Field
  • Formatting Data Model Fields
  • Using Calculated Fields
  • Creating A Timeline
  • Adding Slicers

Great functions for Analysis

  • Understanding Data Lookup Functions
  • Using CHOOSE
  • Using VLOOKUP
  • Using VLOOKUP For Exact Matches
  • Using HLOOKUP
  • Using INDEX
  • Using SUMIF
  • Using SUMIFSUsing SUMPRODUCT

Data Validation

  • Validation Criteria
  • Input Messages & Error Messages
  • Drop-Down Lists
  • Formulas
  • Customised Validation Criteria
  • Creating A Number Range Validation

Data Validation (cont’d)

  • Testing A Validation
  • Creating an Error Message
  • Creating a Drop Down List
  • Using Formulas as Validation Criteria
  • Circling Invalid Data
  • Removing Invalid Circles

Using Sparklines to show trends

  • What is a Sparkline?
  • Types of Sparklines
  • Showing Sparklines only
  • Specifying a Date Axis
  • Hidden Data and Sparklines
  • Sparklines and Targets

Using Conditional formatting

  • Using Conditional Formatting with a Dashboard
  • Top 10 & Custom Formatting
  • Data Bars
  • Show data bars outside the data cell
  • Colour Scales
  • Icon Sets
  • Creating Rules Based Icon Set
  • Removing unnecessary icons
  • Using Symbols in Reporting
  • Using the Camera Tool

Pivot Tables

  • Structure of Pivot Tables
  • Using Compound Fields
  • Counting in A PivotTable
  • Formatting PivotTable Values
  • Working with Grand Total & Subtotals
  • Finding the Percentage of Total
  • Finding the Difference From
  • Grouping in PivotTable Reports
  • Creating Running Totals

Pivot Tables

  • Creating Calculated Fields
  • Providing Custom Names
  • Creating Calculated Items
  • PivotTable Options
  • Sorting in a PivotTable
  • Top and Bottom Views
  • Date Grouping Options
  • Hiding or Showing Data Items
  • Conditional Formatting and Sparklines in Pivot Tables
  • Pivot Caches and File Size

Slicers in Reports

  • What are Slicers?
  • Creating Slicers
  • Using a Slicer on Multiple Pivot Tables
  • Renaming Pivot Tables
  • Timeline Slicer

Trending Charts

  • Why do we use Trending Charts?
  • Appropriate Chart Types for Trending
  • Vertical or Y-Axis Scales
  • Chart Titles linking to a Cell
  • Comparative Trending
  • Labelling
  • Using a Secondary Axis
  • Formatting Key Data Points
  • How to display Actuals and Forecasts
  • Averages and Data Smoothing

Other Report Charts

  • Top and Bottom Charts
  • How to show Top or Bottom in Data Labels
  • Waterfall Charts

Histograms

  • Creating Histograms using Formulas
  • Creating Histograms using Pivot Tables
  • Creating Histograms using Excel’s Statistical Charts

Charting performance against a target

  • Performance against Targets
  • Creating Thermometer Chart
  • Bullet Graph

Defining Dashboards

  • Purpose of a Dashboard
  • Working out what is needed
  • What are the data sources
  • Will the audience need further data to drill-down to?
  • How often will/can the data refresh?
  • Does it need to be maintained?
  • How easy will it be to maintain?

Dashboard Design Principles

  • Thirteen common mistakes in dashboard design

Making an Interface

  • Using Macros with Dashboards
  • Recording a Macro
  • Navigation using Macros
  • Macros to Change Chart types
  • Macros and Pivots

 

Dashboards

Learning Outcomes:

  • Overview of Excel dashboards
  • Why use dashboards
  • Types of dashboards
  • Setting up the dashboard elements
  • Optimising a list
  • Building an analysis sheet
  • Extracting unique values
  • Building dynamic charts
  • Creating a chart
  • Modifying a chart
  • Testing the dashboard
  • Changing variable data and seeing the results
 

 

Power Pivots and View

Learning Outcomes:

  • Connecting to a data source
  • Creating a Power View sheet
  • Editing and filtering views
  • Enabling Power Pivot
  • Working with the data model
  • Creating a Pivot Table
  • Changing Power Pivot view
  • Working with fields
 

 

FAQ

Does the course include hands-on training?

Yes, you can follow the coach as you do the training. The course also includes a quick skillsheets reference guide and quiz for each lesson, to help you cement your knowledge.

What version of software do I need?

The course is compatible with Microsoft 365 (previously known as Office 365) and Excel for Windows 2019, 2016, 2013, 2010. Also available for Mac.

I’m a beginner. Can I take this course?

Yes, you can! This online course starts with the basics and is suitable for beginners. With each lesson, you will build your knowledge, going from beginner to intermediate and advanced lessons. Once enrolled, our friendly support team and instructors are here to help with any course related inquiries.

What if I have some experience with? Is this the right course for me?

Contact our staff and customise the session based on topics you would like to learn.

How long does it take to complete the course?

Each bite-sized lesson consists of a 30min, 45min, 60min, 120min sessions.  This microlearning format makes it easy to fit learning into your busy schedule, so you can even complete a lesson during a lunch break or on your commute. Have a look at our packages www.janszgroup.com.au/digital-coaches

 

Do you have other online courses?

Yes, we do! Here are the other courses we offer:

www.janszgroup.com.au/courses

Do you have any free resources?

You’re in luck! Start learning OVER 100 of the best shortcuts for PC and Mac (free PDF cheat sheet included). And check out our resources for regular posts.

What our learners say!

We removed data duplicates, tidied up % and formulas, overall feeling competent!

SarahTAFE Teacher