|
|
 Excel
2002-2003
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 |
|
