Excel 2002 - Advanced

Course Length: One Day
Fees: Training at Client Offices: One delegate: £395 per-day + VAT
Additional delegates attending: £75 each per-day + VAT

Overview: Microsoft Excel 2002 is a software application that can be used as a spreadsheet, database, or graphing program. The electronic spreadsheet portion of Excel allows you to perform sophisticated calculations and create formulas that automatically calculate answers.

The advantage of using formulas is that when data in the worksheet changes, all the formulas recalculate automatically. This feature assists you in developing budgets, forecasting models, creating sales plans, making financial projections, calculating inventories calculations, generating banking statements, and basically working with any format involving numbers. Excel can sort lists and select specific pieces of information based on specified conditions.

The Advanced module includes: Working wth the Data Series; Advanced Functions; Scenarios & Goal Seeking; Auditing Tools; Solver; Creating Macros; Pivot Tables; Creating Custom Toolbars & Menus.

Working with Data Series

	Using the Series Command
	Creating a Linear Series
	Creating a Date Series
	Using a Stop Value
	Creating a Growth Trend Series

Using Advanced Functions

	Using Lookup Functions
	Using the VLOOKUP Function
	Using the HLOOKUP Function
	Using the IF Function
	Using Nested IF Functions
	Using the ISERROR Function
	Using an AND Condition with IF
	Using an OR Condition with IF
	Using the ROUND Function
	Limiting the Precision of Numbers

Using Scenarios and Goal Seeking

	Using the Scenario Manager
	Creating a Scenario
	Displaying a Scenario
	Editing a Scenario
	Creating a Scenario Summary Report
	Using Goal Seek

Using Data Tables

	Using Data Tables
	Placing Formulas in Data Tables
	Creating a One-Variable Table
	Creating a Two-Variable Table

Using Auditing Tools

	Displaying the Formula Auditing Toolbar
	Displaying/Removing Dependent Arrows
	Displaying/Removing Precedent Arrows
	Removing All Tracer Arrows
	Using the Trace Error Button
	Tracing Cells Causing Errors
	Using the Error Checking Button
	Using the Evaluate Formula Button
	Using the Watch Window

Consolidating Worksheets

	Consolidating Worksheets
	Consolidating by Category
	Consolidating by Position

Solving Problems

	Using Solver
	Saving a Solution as a Scenario
	Restoring the Original Values
	Changing a Constraint
	Creating a Solver Report
	Viewing Solutions using Scenarios

Working with Views

	Using Views
	Creating a Normal View
	Creating a Custom View
	Displaying a View
	Deleting a Custom View

Using Macros

	Defining Macros
	Opening a Workbook Containing Macros
	Running a Macro
	Using a Shortcut Key
	Using the Visual Basic Toolbar
	Opening the Visual Basic Editor Window
	Using the Visual Basic Editor Window

Recording Macros

	Recording a Macro
	Assigning a Shortcut Key
	Using Relative References
	Deleting a Macro

Editing Macros

	Entering Macro Comments
	Copying Macro Commands
	Typing Macro Commands
	Running a Macro from the Code Window

Using Custom Toolbars and Menus

	Creating a Custom Toolbar
	Adding/Removing Toolbar Buttons
	Adding the Custom Button to a Toolbar
	Assigning a Macro to a Button
	Changing Button Text and Image
	Attaching a Custom Toolbar
	Creating a Custom Menu
	Adding Custom Menu Commands

Creating a Macro Button

	Using a Macro Button
	Creating a Macro Button
	Formatting a Macro Button
	Moving/Sizing a Macro Button

Creating/Revising PivotTables

	Creating a PivotTable Report
	Adding PivotTable Report Fields
	Selecting a Page Field Item
	Changing the Summary Function
	Adding New Fields to a PivotTable Report
	Hiding/Unhiding PivotTable Report Items
	Deleting PivotTable Report Fields
	Creating a Page Field Report
	Formatting a PivotTable Report
	Creating a PivotChart Report
	Publishing PivotTable Reports to the Web
	Adding Fields to a PivotTable - Browser
	Using a PivotTable List

If you have other questions, please contact us at enquiry@it-uk.com or complete our response form