Learn Microsoft Excel 2010, Step by Step, Level 3

50546: Learn Microsoft Excel 2010 Step by Step, Level 3

About this Course
This one-day instructor-ledcourse provides students with the skills to analyze alternative data sets, create dynamic worksheets by using PivotTables, create charts and graphics, automate repetitive tasks, work with other Microsoft Office programs, and collaborate on workbooks.

Audience Profile
This course is intended for novice information workers who want to learn advanced-level Excel 2010 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 PivotTables.
• Format PivotTables.
• Create PivotTables from external data.
• Create charts.
• Customize the appearance of charts.
• Find trends in your data.
• Summarize your data by using sparklines.
• Create dynamic charts by using PivotCharts.
• Create diagrams by using SmartArt.
• Create shapes and mathematical equations.
• Enable 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 workbooks.
• Store workbooks as parts of other Office documents.
• Create hyperlinks.
• Paste charts into other documents.
• Share workbooks.
• Manage comments.
• Track and manage colleagues’ changes.
• Protect workbooks and worksheets.
• Authenticate workbooks.
• Save workbooks for the Web.

Course Outline

Module 1: Analyze Alternative Data Sets
This module explains how to use alternative data sets to analyze the results of changes to your data.

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 : Defining an Alternative Data Set

• Create a scenario

Lab : Defining Multiple Alternative Data Sets

• Create and view multiple scenarios; summarize scenario results in a separate worksheet

Lab : Varying Your Data to Get a Desired Result by Using Goal Seek

• Use Goal Seek to determine a solution

Lab : Finding Optimal Solutions by Using Solver

• Use Solver to determine a solution

Lab : Analyzing Data by Using Descriptive Statistics

• Use the Analysis ToolPak to generate statistics

After completing this module, students will be able to:

• Define alternative data sets.
• Determine the necessary inputs to make a calculation produce a particular result.

Module 2: Create Dynamic Worksheets by Using PivotTables
This module explains how to use PivotTables to create worksheets that can be sorted, filtered, and rearranged dynamically to emphasize different aspects of the data.

Lessons

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

Lab : Analyzing Data Dynamically by Using PivotTables

• Create, edit, and pivot a PivotTable

Lab : Filtering, Showing, and Hiding PivotTable Data

• Filter a PivotTable by using multiple methods; show and hide details in a PivotTable

Lab : Editing PivotTables

• Rename and reconfigure a PivotTable; create a formula that references PivotTable data

Lab : Formatting PivotTables

• Apply a number format, PivotTable style, banded rows, and conditional formatting; create a custom PivotTable style

Lab : Creating PivotTables from External Data

• Create a PivotTable based on data you import from a text file

After completing this module, students will be able to:

• Create and edit PivotTables from an existing worksheet.
• Focus PivotTable data using filters and Slicers.
• Format PivotTables.
• Create a PivotTable with data from a text file.

Module 3: Create Charts and Graphics
This module explains how to show trends in data by creating charts to summarize a worksheet's data visually, and to use sparklines to summarize the data in a single cell. It also discusses changing the appearance of charts by changing formatting; creating a PivotChart dynamic view of data; adding shapes and mathematical equations; and creating diagrams.

Lessons

• Creating Charts
• Customizing the Appearance of Charts
• Finding Trends in Your Data
• Summarizing Your Data by Using Sparklines
• Creating Dynamic Charts by Using PivotCharts
• Creating Diagrams by Using SmartArt
• Creating Shapes and Mathematical Equations

Lab : Creating Charts

• Create, modify, and move a chart

Lab : Customizing the Appearance of Charts

• Modify the layout and style of a chart; format chart values; create and apply a chart template

Lab : Finding Trends in Your Data

• Add a trendline to a chart

Lab : Summarizing Your Data by Using Sparklines

• Create, format, and clear Line, Column, and Win/Loss sparklines

Lab : Creating Dynamic Charts by Using PivotCharts

• Create, update, modify, and filter a PivotChart

Lab : Creating Diagrams by Using SmartArt

• Create, modify, and format an organization chart

Lab : Creating Shapes and Mathematical Equations

• Create, format, reorder, and align shapes; add text and equations to shapes

After completing this module, students will be able to:

• Create a chart and customize its elements.
• Find trends in overall data.
• Summarize data using sparklines.
• Create dynamic charts.
• Create and format shapes, diagrams, and shapes containing mathematical equations.

Module 4: Automate Repetitive Tasks by Using Macros
This module explains how to run and edit macros; make frequently used macros more accessible by assigning them to a new button on the Quick Access Toolbar; and create macros that run when a workbook is opened.

Lessons

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

Lab : Enabling and Examining Macros

• Examine, step through, and run a macro

Lab : Creating and Modifying Macros

• Record, edit, save, and run a macro

Lab : Running Macros When a Button Is Clicked

• Add macro buttons to the Quick Access Toolbar; assign a macro to a shape; run a macro

Lab : Running Macros When a Workbook Is Opened

• Create and test a macro that runs automatically

After completing this module, students will be able to:

• Open, run, create, and modify macros.
• Create Quick Access Toolbar buttons and shapes in order to run macros with a single mouse click.
• Define macro security settings.
• Run a macro when a workbook is opened.

Module 5: Work with Other Microsoft Office Programs
This module explains how to include Office documents in Excel workbooks, include Excel workbooks in documents, and create hyperlinks in files.

Lessons

• Including Office Documents in Workbooks
• Storing Workbooks as Parts of Other Office Documents
• Creating Hyperlinks
• Pasting Charts into Other Documents

Lab : Including Office Documents in Workbooks

• Link a presentation to an Excel workbook and edit the presentation from within Excel

Lab : Storing Workbooks as Parts of Other Office Documents

• Embed an Excel workbook in a PowerPoint presentation

Lab : Creating Hyperlinks

• Create internal and external hyperlinks

Lab : Pasting Charts into Other Documents

• Paste an image of a chart into a PowerPoint presentation

After completing this module, students will be able to:

• Include an Office 2010 document in a worksheet.
• Store an Excel workbook as part of another Office document.
• Create hyperlinks.
• Paste and Excel chart into another document.

Module 6: Collaborate with Colleagues
This module explains how to make workbooks available to other people; manage their comments; manage changes to workbooks from multiple users; add password protection; digitally sign a workbook; and save a workbook as a Web page.

Lessons

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

Lab : Sharing Workbooks

• Share a workbook via e-mail

Lab : Managing Comments

• Add, display, review, and delete comments

Lab : Tracking and Managing Colleagues' Changes

• Track and accept changes; create a History worksheet

Lab : Protecting Workbooks and Worksheets

• Password-protect a workbook, a worksheet, and a range of cells; hide a formula

Lab : Authenticating Workbooks

• Create a digital certificate and digitally sign a workbook

Lab : Saving Workbooks for the Web

• Save a workbook as a Web page; publish a PivotTable to the Web

After completing this module, students will be able to:

• Share a workbook.
• Manage comments in workbook cells.
• Track and manage changes made by colleagues.
• Protect workbooks and worksheets.
• Digitally sign workbooks.
• Save workbooks for the Web.

Before attending this course, students must have:
Basic computer knowledge, such as keyboard and mouse skills.
Basic file-management skills. The student should know how to navigate to folders and files on a computer running Windows 7.

E-Tek is an authorized Prometric testing center, offering official Microsoft certification exams at its Secaucus, New Jersey (NJ) location