About this Course
This one-day instructor-led course provides students with an overview of the more advanced features and functions of Microsoft Office Excel 2007.
Audience Profile
This course is intended for experienced information workers who want to learn advanced-level Excel 2007 skills.
At Course Completion
After completing this course, students will be able to:
Define an alternative data set.
Define multiple alternative data sets.
Vary your data to get a desired result by using Goal Seek.
Find optimal solutions by using Solver.
Analyze data by using descriptive statistics.
Analyze data dynamically by using PivotTables.
Filter, show, and hide PivotTable data.
Edit & Format PivotTables.
Create PivotTables from external data.
Create dynamic charts by using PivotCharts.
Describe and examine macros.
Create and modify macros.
Run macros when a button is clicked.
Run macros when a workbook is opened.
Include Office documents in worksheets.
Store workbooks as parts of other Office documents.
Create hyperlinks.
Paste charts into other documents.
Share data lists.
Manage comments.
Track and manage colleagues’ changes.
Protect workbooks and worksheets.
Authenticate workbooks.
Save workbooks for the Web
:: Course 50125A ::
Before attending this course, students must have:
Basic computer knowledge
Basic file-management skills
Basic knowledge of the Excel interface, workbook manipulation, and formula creation
Module 1: Data Analysis
This module explains how to define alternative data sets, use Goal Seek and Solver, and analyze descriptive statistics.
Lessons
Defining an Alternative Data Set
Defining Multiple Alternative Data Sets
Varying Your Data to Get a Desired Result by Using Goal Seek
Finding Optimal Solutions by Using Solver
Analyzing Data by Using Descriptive Statistics
Lab : Data Analysis
Create a scenario to measure the impact of a rate increase.
Create and summarize the results of two scenarios.
Use Goal Seek to find a target value.
Use Solver to determine the best values for multiple variables, given specific constraints.
Generate descriptive statistics by using the Analysis ToolPak.
After completing this module, students will be able to:
Create scenarios and work with Scenario Manager.
Determine a value by using Goal Seek.
Determine multiple values by using Solver.
Use descriptive statistics to analyze data.
Module 2: PivotTables and PivotCharts
This module explains how to work with PivotTables and PivotCharts.
Lessons
Analyzing Data Dynamically by Using PivotTables
Filtering, Showing, and Hiding PivotTable Data
Editing PivotTables
Formatting PivotTables
Creating PivotTables from External Data
Creating Dynamic Charts by Using PivotCharts
Lab : PivotTables and PivotCharts
Create a PivotTable, add fields, and then pivot it.
Filter a PivotTable, and show and hide levels of detail.
Rename and edit a PivotTable, and then link to a PivotTable cell.
Apply a number format, styles, banded rows, and a conditional format to a PivotTable.
Use an imported text file as the basis for a PivotTable.
Create, modify, and update a PivotChart.
:: Course 50125A ::
After completing this module, students will be able to:
Create PivotTables.
Filter PivotTables to highlight specific information.
Edit and format PivotTables.
Use external data to create PivotTables.
Use PivotCharts to visually represent PivotTable data.
Module 3: Macros
This module explains how to create and run macros.
Lessons
Introducing Macros
Creating and Modifying Macros
Running Macros When a Button Is Clicked
Running Macros When a Workbook Is Opened
Lab : Macros
Examine, step through, and run a macro.
Record, save, and run a macro.
Run macros from the Quick Access Toolbar and by clicking a workbook shape.
Run a macro when someone opens a workbook.
After completing this module, students will be able to:
Move through a macro a step at a time.
Create and modify macros.
Run macros in various ways.
Module 4: Office Document Recycling
This module explains how to use Office documents in Excel workbooks and how to use Excel data in other Office programs.
Lessons
Including Office Documents in Worksheets
Storing Workbooks as Parts of Other Office Documents
Creating Hyperlinks
Pasting Charts into Other Documents
Lab : Office Document Recycling
Link a presentation to a workbook, and edit the presentation.
Embed a workbook in a presentation, and change the formatting of the workbook.
Create two hyperlinks to different locations.
Copy and paste a chart into a presentation.
After completing this module, students will be able to:
Link other types of Office documents to Excel workbooks.
Embed Excel workbooks in other Office documents.
Create hyperlinks to and from workbooks.
Use Excel charts in other Office documents.
:: Course 50125A ::
Module 5: Collaboration
This module explains how to collaborate on Excel files by sharing files, managing comments and changes, passwordprotect workbooks and worksheets, use digital signatures, save and publish Excel data.
Lessons
Sharing Data Lists
Managing Comments
Tracking and Managing Colleagues’ Changes
Protecting Workbooks and Worksheets
Authenticating Workbooks
Saving Workbooks for the Web
Lab : Collaboration
Turn on workbook sharing, and send the workbook as an e-mail attachment.
Add, highlight, review, and delete comments.
Track, accept, and reject changes; and create a History worksheet.
Create passwords, and hide the formula.
Digitally sign a workbook.
Convert a workbook to a Web page, and publish a PivotTable to the Web.
After completing this module, students will be able to:
Share workbooks with colleagues.
Manage comments and changes made by colleagues.
Password-protect workbooks, worksheets, and cells.
Use digital signatures.
Save and publish workbook and worksheet data to the Web
Programme scope, delivery arrangements and suitability are confirmed during enquiry.