Microsoft Excel 2003: Analytics

Course Overview

This course is aimed at people who want to expand their knowledge into the more specialised analytical functionality and built-in automation offered by Microsoft Excel.

Not sure whether this is the right Microsoft training course for you? For a more advanced search, click on our course category page

Related Courses

• Microsoft Excel 2003: Advanced (2 day)
• Microsoft Excel 2003: Visual Basic for Applications (VBA)
• Microsoft Excel 2003: Intermediate (1 day)
• Microsoft Excel 2003: Introduction for Beginners (1 day)
• Microsoft Excel 2003: Introduction for Beginners (2 day)
• Microsoft Excel 2003: Advanced (1 day)
• Microsoft Excel 2003: Intermediate (2 day)
• Microsoft Excel 2003: VB & Macros
• Microsoft Excel 2000 XP 2003: Advanced - Formulae and Functions
• Microsoft Excel 2003: Programming with VBA

Audience

Students who wish to attend this course must have a very good knowledge of Excel and be competent at working with advanced functions (e.g. Logical & LookUp) and have some basic knowledge of Excel's analytical capabilities (e.g. Pivot Tables and database tools).

Skills Gained

On completion of this course delegates will be able to:

• Import and export data from other applications
• Create variable tables to display different results using Data Form
• Use variables to find a required result using Goal Seek, Scenario Manager and Solver
• Use SUMIF, COUNTIF, Index and Match functions
• Create a purpose-made form using the Forms Toolbar
• Create standardised worksheets with the use of templates
• Customise toolbars and automate worksheets

Course Outline

Using Other Functions

• Using Date Functions
• Formatting Dates

Useful Functions

• INDEX
• MATCH
• SUMIF
• COUNTIF

Importing & Exporting Data

• Importing Data from Text Files
• Exporting Data
• Changing External Data Range Properties
• Importing Data from Other Applications
• Removing the Query Definition
• Exporting Data to Other Applications

Using Scenarios & Goal Seek

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

Solving Problems

• Using Solver
• Restoring the Original Values
• Changing a Constraint

Creating a Solver Report

Using Data Tables

• Placing Formulas In a Data Table
• Creating One & Two Variable Tables

Using the Forms Toolbar

• Worksheet Controls - Introduction to ActiveX Controls
• Creating a Control Box, Check Box, List Box, Options Buttons & a Spinner

UsingTemplates

• Working with Templates
• Using, Editing, Deleting Templates

Introduction to Macros

• Defining & Running Macros
• Recording a Macro
• Assigning a Shortcut Key & Menu
• Creating a Macro Button

Using Custom Toolbars & Buttons

• Creating a Custom Toolbar
• Adding a Button
• Adding a Custom Button
• Assigning a Macro Button
• Changing a Button Image
• Attach & Delete a Custom Toolbar