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

This course will help you to master the features of Excel required to develop and maintain spreadsheets for use in a workgroup. You will learn to use security, validation, and auditing tools to ensure the integrity of data.

You will also learn to customise the user environment by creating command bars and recording and editing macros. Finally, you will learn to integrate Office applications using XML data and Microsoft Query.

Target audience

Those wanting to develop their skills in using Excel to design spreadsheets that solve complex problems and can be deployed effectively in a workgroup.

Templates and Add-Ins

Create a template file for frequently used worksheet layouts • Edit and apply a template file • Use workgroup templates • Load an Add-in program

Using Analysis Tools

Use one- and two-input data tables to resolve What-If? calculations • Solve a problem using Goal Seek • Use Solver to calculate complex problems • Create What-If analyses using the Scenario Manager

Scatter and Combination Charts

Create an XY (Scatter) or Bubble chart • Add a trendline to a chart • Create a combination chart • Add a second value axis to a chart

Protecting Data

Apply and remove worksheet protection • Lock and unlock cell ranges • Apply and remove file passwords to open or change a workbook

Excel Validating Data Entry

Apply data validation rules and display user prompts • Validate data on a worksheet

Auditing a Worksheet

Display formulas in worksheet cells • Trace precedents and dependents • Trace and fix errors • Use the Formula Error Checker • Use the Formula Evaluator to obtain results in part of a formula • Use the Watch Window to view results in cells

Sharing a Workbook

Create a shared workbook • Track changes and resolve conflicting changes • Merge workbooks • Route a workbook to several reviewers

Querying a Database

Set up a data source • Create a query to import data as an external range • Format and refresh an external data range • Import a table from an HTML web page • Round trip HTML data in Excel

Using XML

Understand the use and capabilities of XML • Use Access to generate a schema • Apply a schema to a Word document • Import and export XML lists in Excel

Customising Command Bars

Add and remove a command from a toolbar • Customise button images and groups • Create a new command bar • Assign keyboard shortcuts to actions

Using Views and Workspaces

Save different views of a workbook • Use a workspace file

Recording a Macro

Record a macro • Run a macro • Edit a macro • Delete a macro • Assign macros to menus, shortcut keys, and toolbars • Set up macro virus protection

Digitally Signing a File

Create, view, and remove a digital signature

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