Microsoft Excel Advanced

Course Description

Microsoft Excel Advanced is part of a 3-level study program. This course aims to extend participant’s skills and knowledge to protect worksheet data, perform advanced data operations using summarizing, data consolidation, filters and advanced PivotTable techniques, macros and much more.

Learning Outcomes

At the completion of Microsoft Excel Advanced you should be able to:

protect data in worksheets and workbooks
use data linking to create more efficient workbooks
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
create and edit a PivotChart
construct and operate PivotTables using some of the more advanced techniques
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
create recorded macros in Excel

Prerequisites

Microsoft Excel Advanced assumes reasonable knowledge of the software as well as a general understanding of personal computers and the Windows operating system environment.

Course Duration

The Microsoft Excel Advanced course is run over one-day but can be customised to suit your needs.

Course Overview

Protecting Data

Protecting a worksheet
Disabling worksheet protection
Password protecting a workbook
Opening a password protected workbook

Importing and Exporting

Data Linking

Link data between worksheets
Link data between workbooks
Update links between workbooks

Group and Outline

Create an automatic outline
Working with an outline
Creating a manual group
Grouping by columns

Summarising and Subtotaling

Create subtotals
Using a subtotaled worksheet
Copying subtotals
Using subtotals with AutoFilter
Creating relative names for subtotals

Data Consolidation

Consolidating with identical layouts
Creating a linked consolidation
Consolidating from different layouts
Consolidating data using the SUM function

Data Tables

Using a simple What-If model
Creating a one-variable table
Creating a two-variable table

Scenarios

Creating a default scenario
Creating scenarios
Using names in scenarios
Creating a scenario summary report

PivotCharts

Insert a PivotChart
Defining the PivotChart structure
hanging the PivotChart type
Moving PivotCharts to chart sheets

PivotTable Features

Advanced Filters

Using an Advanced Filter
Extracting records with advanced filters
Using formulas in criteria
Using database functions
Using DSUM
Using DMIN and DMAX functions

Validating Data

Creating a number range validation
Testing a validation
Creating an Input message
Creating an Error message
Circling invalid data

Controls

Protecting a worksheet with controls

Sharing Workbooks

Sharing Workbooks
Open shared workbooks
Enabling Tracked Changes
Accepting or rejecting changes
Adding worksheet comments

Recorded Macros

Course Includes

Skills Analysis

Certificate

We come to you

Hands-on training

Email Support

Real-world examples

Course Pricing

Pricing as of 1st July 2019. All prices are exempt of GST. All prices are PER SESSION, not per person.

Number of participants	Investment (per day)
1-on-1 Training	$500.00
2-3 participants	$650.00
4-6 participants	$800.00
7-8 participants	$950.00
9-10 participants	$1,200.00

Above costs are for training conducted within the Sydney region and is subject to travel fees dependent on location. Pricing provides a guide only and will be confirmed at time of booking enquiry.