Develop and Use Complex Spreadsheets - Microsoft Excel 2003 (23 hours, 3 days)

General Description
The skills and knowledge covered in this publication are sufficient to create real-world workbooks and charts, and to use advanced functions and macros to create automated workbooks.

Technical Observations
• Application - Microsoft Excel 2003
• Competency - BSBADM404A

Learning Outcomes
At the completion of this course you should be able to:
• use the various printing features to produce a variety of reports
• create a variety of charts (graphs) in Excel
• enhance charts that you create in Excel
• use a range of logical functions
• use the mathematical functions in Excel
• use the statistical functions in Excel
• use a variety of Financial functions
• use the date and time functions to perform calculations
• use the various Lookup functions
• use linking to create more efficient workbooks
• use templates to create consistency in your workbooks
• import data into and export data from workbooks
• create recorded macros in Excel
• use the macro recorder to create a variety of macros
• access and use the help system and work safely with your computer

Target Audience
This publication has been mapped to the BSBADM404A Develop & Use Complex Spreadsheets competency and is designed to act as a learning guide covering the technical aspects of the competency. It is primarily designed for people who need to know how to use Microsoft Excel 2003 to develop automated and linked workbook solutions.

This publication assumes a basic knowledge of Microsoft Excel consistent with the skills and knowledge detailed in the competency BSBCMN214A Create & Use Simple Spreadsheets. It would also be beneficial to have a general understanding of personal computers and the Windows operating system as you will be required to start applications, work with files, and locate folders.

The In Focus series of publications have been written with one topic per page. Topic sheets either contain relevant reference information, or detailed step-by-step instructions designed on a real-world case study scenario.

Companion Publications
More choices and easier customization for whatever you or your clients require:
Student Guide - in depth guide featuring additional exercises, and consolidation tasks
Instructor Guide - includes teacher resource kits, assessment resources, class workshop files, and solutions to all exercises and tasks.
Abbreviated Student Guide - low-cost and concise student guides
Self-Study Guide - the most thorough, extensive and complete student guide available

Table of Contents
Printing Techniques
• Printing A Selection
• Setting The Print Area
• Setting Page Margins By Dragging
• Setting Margins With Page Setup
• Centring Data On The Page
• Printing Gridlines
• Specifying Print Title Rows
• Specifying Print Title Columns
• Printing With Row And Column Headings

• Using The Chart Wizard
• Choosing The Chart Type
• Changing The Chart Type
• Modifying A Chart
• Printing A Chart
• Creating A Pie Chart
• Creating A Bar Chart
• Moving A Chart
• Deleting A Chart

Charting Techniques
• Adding Data Labels
• Changing The Legend
• Changing Chart Titles
• Showing Data Tables
• Changing Fills And Patterns
• Chart Gridlines

Logical Functions
• Logical Function Concepts
• Displaying Information Using IF
• Displaying Values Using IF
• Nesting An IF Function
• Using The AND Function
• Using The OR Function
• Using The NOT Function

Math Functions
• Math & Trig Functions Listing
• Calculating Square Root
• Calculating Absolute Value
• Rounding Values
• Rounding Up Values
• Using The FLOOR Function
• Truncating Values
• Using The SUMIF Function

Statistical Functions
• Statistical Functions Listing
• Status Bar Statistics
• Basic Statistical Functions
• Counting Cells
• Calculating Standard Deviation
• Conditional Counting

Financial Functions
• Financial Functions Listing
• Calculating Regular Payments With PMT
• Calculating Future Values With FV
• Calculating Net Present Value With NPV

Date & Time Functions
• Date & Time Functions Listing
• Using The NOW Function
• The HOUR & MINUTE Functions
• Using The TODAY Function
• Calculating A Future Date
• Creating A Four Digit Year Format
• Using The DATE Function
• The Calendaring Functions
• The WEEKDAY Function

Lookup Functions
• Requirements Of Lookup Functions
• The CHOOSE Function
• The ISERROR Function
• The VLOOKUP Function
• Testing Lookup Functions
• Using VLOOKUP For Exact Matches
• The INDEX Function

Linking Workbooks
• Linking Data In Excel
• Linking Within A Workbook
• Linking Between Workbooks
• Updating Links Between Workbooks

• Creating A Template
• Using A Template
• Inserting A Worksheet From A Template
• Locating The Excel Templates
• Modifying A Template
• Creating An AutoStart Template

Importing & Exporting
• Importing From Another Spreadsheet Program
• Text File Formats
• Importing Tab-Delimited Text
• Exporting To Microsoft Word
• Exporting Data As Text

Recorded Macros
• 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

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

General Computer Usage
• Printed Help Sources
• The Help Task Pane
• Searching For General Help
• Searching For Specific Help
• Understanding A Help Window
• Disabling Online Content
• Enabling Online Content
• Displaying And Using The Office Assistant
• Customising The Office Assistant
• Disabling The Office Assistant
• Screen Tips And Dialog Box Help
• Setting Up An Ergonomic Workstation
• Breaks And Exercises
• Minimising Paper Wastage
• Backup Procedures
• Glossary Of Database Terms
• Electronic File Management
• Managing File Folders In Excel 2003
• Maintaining A Printed File Register
• Creating A Quick File Register

Concluding Remarks