Preparing for the Microsoft Certified Application Specialist Exam in Microsoft Office Excel 2007

50041: Preparing for the Microsoft Certified Application Specialist Exam in Microsoft Office Excel 2007 (1 day)

About this Course

This one-day instructor-led course provides students with an overview of the Microsoft Office Excel 2007 features and functions that pertain to the objectives of the corresponding Microsoft Business Certification credential.

Audience Profile

This course is intended for beginning to intermediate-level users who want to learn the core capabilities and essential skills for working with Excel 2007.

At Course Completion

After completing this course, students will be able to:

• Set up and modify workbooks and worksheets.
• Perform formulas to calculate values.
• Manipulate list data.
• Create and use PivotTables.
• Create charts and diagrams.

Course Outline

Module 1: What's New in Excel 2007

This module explains the new features that are included in Excel 2007.

Lessons

• Becoming Familiar with the New User Interface
• Managing Larger Data Collections
• Using the New Microsoft Office File Format
• Formatting Cell and Worksheets
• Managing Data Tables More Effectively
• Creating Formulas More Easily by Using Formula AutoComplete
• Summarizing Data Using New Functions
• Creating Powerful Conditional Formats
• Creating More Attractive Charts
• Controlling Printouts More Carefully

After completing this module, students will be able to:

• Understand the new user interface.
• Use the improved formatting capabilities.

Module 2: Setting Up a Workbook

This module explains how to create and modify workbooks, create and modify worksheets, make workbooks easier to find, and customize the Excel 2007 program window.

Lessons

• Creating Workbooks
• Modifying Workbooks
• Modifying Worksheets
• Customizing the Excel 2007 Program Window

After completing this module, students will be able to:

• Create a workbook.
• Modify a workbook.
• Modify a worksheet.
• Customize the Excel 2007 program window.

Module 3: Working with Data and Data Tables

This module explains how to enter and revise Excel 2007 data, move data within a workbook, find and replace existing data, use proofing and reference tools to enhance data, and organize data by using Excel 2007 data tables.

Lessons

• Entering and Revising Data
• Moving Data Within a Workbook
• Finding and Replacing Data
• Correcting and Expanding Upon Worksheet Data
• Defining a Table

After completing this module, students will be able to:

• Enter data quickly.
• Move data within a workbook.
• Find and replace workbook data.
• Verify and extend workbook data using proofing and reference tools.
• Organize data using tables.

Module 4: Performing Calculations on Data

This module explains how to streamline references to groups of data on worksheets and how to create and correct formulas that summarize Consolidated Messenger's business operations.

Lessons

• Naming Groups of Data
• Creating Formulas to Calculate Specific Conditions
• Finding and Correcting Errors in Calculations

After completing this module, students will be able to:

• Name groups of data.
• Create formulas to calculate values.
• Summarize data that meets specific conditions.
• Find and correct errors in calculations.

Module 5: Changing Document Appearance

This module explains how to change the appearance of data, apply existing formats to data, make numbers easier to read, change data's appearance based on its value, make printouts easier to follow, and position the data on the printed page.

Lessons

• Formatting Cells
• Defining Styles
• Applying Workbook Themes and Tables Styles
• Making Numbers Easier to Read
• Changing the Appearance of Data Based on Its Value
• Adding Images to a Document

After completing this module, students will be able to:

• Format a cell.
• Define styles.
• Apply a workbook theme.
• Make numbers easier to read.
• Change data's appearance based on its value.
• Add an image to a document.

Module 6: Focusing on Specific Data Using Filters

This module explains how to limit the data that appears on the screen, manipulate list data, and create validation rules that limit data entry to appropriate values.

Lessons

• Limiting Data That Appears on ther Screen
• Manipulating List Data
• Defining Valid Sets of Values for Ranges of Cells

After completing this module, students will be able to:

• Limit the data that appears on the screen.
• Manipulate list data.
• Define a valid set of values for a range of cells.

Module 7: Reordering and Summarizing Data

This module explains how to sort the data using one or more criteria, calculate subtotals, organize data into levels, and look up information in a data list.

Lessons

• Sorting Data Lists
• Organizing Data into Levels
• Looking Up Information in a Data List

After completing this module, students will be able to:

• Sort a data list.
• Organize data into levels.
• Look up information in a data list.

Module 8: Combining Data from Multiple Sources

This module explains how to use a data list as a template for other lists, work with more than one set of data, link to data in other workbooks, summarize multiple sets of data, and group multiple data lists.

Lessons

