Microsoft Office Excel 2007 Level 3

DSOC0273—One Day Training Class

Course Description

Elements of this syllabus are subject to change.

This course includes the popular VLOOKUP function and pivot tables. Logical functions such as AND and OR are included also. The student will learn how to validate data that is entered and set caps on values that can be entered. Students will create and manage formulas that refer to cells in other workbooks and use a command to consolidate data from multiple ranges into a single range. To solve what-if questions, the students will learn to use Scenarios, Goal seek, and Solver. The course also includes importing and exporting data, naming cells, ranges and constants, and using names in formulas.

Audience

This course was designed for students desiring to gain the skills necessary to create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import and export data. In addition, the course is also for students desiring to prepare for the Microsoft Certified Application Specialist exam in Microsoft Office Excel 2007, and who already have knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data.

At Course Completion

After completing this course, students will be able to:

• Connect to external data and set it to refresh automatically and connect to data on the internet
• Export a worksheet
• Assign names to ranges and use existing labels to assign names to ranges
• Use names in formulas
• Use a function to return a value from same row as a looked-up value
• Use a function to specify the result to be returned if a formula would otherwise result in an error
• Use the logical functions AND and OR along with multiple arguments, to return TRUE or FALSE
• Control the kind of information and set limits on values that can be entered into a cell and customize an input message and an error message
• Create formulas that refer to cells in other workbooks
• Combine ranges into a single range as a summary
• Create pivot tables to summarize data
• Save different “What-if” situations in a single worksheet
• Specify the desired formula result and which input Excel should change to yield that formula result
• Use Solver to find answers for situations with multiple variables
• Record, run, and edit macros

Prerequisites

Before starting this course, students are recommended to take the following courses or possess equivalent knowledge: Microsoft Office Excel 2007 Level 1 and Microsoft Office Excel 2007 Level 2

Training Course Content

Module 1: Connecting to Data and Exporting Data

• Create and Use Connection
• Export an Excel Worksheet as XML, text, or CSV
• Publish a Worksheet to the Web

Module 2: Advanced Formulas

• Using the Name Manager and Defining Names
• The VLOOKUP Function
• The “IS” Functions
• AND and OR
• IFERROR

Module 3: Validation and Auditing

• Types of Data Validation
• Types of Messages
• Locating Data Validation and Invalid Data
• Show Formulas
• Trace Cell Precedents and Cell Dependents
• Formula Evaluator
• Error Checking
• The Watch Window

Module 4: Combining Data from Multiple Workbooks

• Source Workbooks and Destination Workbooks
• Arranging the Windows
• Creating Linking Formulas
• Security
• Workspaces
• Consolidating Data

Module 5: Pivot Tables

• PivotTable Reports and Fields
• PivotTable Tools
• Pivot Charts

Module 6: What-If Tools

• Scenarios
• Goal Seek
• Data Tables
• Solver

Module 7: Macros

• Recording a Macro
• Macro Security
• Editing a Macro