EXCEL ADVANCED

Course Overview

This is a one-day training course tailored to give intermediate Excel users a complete command of Microsoft Excel. Led by dedicated instructors who are fully versed in the Microsoft Office package, you’ll build upon your existing skills and embrace the more complex and technical aspects of Excel. We’ll teach you how to summarise large datasets quickly and confidently using PivotTables, protect data, validate data entry, audit formulas, implement logical functions, nest functions, utilise analytical tools such as Goal Seek and Data tables, create and manage templates as well as automating repetitive tasks using Macros and much more.

Raising the Bar sets a new industry standard for training courses. Our Excel training is delivered face-to-face by industry leading experts who use clear demonstrations and easy to follow instructions to help lead students to success. Our class sizes are small so that all students receive the required attention and achieve the best possible learning outcomes. We also supply all the equipment you need, so you can show up on the day without having to bring a thing. You’ll learn through clear hands-on activities and leave with brand new skills and knowledge of Excel that far exceeds your expectations.

At the end of the Excel Advanced course, you’ll receive a certificate of completion, a detailed course manual to work through at your own pace and the proficiency required to use Excel’s more complex functions.

Who Should Attend

The Excel Advanced Training Course is designed especially for students who already have an intermediate knowledge of Excel and want to expand their skills beyond creating and formatting basic spreadsheets. This course is perfect for users who want to gain confidence and acquire an advanced level knowledge of Excel. It’s also great for companies looking to master spreadsheet creation and streamline services.

Typically, students who attend this course already have a good level of confidence with Excel and know how to create professional and productive spreadsheets. This course is right for you if you’re a relatively experienced Excel user who wants to learn how to report data using PivotTables and learn advanced formula creation, data protection, formula auditing and automation through the use of Macros.

Raising the Bar’s public training courses are available throughout the year in our Sydney CBD training centre. For students in other cities, training sessions are available by appointment for companies and organisations in Sydney, Melbourne, Adelaide, Canberra, Brisbane and Perth.

Course Prerequisites

Our advanced course is designed for experienced Microsoft Excel users. Students should have completed our Excel Intermediate course or have equivalent skills creating and formatting spreadsheets at an intermediate level. You will also need general computer skills using the Windows operating system.

Raising the Bar

1300 937 782

www.rtb.com.au

CREATING A PIVOTTABLE

Creating A PivotTable

Create A PivotTable

Updating A PivotTable

Update The Information In A PivotTable
Adding Multiple Fields To The Row Area
Adding Multiple Fields To The Column Area
Arrange PivotTable Information
Remove A PivotTable Field
Filter A PivotTable
Remove A Filter
Filter A PivotTable Using A Filters Field
Clear the Filter
Change The Values Field Calculation
Format Data In A PivotTable
Managing Totals

PivotTables in Excel

Inserting A Recommended PivotTable
Filter Records Using Slicers
Selection Multiple Filter Criteria
Clear The Slicer Filter
Remove A Slicer

ADVANCED PIVOTTABLE FEATURES

Working With Calculated Fields In A PivotTable

Insert A Calculated Field
Working With The Values Field
Delete Calculated Fields
Changing PivotTable Options
Show Report Filter Pages
Show Detailed Data From A PivotTable Value
Show Different Calculations In PivotTable Value Fields

Value Field Calculation Options

Creating A Chart From A PivotTable

Create A PivotChart From A PivotTable

DATA VALIDATION AND PROTECTING DATA

Using Data Validation And Tracing Invalid Data

Validate Data Input
Compose A Data Input Message
Create An Error Alert Message
Circle Invalid Data

Protecting Worksheet Styles, Contents And Elements

Protecting A Worksheet
Unprotecting A Worksheet
Unlock Cells For Editing In A Protected Sheet
Hide A Cell Formula
Set A Password To Edit A Specific Range

Protecting Workbook Contents

Protect And Unprotect The Workbook Structure

Protecting Your Workbook From Unauthorised Users Access

Set A Password To Open A Workbook
Remove A Password From A Workbook
Set A Password To Modify A Workbook

WORKING WITH TEMPLATES

Creating A New Workbook Based On A Template

Create A New Workbook Based On A Template
Create A New Template
Create A New Workbook Based On Your Own Template
Editing A Template

FORMULA AUDITING

Auditing Worksheets And Tracing Cell References

Tracing Precedent Cells
Tracing Dependent Cells
Trace Cells Across Worksheets
Error Checking A Worksheet
Tracing Errors
Set Error Checking Options
Using The Watch Window
Showing and Hiding Formulas
Evaluate A Formula

USING ADVANCED FUNCTIONS

Using Financial Functions

The PMT Function
Nesting the PMT and ABS Functions

Editing Nested Functions

Applying Named Ranges To An Existing Formula

Using Logical Functions

Use A Logical Test
Use The IF Function To Return A Text Message
Use The IF Function To Return A Value
Nesting IF Functions
The OR Function
The AND Function
The COUNTIF Function

ANALYSING DATA

The Goal Seek Tool

Use The Goal Seek Tool

Exploring What-If Tables

Create A One Variable What-If Table
Create A Two Variables What-If Table

Using Scenarios

Create Scenarios
Add a Scenario
Displaying the Various Scenarios

Solver Tool

Load The Solver Tool Add-In
Use The Solver Tool

AUTOMATING ROUTINE TASKS WITH MACROS

Understanding Macros

Types of Macros

Show The Developer Tab
Record A New Macro
Run A Macro
Create A Run Macro Button On The Quick Access Toolbar
Create A Macro Command Button On A Worksheet
Edit The Command Button

Editing A Macro

Edit Macro Coding
Rename A Macro
Delete A Macro

Using A Relative Reference Macro

Record A Relative Reference Macro
Run A Relative Reference Macro
Saving a Macro Workbook
Open a Macro Workbook