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