skip to navigation skip to content
Self-taught course

Provided by: University Information Services - Digital Literacy Skills


This course is self taught (Materials may be loaned out).

Bookings cannot be made on this course (Course is not taking bookings).

More information about UIS self-taught courses.

Booking / availability

Microsoft Excel 2016 Level 3 (with Challenge Exercises)

Self-taught course


Microsoft Excel 2016 - Level 3 enables users to protect worksheet data, perform advanced data operations using summarising, data consolidations, filters and PivotTables, macros, and much more.

Target audience

Microsoft Excel 2016 - Level 3 assumes the learner has some knowledge of the software and it would help if level 1 & 2 have been completed first.

  • Completion of Microsoft Excel 2016 - Level 1 & 2.
  • It would also be beneficial to have a general understanding of personal computers and the operating system environment, especially in regard to working with files and folders.
Topics covered

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
  • Practice Exercise
  • Practice Exercise Sample

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
  • Practice Exercise
  • Practice Exercise Sample

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
  • Exporting To Microsoft Word
  • Exporting Data As Text
  • Inserting A Picture
  • Modifying An Inserted Picture
  • Practice Exercise
  • Practice Exercise Sample

Data Linking

  • Understanding Data Linking
  • Linking Between Worksheets
  • Linking Between Workbooks
  • Updating Links Between Workbooks
  • Practice Exercise
  • Practice Exercise Sample

Grouping And Outlining

  • Understanding Grouping And Outlining
  • Creating An Automatic Outline
  • Working With An Outline
  • Creating A Manual Group
  • Grouping By Columns
  • Practice Exercise
  • Practice Exercise Sample

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
  • Practice Exercise
  • Practice Exercise Sample

Data Consolidation

  • Understanding Data Consolidation
  • Consolidating With Identical Layouts
  • Creating A Linked Consolidation
  • Consolidating From Different Layouts
  • Consolidating Data Using The SUM Function
  • Practice Exercise
  • Practice Exercise Sample

Data Tables

  • Understanding Data Tables And What-If Models
  • Using A Simple What-If Model
  • Creating A One-Variable Table
  • Using One-Variable Data Tables
  • Creating A Two-Variable Data Table
  • Practice Exercise
  • Practice Exercise Sample


  • Understanding Scenarios
  • Creating A Default Scenario
  • Creating Scenarios
  • Using Names In Scenarios
  • Displaying Scenarios
  • Creating A Scenario Summary Report
  • Merging Scenarios
  • Practice Exercise
  • Practice Exercise Sample


  • 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
  • Practice Exercise
  • Practice Exercise Sample

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
  • Practice Exercise
  • Practice Exercise Sample


  • Inserting A PivotChart
  • Defining The PivotChart Structure
  • Changing The PivotChart Type
  • Using The PivotChart Filter Field Buttons
  • Moving PivotCharts To Chart Sheets
  • Practice Exercise
  • Practice Exercise Sample

Advanced Filters

  • Understanding Advanced Filtering
  • Using An Advanced Filter
  • Extracting Records With Advanced Filter
  • Using Formulas In Criteria
  • Understanding Database Functions
  • Using Database Functions
  • Using DSUM
  • Using The DMIN Function
  • Using The DMAX Function
  • Using The DCOUNT Function
  • Practice Exercise
  • Practice Exercise Sample

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
  • Practice Exercise
  • Practice Exercise Sample


  • Understanding Types Of Controls
  • Understanding How Controls Work
  • Preparing A Worksheet For Controls
  • Adding A Combo Box Control
  • Changing Control Properties
  • Using The Cell Link To Display The Selection
  • Adding A List Box Control
  • Adding A Scroll Bar Control
  • Adding A Spin Button Control
  • Adding Option Button Controls
  • Adding A Group Box Control
  • Adding A Check Box Control
  • Protecting A Worksheet With Controls
  • Practice Exercise
  • Practice Exercise Sample

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
  • Practice Exercise
  • Practice Exercise Sample

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
  • Practice Exercise
  • Practice Exercise Sample

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

  • Modify Excel options
  • Protect data in worksheets and workbooks
  • Import data into Excel and export data from Excel
  • Use data linking to create more efficient workbooks
  • group cells and use outlines to manipulate the worksheet
  • create summaries in your spreadsheets using subtotals
  • Use the Data Consolidation feature to combine data from several workbooks into one
  • create, use and modify data tables
  • Create and work with scenarios and the Scenario Manager
  • Understand and create simple PivotTables
  • Construct and operate PivotTables using some of the more advanced techniques
  • Create and edit a PivotChart
  • Use advanced filters to analyse data in a list
  • Use a variety of data validation techniques
  • Create and use a range of controls in a worksheet
  • Share workbooks with other users
  • Create recorded macros in Excel

Watsonia workbook with 210 topics. Files for the exercises are provided on a CD or can be downloaded here


Approximately 20 hours

Related courses

Booking / availability