Advanced Excel 2003 Package
This package is designed for proficient Excel users who want to improve their productivity in Microsoft Excel 2003, and covers advanced spread sheeting techniques plus an introduction to Visual Basic.
Quick Facts
$990 incl GST
Save $385.00
Download Course Brochures
About the Package
Participants will learn how to create macros, user defined functions, advanced workbook automation and productivity features, understand the Visual Basic environment and use a range of programming techniques to develop automated solutions in Excel 2003.
The Advanced Excel 2003 Package includes the following courses:
Microsoft Excel 2003 Level 3 (1 day)
Microsoft Excel 2003 Visual Basic for Applications (2 days)
Prerequisites
Participants should have extensive experience in designing and creating workbooks and have a good working knowledge of formulas, ranges, functions and multiple workbooks. Participants should also follow the sequence of the courses listed above. More details on prerequisites can be found within individual course descriptions.
Subject to prerequisites being met, one or more participants can book into a package. Review training package conditions .
Courses Units: Microsoft Excel 2003 Level 3
- Unit 1: Nesting Functions Workshop
- - Scoping a Formula
- - Developing a Nested Function
- - Creating a Nested Function
- - Editing a Formula with Nested Functions
- - Copying a Formula with Nested Functions
- - Concatenation
|
- Unit 2: Advanced Filters
- - Advanced Filter Concepts
- - Using an Advanced Filter
- - Extracting Records with Advanced Filter
- - Using Formulas in Criteria
- - Using Database Functions
|
- Unit 3: Validations
- - Validation Techniques
- - Data Validation By Number Range
- - Testing Data Validation
- - Input Messages
- - Creating Error Alerts
- - Creating Drop-Down Lists
- - Using Formulas As Validation Criteria
- - Creating Custom Validation Criteria
- - Number Formats with Built-in Logic
- - Conditional Formatting
- - Copying Data Validation Settings
|
- Unit 4: Goal Seeking
- - Goal Seek Components
- - Using Goal Seeking
|
- Unit 5: Scenarios
- - Creating a Default Scenario
- - Creating Scenarios
- - Using Names in Scenarios
- - Displaying Scenarios
- - Creating a Scenario Summary Report
- - Merging Scenarios
|
- Unit 6: Summarising Data
- - Creating Subtotals
- - Using a Subtotaled Worksheet
- - Creating Nested Subtotals
- - Copying Subtotals
- - Using Subtotals with AutoFilter
- - Installing the Conditional Sum Wizard
- - Using the Conditional Sum Wizard
- - Creating Relative Names for Subtotals
- - Using Relative Names for Subtotals
|
- Unit 7: PivotTables
- - PivotTable Theory
- - Creating a Simple PivotTable
- - Adding a Row Field to a PivotTable
- - Using the Page Field in a PivotTable
- - Filtering Row And Column Values
- - Formatting a PivotTable
- - Counting with PivotTables
- - PivotTable Summary And Display Options
- - Show Data As Percentages in PivotTables
- - Calculated Fields in PivotTables
- - Calculated Items in PivotTables
- - Creating a PivotChart
- - Modifying a PivotChart Via the PivotTable
|
- Unit 8: Linking Workbooks
- - Linking Data in Excel
- - Linking Within a Workbook
- - Linking Between Workbooks
- - Updating Links Between Workbooks
|
- Unit 9: Consolidation
- - Consolidating Data with Identical Layouts
- - Consolidating Data with Different Layouts
- - Consolidating Data Using a PivotTable
|
- Unit 10: Macro Virus Control
- - Opening Workbooks with Macros
- - Setting Macro Security Levels
- - Trusting Publishers
- - Creating a Digital Signature
- - Removing a Trusted Publisher
|
- Unit 11: Recorded Macros
- - Setting Macro Security Levels
- - Recording a Simple Macro
- - Running a Recorded Macro
- - Relative Cell References
- - Running a Macro with Relative References
- - Viewing a Macro Module
- - Modifying a Recorded Macro
|
- Appendix 1: Data Tables
- - Using a Simple what-If Model
- - Creating a One-Variable Table
- - Using One-Variable Data Tables
- - Creating a Two-Variable Data Table
|
- Appendix 2: Excel on the Web
- - Previewing Workbooks As Web Pages
- - Publishing a Static Worksheet
- - Adding to an Existing Web Page
- - Publishing an Interactive Web Page
|
- Appendix 3: Recorder Workshop
- - Preparing Data
- - Recording Summation Macros
- - Recording Consolidations
- - Recording Divisional Macros
- - Testing Macros
- - Creating Objects to Run Macros
- - Assigning a Macro to an Object
|
Courses Units: Microsoft Excel 2003 Visual Basic for Applications
- Unit 1: Recorded Macros
- - Setting Macro Security Levels
- - Recording a Simple Macro
- - Running a Recorded Macro
- - Relative Cell References
- - Running a Macro with Relative References
- - Viewing a Macro Module
- - Modifying a Recorder Macro
|
- Unit 2: Recorder Workshop
- - Preparing Data for an Application
- - Recording a Summation Macro
- - Recording Consolidations
- - Recording Divisional Macros
- - Testing Macros
- - Creating Objects to run Macros
- - Assigning a Macro to an Object
|
- Unit 3: User-Defined Functions
- - User-Defined Functions Theory
- - Creating Simple User-Defined Functions
- - Using User-Defined Functions
- - Using Multiple Arguments
- - Modifying a Function
- - Creating a Function Library
|
- Unit 4: Using the VBA Editor
- - Opening and closing the Editor
- - Working with the Project Explorer
- - Working with the Properties Window
- - Working with the Work Area
- - Working with the Code Module
- - Running Procedures from the Editor
- - Setting Breakpoints in the Editor
- - Stepping through a Procedure
|
- Unit 5: Understanding VBA
- - VBA terminology
- - Accessing the Excel Object Model
- - Using the Immediate Window
- - Working with Object Collections
- - Setting Property Values
- - Working with Object Collections
- - Setting Property Values
- - Working with Worksheets
- - Using the Object Browser
- - Programming with the Object Browser
|
- Unit 6: Procedures
- - Creating a Command Procedure
- - Making sense of IntelliSense
- - Using the edit toolbar
- - Commenting Statements
- - Indenting Code
- - Bookmarking in Procedures
|
- Unit 7: Using Variables
- - Creating and Using Variables
- - Implicit and Explicit Declarations
- - The Scope of Variables
- - Procedure Level Scoping
- - Module Level Scoping
- - Passing Variables by Reference
- - Passing Variables by Value
- - Data Types
- - Declaring Data Types
- - Using Arrays
|
- Unit 8: Using Excel Objects
- - Applications Methods and Properties
- - Workbook Methods and Properties
- - Viewing Excel and the Editor together
- - Using Workbook Objects
- - Range Methods and Properties
- - Using Range Objects
- - Project - Using Objects in a Procedure
|
- Unit 9: Programming Techniques
- - The MsgBox Function
- - Sending Messages to the User
- - InputBox Techniques
- - Using the InputBox Function
- - Using the InputBox Method
- - The IF statement
- - Using IF for Single Conditions
- - Using IF for Multiple Conditions
- - The FOR NEXT Statement
- - Looping with known or specified Iterations
- - The DO LOOP Statement
- - Looping with Unknown Iterations
|
- Unit 10: Creating Custom Forms
- - Creating a Custom Form
- - Adding Text Boxes to a Form
- - Changing Text Box Control Properties
- - Adding Label Controls to a Form
- - Adding a Combo Box Control
- - Adding Option Buttons
- - Adding Command Buttons
- - Running a Custom Form
|
- Unit 11: Programming Custom Forms
- - Initialising a Form
- - Closing a Form
- - Project - Transferring Data From a Form
- - Running Form Procedures
- - Creating Error Checking Procedures
- - Running a Form from a Procedure
|
- Unit 12: Custom Menus
- - Creating a New Menu
- - Creating a Cascade Menu
- - Adding Menu Commands
- - Assigning Macros to Menu Commands
- - Removing a Menu
|
- Unit 13: Automatic Startup
- - Programming Automatic Procedures
- - Running Automatic Procedures
- - Automatically Starting a Workbook
|
Upcoming Dates
You’ll need to check the dates for each of the individual courses within this Package.
Check our Course Dates, or contact us to discuss your specific needs.
$990 incl GST
Save $385.00
