50125 - Learn Microsoft Excel 2007 Step by Step, Level 3

This one-day instructor-led course provides students with an overview of the more advanced features and functions of Microsoft Office Excel 2007.

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
• Analyse data by using descriptive statistics
• Analyse data dynamically by using Pivot Tables
• Filter, show, and hide Pivot Table data
• Edit Pivot Tables
• Format Pivot Tables
• Create Pivot Tables from external data
• Create dynamic charts by using Pivot Charts
• 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

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
• Analysing Data by Using Descriptive Statistics

Lab : Data Analysis
• Create a scenario to measure the impact of a rate increase
• Create and summarise 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 analyse data

Module 2: Pivot Tables and Pivot Charts
This module explains how to work with Pivot Tables and Pivot Charts.

Lessons
• Analysing Data Dynamically by Using Pivot Tables
• Filtering, Showing and Hiding Pivot Table Data
• Editing Pivot Tables
• Formatting Pivot Tables
• Creating Pivot Tables from External Data
• Creating Dynamic Charts by Using Pivot Charts

Lab : Pivot Tables and Pivot Charts
• Create a Pivot Table, add fields, and then pivot it
• Filter a Pivot Table, and show and hide levels of detail
• Rename and edit a Pivot Table, and then link to a Pivot Table cell
• Apply a number format, styles, banded rows, and a conditional format to a Pivot Table
• Use an imported text file as the basis for a Pivot Table
• Create, modify and update a Pivot Chart

After completing this module, students will be able to:
• Create Pivot Tables
• Filter Pivot Tables to highlight specific information
• Edit and format Pivot Tables
• Use external data to create Pivot Tables
• Use Pivot Charts to visually represent Pivot Table 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

Module 5: Collaboration
This module explains how to collaborate on Excel files by sharing files, managing comments and changes, password-protect 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

This course is intended for experienced information workers who want to learn advanced-level Excel 2007 skills.

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