Introduction
Elements of this syllabus are subject to change.
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
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.
Prerequisites
Before attending this course, students must have:
Basic computer knowledge
:: Course 50041A ::
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.
:: Course 50041A ::
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.
:: Course 50041A ::
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.
:: Course 50041A ::
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.
:: Course 50041A ::
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.
:: Course 50041A ::
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.
:: Course 50041A ::
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.
:: Course 50041A ::
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.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.