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

  • Importing from an earlier version
  • Understanding file formats
  • Importing tab delimited text
  • Importing space delimited text
  • Importing Access data
  • Working with connected data
  • Exporting to Microsoft Word

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
  • Changing the PivotChart type
  • Moving PivotCharts to chart sheets

PivotTable Features

  • Counting in a PivotTable
  • Formatting PivotTable values
  • Working with PivotTable grand totals
  • Finding the percentage of total
  • Finding the difference from
  • Grouping in a PivotTable
  • Creating calculated fields
  • Sorting in a PivotTable

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

  • Preparing a worksheet for controls
  • Adding a combo box control
  • Adding a list box control
  • Adding a scroll bar control
  • Adding a spin button control
  • Adding a group box control
  • Protecting a worksheet with controls

Sharing Workbooks

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

Macros

  • Setting Macro Security
  • Saving a document as Macro enabled
  • Recording a simple macro
  • Viewing a macro
  • Editing a macro
  • Running a macro
  • Delete a macro

Course Inclusions

Skills Analysis

Take advantage of our free online skills analysis prior to your training to ensure you’re enrolled in the right course.

Certificate

After completing the course you’ll receive a certificate to show off your new-found Microsoft Outlook knowledge.

We come to you

To save your business time, money and unnecessary travel, we come to you, delivering our training at your premises.

Hands-on training

We’re big believers in hands-on learning so you’ll be on a computer and building your skills in real-time.

Email Support

Access unlimited support following your training course to build on your learning and ask any burning questions.

Real-world examples

There’s nothing better than working through real-world examples to reinforce learning and increase your confidence.

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.

Enquire Online Now

View other course outlines

Microsoft Excel Courses

Excel Introduction

- 1 day course duration

Microsoft Excel Courses

Excel Intermediate

- 1 day course duration

Call Now Button Scroll to Top