DT016 - Excel 2003 Level 3

This two-day instructor-led course will increase your productivity by providing the advanced skills and knowledge to use Microsoft Excel 2003 effectively through group and individual instruction, hands-on practical exercises, visual aids, and comprehensive training manuals and materials.

Upon successful completion of this course, students will be able to automate common Excel tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and share Excel data with other applications.

Advanced Functions
• What is a Function?
• Text Functions
• Mathematical Functions
• Logical Functions
• Nested If Functions

Working with External Data
• About Microsoft Query
• Starting a Query within Excel
• Creating a Query
• Editing the Data Results
• Editing a Saved Query
• Refreshing a Single Query
• Refreshing All Queries At Once
• Refreshing Queries Automatically

Using Hyperlinks in Spreadsheets
• What is a Hyperlink?
• Using a Hyperlink
• Creating a Link to a Different Cell
• Creating a Link to Other Worksheets
• Creating a Link to Other Workbooks
• Creating a Link to a Web Page
• Formatting Hyperlink Styles
• Editing a Hyperlink

Using Advanced Filtering Techniques
• Advanced Filter
• Using Functions to Analyse a List
• Database Functions

Analysing Data
• Using Goal Seek
• One-Input Data Tables
• Two-Input Data Tables
• Editing Tables
• Creating a Scenario
• Viewing Scenarios
• Editing a Scenario
• Adding the Scenarios List to a Toolbar
• Generating a Summary Report
• Deleting and Merging Scenarios
• Creating and Using Custom Views

Embedding and Linking Objects
• Embedding Versus Linking
• Embedding an Object
• Linking an Object
• Editing an Object
• Updating Links
• Changing the Link to Update Manually
• Using Start Up Prompts in Excel
• Hiding an Object’s Data
• Deleting an Object

Protecting Workbooks
• About Protection
• Unlocking Cells
• Protecting a Worksheet and Workbook
• Unprotecting Workbooks and Worksheets

Pivottable and Pivotchart Reports
• What is a Pivottable?
• Creating a Pivottable
• Applying Different Summary Functions
• Displaying/Hiding Items in Fields
• Rearranging a Pivottable
• Using the Page Axis
• Setting Pivottable Options
• Creating a Pivottable from Multiple Sources
• Creating a Pivotchart Report from a List
• Creating a Pivotchart Report from a Pivottable

Array Calculations
• Working with Arrays
• Creating a Basic Array Calculation
• Editing and Deleting an Array
• Consolidating Information Using Arrays

Working with Controls
• About Controls
• Displaying the Forms Toolbar
• Adding Controls to a Worksheet
• Setting Properties for Controls
• Using Linked Cells

Consolidation
• Consolidating Worksheets
• Creating Links to Source Data
• Performing Other Summary Operations
• Consolidation by Category

Working with Add - Ins
• What is an Add - In?
• Loading and Unloading an Add - In

Sharing Workbooks
• Sharing Workbooks
• Customising Shared Workbook Options
• Tracking Changes, Displaying Comments
• Reviewing Changes, Merging Workbooks

Using Macros
• What is a Macro?
• Recording a Basic Macro
• Assigning a Macro to a Toolbar
• Running a Macro
• Changing the Picture on a Toolbar
• Deleting Macros
• What is Vba?
• Components of Code
• Editing Vba Code
• Writing Code
• Adding Comments

Using the Web Tools
• Converting an Excel Workbook into Html
• Publishing Excel Items
• Viewing Published Excel Items
• Editing and Republishing Pages
• Deleting Excel Web Pages

This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building and formatting simple workbooks.

This course is designed for current Microsoft Excel users who can build basic workbooks. It is assumed that the user will know how to create and save a workbook, create formulas and use functions, and perform simple formatting of data.