Computer Training & Development Australia
Home
Training
Services
Course Schedule
Course Outlines
Affiliates
Contact Us

Book a CourseExcel 2002-2003

Advanced Level Course

Description

To assist the user in becoming more proficient in Excel’s advanced data analysis, formatting and handling capabilities.

Learning Outcomes

Upon completion of this course the participant will be able to perform a selection of the tasks listed below:

  • create and manage arrays to speed and simplify calculations
  • establish data tables to speed the calculation of functions where one or two arguments take on a variety of values
  • outline a complex, detailed worksheet to speed, simplify and control the display of differing levels of summary data
  • create custom views to control viewing and printing
  • create and manage scenarios – named collections of values
  • create print reports, collating views, scenarios and/or sheets
  • bring text files into Excel and separate data into distinct columns
  • use Goal Seek and Solver to analyse data
  • utilise a variety of Excel’s statistical analysis features
  • record, edit, debug and comment a basic macro and run it from a menu, hot-key combination, worksheet button or toolbar icon

Pre-requisites

Course Content

Arrays
Creating an Array Formula
Advantages and Disadvantages
Selecting an Array
Editing an Array
Data Tables
One-Input Data Table
Two-Input Data Table
Outlining
Different Ways to Create an Outline
Creating an Automatic Outline
The Parts of an Outline Display
Hiding / Showing Detail
Removing an Outline
Creating an Manual Outline
Adding Automatic Styles
Modifying Existing Styles
Selecting Visible Cells Only
Opening and Updating
Live-Linked Workbook
Displaying Outline Symbols
Changing the Default Settings
Custom Views
Adding a Custom View
Showing a Custom View
Deleting Custom Views
Scenario Manager
Creating a Scenario
Deleting a Scenario
Showing a Scenario
Editing a Scenario
Hiding and/or Preventing Changes
to Scenarios
Re-Displaying a Hidden Scenario
Summary Reports
Lists
Guidelines for Creating a List
Using Data, Form to Add Items to a List
Sorting a List Via the Menu
Sorting a List Via the Icons
Performing Sorts within Sorts
AutoFilter
Unfiltering a List
Setting Custom Criteria with AutoFilter
Using the =SUM() Function in a Filtered List
Automatic SubTotals
Removing SubTotal
Pivot Table Reports
Creating Second and Subsequent Pivot Table Reports
Re-Running a Pivot Table Wizard
The Pivot Table Toolbar
Converting Text to Columns
Using Copy, Paste
Open a Text (*TXT) File in Excel
Concatenating Data
Solver and Goal Seek
Goal Seek
Solver
Report Results
Save Scenario
Resetting Solver
About the Solver Options Dialog Box
Data Integrity
Overview
Conditional Formatting
Going To Cells With Conditional Formatting
Deleting Conditional Formatting
Data Validation
Restricting Cell Entries To Numbers / Dates / Times Within Specified Limits
Restricting Cell Entries To The DataForm List
Limiting The Number Of Characters In Cell Entries
Macros
Understanding Macros
Recording and Running a Macro
Understanding Macro Code
Debugging Using Step Mode
Editing a Macro
Duration 2 days (9:00 am - 4:30 pm)
Venue 3-5 Bennett Street (Gr. Fl.)
East Perth WA 6004
Telephone 08 9221 0688
Facsimile 08 9221 0687

Home | Training | Services | Course Schedule | Course Outlines | Affiliates | Contact Us