Microsoft Excel 2002 Expert: Auditing and Analyzing Data

Overview/Description
Increasing numbers of professionals are required to perform data analysis as part of their job; companies need powerful analysis tools and the people who know how to use them. Microsoft Excel is such an analytical tool, and your ability to create and apply lookup and reference functions, audit worksheets, and analyze data will help ensure your success. This course offers you hands-on instruction so you can master data analysis with Excel. This course is also designed to prepare users for the Excel Expert 2002 MOUS certification exam.

Target Audience
This course is designed for Microsoft Excel 2002 users. Previous experience with Excel 2002 is recommended.

Expected Duration
This course, including the practice exercises, will take up to four hours to complete.

Objectives:

Ranges and Lookups

  • Define a range name
  • Create multiple range names from existing data
  • Use range names in formulas
  • Use VLOOKUP to find an approximate match
  • Use VLOOKUP to find an exact match
  • Use HLOOKUP

    • Auditing Worksheets

  • Use the Formula Auditing toolbar
  • Identify dependent cells
  • Identify precedent cells
  • Remove tracer arrows from a worksheet
  • Display all worksheet formulas
  • Locate all worksheet cells that contain formulas
  • Locate errors in formulas
  • Use the Trace Error button

    • Using Data Validation and Creating Queries

  • Define data validation based on content
  • Define data validation based on a formula
  • Define data validation based on a data list
  • Retrieve data from an Access database table
  • Create a database query
  • Create an XML Web query using Import Data
  • Create an XML Web query using Copy and Paste

    • Analyzing Data

  • Create a PivotTable report
  • Manipulate PivotTable elements
  • Create a PivotChart report
  • Create forecast values with trendlines on charts
  • Create what-if options with Goal Seek
  • Create what-if options with Solver
  • Create multiple scenarios for a worksheet
  • Activate a scenario from a list of multiple scenarios
  • Edit a scenario


    • Course Number: MOFF0215