investors in people REC member one group many options
 

Microsoft Excel 2003 Advanced

Details

Time: 09:00 to 16:00
Venue: AP Courses Training Room
Cost: £295
Included in the price: Learning manual for the course; lunch, refreshments and post-course support.

Course Description

Microsoft Excel 2003 Advanced is designed for users who are keen to extend their understanding and knowledge of the software.  The skills and knowledge acquired in Microsoft Excel 2003 Advanced are sufficient to be able to use and operate the software at a proficient level.

Course Design

The course is a full-day tutor-led course that combines tutoring and practical work.

Target Audience

Microsoft Excel 2003 Advanced assumes that you are familiar with using personal computers and have used a mouse and keyboard. It would be beneficial to have a general understanding of personal computers and to be comfortable in the Windows operating system environment.  This course is intended for persons pursuing Microsoft Office Specialist Certification in Microsoft Excel 2003 or in employment that involves using Microsoft Excel to create and insert charts, create and insert pivot tables, functions and formulas, macros, filter data, format appearance of spreadsheets and cells.

Learning Objectives

On completion of Microsoft Excel 2003 Advanced, you should be able to:

  • Nest functions to create complex formulas
  • Use advanced filters to analyse data in a list
  • Use a variety of data validation techniques
  • Use goal seeking to determine the values required to reach a desired result
  • Create, use and modify data tables
  • Create and work with scenarios and the Scenario Manager
  • Create, modify and work with pivot tables
  • Use linking to create more efficient workbooks
  • Combine data in separate worksheets or workbooks using consolidation
  • Publish workbooks and worksheets for the web
  • Summarise data using subtotals and relative range naming
  • Confidently open workbooks that contain macros
  • Create recorded macros in Excel
  • Use the macro recorder to create a variety of macros

Course Content

Nesting Functions

  • Scoping a formula
  • Developing, creating and editing a nested formula
  • Copying a formula with nested functions
  • Concatenation

Advanced Filters

  • Advanced filter concepts
  • Using an advanced filter
  • Extracting records with advanced filters
  • Using formulas in criteria
  • Using database functions
  • The database functions
  • Using the DSUM functions
  • Using the DMIN functions
  • Using the DMAX functions
  • Using the DCOUNT functions

Validations

  • Validation techniques
  • Data validation by number range
  • Testing data validation
  • Input messages
  • Create error alerts
  • Create drop down lists
  • Using formulas and validation criteria
  • Create custom validation
  • Number formats with built-in logic
  • Conditional formatting
  • Copying data validation settings

Goal Seeking

  • Goal-seek components
  • Using goal seek

Data Tables

  • Using a simple ’what-if’ model
  • Creating a one-variable table
  • Using one-variable data tables
  • Creating a two-variable data table

Scenarios

  • Creating a default scenario
  • Creating scenarios
  • Using names in scenarios
  • Displaying scenarios
  • Creating a scenario summary report
  • Merging scenarios

Pivot Tables

  • Pivot table history
  • Creating a simple pivot chart
  • Adding row fields to a pivot table
  • Using the page field in pivot table
  • Filtering row and column values
  • Formatting a pivot table
  • Counting with pivot tables
  • Pivot table and summary and display option
  • Show data as percentages in pivot tables
  • Calculated items in pivot tables
  • Calculated fields in pivot tables
  • Creating a pivot chart
  • Refreshing a pivot table
  • Modifying a pivot chart via the pivot table

Linking Workbooks

  • Linking data in Excel
  • Linking within a workbook
  • Linking between workbooks
  • Updating links between workbooks

Consolidation

  • Consolidating data with identical layouts
  • Consolidating data with the same and different layouts
  • Consolidating data using a pivot table
  • Consolidating data using 3D SUM functions

Excel on the Web

  • Previewing workbooks as web pages
  • Publishing a static worksheet
  • Adding to an existing webpage
  • Publishing an interactive web page

Summarising Data

  • Creating subtotals
  • Using a subtotalled worksheet
  • Creating nested subtotals
  • Copying subtotals
  • Using sub totals with auto filter
  • Installing and using the conditional sum wizard
  • Creating relative names for subtotals
  • Using relative names for subtotals

Macro Virus Control

  • Opening workbooks with macros
  • Setting macro security levels
  • Trusting publishers
  • Creating a digital signature
  • Removing a trusted publisher

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

Recorded Workshops

  • 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