Download Course Details

Prerequisites

Microsoft Excel 2003 Advanced assumes the delegate has attended the Excel 2003 Fundamentals and Excel Intermediate courses or has equivalent knowledge.

Audience

Microsoft Excel 2003 Advanced is designed for users who are keen to extend their understanding and knowledge of the software.

Objectives

At the completion of Microsoft Excel 2003 Advanced you should be able to:

• use names and labels
• use a wide range of paste special options
• use a variety of Financial functions
• create, use and modify data tables
• use advanced filters to analyse data in a list
• summarise data using subtotals and relative range naming
• use data forms to manage data in a list
• use the statistical functions in Excel
• use goal seeking to determine the values required to reach a desired result
• use Solver to resolve complex optimisation problems
• create and work with scenarios and the Scenario Manager
• import data into and export data from workbooks
• create, modify and work with PivotTables
• create recorded macros in Excel
• use the macro recorder to create a variety of macros
• confidently open workbooks that contain macros
• create and use a range of controls in a worksheet
• publish workbooks and worksheets for the web

Outline

Labels and Names
Labels And Names Explained
Accepting Labels In Formulas
Using Text Labels
Creating Labels
Creating Names Using The Name
Box
Using Names To Select Cells
Using Names In Formulas
Creating Names Between
Workbooks
Creating Names For Constants
Creating A List Of Names
Changing The Range For Names
Deleting Names
Paste Special
The Paste Special Options
Copy Formulas
Copy Formats
Copy Validation Settings
Copy Column Widths
Copy Values
Copy Comments
Add Values
Multiply Values
Transpose Cells
Financial Functions
Financial Functions Listing
Regular Payments With PMT
Calculating Future Values With FV
Net Present Value With NPV
Present Value With PV
The Interest Rate With RATE
Data Tables
Using A Simple What-If Model
Creating A One-Variable Table
Using One-Variable Data Tables
Creating A Two-Variable Data
Table
Advanced Filters
Advanced Filter Concepts
Using An Advanced Filter
Extracting Records With
Advanced Filter
Using Formulas In Criteria
Using Database Functions
The Database Functions
Using The DSUM Function
Using The DMIN Function
Using The DMAX Function
Using The DCOUNT Function
Summarising Data
Creating Subtotals
Using A Subtotalled Worksheet
Creating Nested Subtotals
Copying Subtotals
Using Subtotals With AutoFilter
Using The Conditional Sum
Wizard
Creating Relative Names For
Subtotals
Using Relative Names For
Subtotals
Data Forms
Displaying A Data Form
Viewing Records By Criteria
Editing Records
Adding A New Record
Deleting Records
Statistical Functions
Statistical Functions Listing
Status Bar Statistics
Basic Statistical Functions
Counting Cells
Using MODE & MEDIAN
Calculating Standard Deviation
Conditional Counting
Goal Seeking
Goal Seek Components
Using Goal Seek
Solver
Installing Solver
Solver Theory
Using Solver and Solver Reports
Restricting Answers Using Solver
Options
Scenarios
Creating A Default Scenario
Creating Scenarios
Using Names in Scenarios
Displaying Scenarios
Creating A Scenario Summary
Report
Merging Scenarios
Importing & Exporting
Importing From Another
Spreadsheet Program
Text File Formats
Importing Tab-Delimited Text
Exporting To Microsoft Word
Linking Data Into A Word
Processing Document
Exporting Data As Text
Importing Comma-Delimited Text
Importing Space-Delimited Text
Importing An Object Into A
Worksheet
Formatting An Imported Object
PivotTables
PivotTable Theory
Creating A Simple PivotTable
Adding Row Fields 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
Refreshing A PivotTable
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
Assigning A Macro To A Toolbar
Button
Running A Macro From A Custom
Button
Removing A Custom Button From
The Toolbar
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
Macro Virus Control
Opening Workbooks With Macros
Setting Macro Security Levels
Trusting Publishers
Creating A Digital Signature
Removing A Trusted Publisher
Controls
Control Types
How Controls Work
Preparing A Worksheet For
Controls
Adding A Combo Box Control
Changing Control Properties
Using The Cell Link To Display
The Selection
Adding A List Box Control
Adding A Scroll Bar Control
Adding A Spinner Control
Adding Option Button Controls
Adding A Group Box Control
Adding A Check Box Control
Protecting A Workbook With
Controls
Excel On The Web
Previewing Workbooks As Web
Pages
Publishing A Static Worksheet
Adding To An Existing Web Page
Publishing An Interactive Web
Page