EXCEL ADVANCED 2010

Course Overview

The Excel Advanced course assumes students are confident in working with Excel and the spreadsheet environment. Students will want to learn more functions available to them other than the standard sum function. Typically in their workplace they are required to use Excel a great deal to evaluate, extract and analyse results and desire techniques to automate tasks. They may also have an interest in gaining a basic knowledge of VBA code to understand macros more fully.

As a result of this course students will be able to use a variety of lookup, logical, financial and statistical functions. Analytical features of the Goal Seek, Scenario Manager will be demonstrated, and students will gain exposure to Macros including an introduction to VBA code.

Course Prerequisites

Microsoft Excel Intermediate 2010 or equivalent

Raising the Bar

1300 937 782

www.rtb.com.au

CREATING A PIVOTTABLE IN EXCEL

Creating Excel PivotTables

Creating A PivotTable

Updating An Excel PivotTable

Update A PivotTable
Adding Multiple Fields To The Row / Column Area
Rearrange PivotTable Fields
Removing A PivotTable Field
Filter A PivotTable Field
Remove A Filter
Filter The PivotTable Using The Filters Field
Clearing A Filter
Setting The Field Calculation
Format Calculations In A PivotTable
Managing Subtotals and Grand Totals

More Features In PivotTables

Filter Records Using Slicers
Setting Multiple Filter Criteria
Clearing Filters In The Slicer
Remove A PivotTable Slicer

ADVANCED FEATURES OF PIVOTTABLES

Working With Calculated Fields In A PivotTable

Insert A Calculated Field Into A PivotTable
Moving The Values Field
Remove A Calculated Field
Set PivotTable Options
Show Report Filter Pages For A Field
Show Detailed Information From A PivotTable Result

PivotCharts

Create A PivotChart From A PivotTable

DATA VALIDATION, DATA PROTECTION & WORKING WITH TEMPLATES

Setup Data Validation

Validate Data Entry
Create A Data Validation Input Message
Compose An Error Alert Message
Circle Cells With Invalid Data

Working With Template

Create A New Workbook Based On A Workbook Template
Create A New Excel Template
Create A New Workbook From Your Own Template
Edit Your Own Template

Protecting Worksheets

Protecting Data On A Single Worksheet
Unprotecting A Worksheet For Editing
Unlock Cells For Editing
Hide Formulas In A Cell
Add A Password To Edit A Specific Ranges

Protecting The Workbook Structure

Protect And Unprotect The Workbook Structure

Password Protect The Workbook

Setting A Password To Open A Spreadsheet
Remove A Password From A Spreadsheet
Setting A Password To Modify A Spreadsheet

FORMULA AUDITING IN EXCEL

Auditing Formulas And Tracing Cell References

Tracing Precedents
Tracing Dependents
Tracing Precedents / Dependents Across Worksheets
Error Checking
Trace An Error In A Worksheet
Setting Error Checking Options
The Watch Window
Showing / Hiding Formulas In A Worksheet
Evaluate Formula Steps

ADVANCED FUNCTIONS

The Payment Function

PMT Function (Payment)
Using The ABS Function (Absolute)

Working With Logical Functions

Introducing A Logical Test
Setup An IF Function To Return Text
Setup An IF Function To Return Value
Nesting IF Functions In A Formula
Using The OR Function
Using The AND Function
Count Cells With Specific Criteria Using The COUNTIF Function

WHAT IF ANALYSIS AND ANALYSING DATA

Goal Seek Tool

Use The Goal Seek Tool

Data Tables

Create A One Variable Data Table
Create A Two Variables Data Table

Scenarios

Create Scenarios
Apply A Scenario

Solver Tool

Load The Solver Add-In
Setting Up The Solver Tool
Solver Features

RECORDING MACROS

Recording Macros

Understanding The Two Types of Macros

Recording A New Macro
Run A Recorded Macro
Add A Button To The Quick Access Toolbar
Showing The Developer Tab
Embed A Command Button Into A Worksheet
Editing The Command Button

Editing A Recorded Macro

Unhide the Personal Macro Workbook
Edit The VBA Code In A Macro
Rename A Recorded Macro
Delete A Recorded Macro
Hide the Personal Macro Workbook

Relative Reference Macros

Record A Macro With Relative Cell References
Run The Relative Reference Macro
Saving A Workbook With Macros
Open A Workbook With Macros

INTEGRATING DATA WITH OTHER APPLICATIONS

Importing And Exporting Data

Using The Text Import Wizard

Import Data From A Database

Introducing Microsoft Query

Importing Data From Another Source

Import Information From Other External Sources