• Using Data Lists as Templates for Other Lists
• Linking to Data in Other Worksheets and Workbooks
• Consolidating Multiple Sets of Data into a Single Workbook
• Grouping Multiple Sets of Data

After completing this module, students will be able to:

• Use an existing data list as a template for other lists.
• Link to data in other workbooks.
• Summarize multiple sets of data.
• Group multiple data lists.

Module 9: Analyzing Alternative Data Sets

This module explains how to define alternative data sets and determine the necessary inputs to make a calculation produce a particular result.

Lessons

• Defining and Editing Alternative Data Sets
• Defining Multiple Alternative Data Sets
• Varying Data to Get a Desired Result Using Goal Seek
• Finding Optimal Solutions with Solver
• Analyzing Data with Descriptive Statistics

After completing this module, students will be able to:

• Define and edit alternative data sets.
• Define multiple alternative data sets.
• Vary the data to get a desired result using Goal Seek.
• Find optimal solutions with Solver.
• Analyze data with descriptive statistics.

Module 10: Creating Dynamic Lists with PivotTables

This module explains how to create and edit Microsoft Office PivotTables from an existing worksheet and how to create a PivotTable with data imported from a text file.

Lessons

• Analyzing Data Dynamically with PivotTables
• Filtering, Showing, and Hiding PivotTable Data
• Editing PivotTables
• Formatting PivotTables
• Creating PivotTables from External Data

After completing this module, students will be able to:

• Create dynamic lists with PivotTables.
• Filter, show, and hide PivotTable data.
• Edit PivotTables.
• Format PivotTables.
• Create PivotTables from external data.

Module 11: Creating Charts and Graphics

This module explains how to create a chart and customize its elements, find trends in the overall data, create dynamic charts, and create and format diagrams.

Lessons

• Creating Charts
• Customizing the Appearance of Charts
• Finding Trends in Data
• Creating Dynamic Charts Using Microsoft Office PivotCharts
• Creating Diagrams by Using Microsoft Office SmartArt

After completing this module, students will be able to:

• Create charts.
• Customize the appearance of charts.
• Find trends in the data.
• Create dynamic charts by using PivotCharts.
• Create diagrams by using SmartArt.

Module 12: Printing

This module explains how to print all or part of a data list, how to print charts, and how to add headers and footers to worksheets.

Lessons

• Adding Headers and Footers to Printed Pages
• Preparing Worksheets for Printing
• Printing Data Lists
• Printing Parts of Data Lists
• Printing Charts

After completing this module, students will be able to:

• Add headers and footer to printed pages.
• Prepare worksheets for printing.
• Print data lists.
• Print parts of data lists.
• Print charts.

Module 13: Automating Repetitive Tasks with Macros

This module explains how to open, run, create, and modify macros. It also explains how to create Quick Access Toolbar buttons and shapes that enable you to run macros with a single mouse click, define macro security settings, and run a macro when a workbook is opened.

Lessons

• Introducing Macros
• Creating and Modifying Macros
• Running Macros When a Button is Clicked
• Running Macros When a Workbook is Opened

After completing this module, students will be able to:

• Describe and examine macros.
• Create and modify macros.
• Run macros when a button is clicked.
• Run macros when a workbook is opened.

Module 14: Working with Other Microsoft Office System Programs

This module explains how to include a Microsoft Office System document in a worksheet, store an Excel 2007 workbook as part of another Microsoft Office System document, create hyperlinks, and paste an Excel 2007 chart into another document.

Lessons

• Including Microsoft Office System Documents in Excel 2007 Worksheets
• Storing Excel 2007 Documents as Parts of Other Microsoft Office System Documents
• Creating Hyperlinks
• Pasting Charts into Other Documents

After completing this module, students will be able to:

• Include Microsoft Office System documents in worksheets.
• Store Excel 2007 documents in other Microsoft Office System documents.
• Create hyperlinks.
• Paste Excel 2007 charts into other documents.

Module 15: Collaborating with Colleagues

This module explains how to share a data list, manage comments to workbook cells, track and manage changes made by colleagues, protect workbooks and worksheets, and digitally sign workbooks.

Lessons

• Sharing Data Lists
• Managing Comments
• Tracking and Managing Colleagues' Changes
• Protecting Workbooks and Worksheets
• Authenticating Workbooks
• Saving Workbooks for the Web

After completing this module, students will be able to:

• Share data lists.
• Manage comments.
• Track and manage colleagues' changes.
• Protect workbooks and worksheets.
• Authenticate workbooks.
• Save workbooks for the Web.

Before attending this course, students must have:

• Basic computer knowledge