Microsoft® Excel® provides comprehensive tools to help you create, analyze, and share spreadsheets. You can analyze your data with charts, PivotTable® dynamic views, and graphs. You can also post your results to the Web for universal viewing and collaboration.

Objective

To provide you with the necessary skills to:

  • Use Excel functions
  • Share workbooks with colleagues
  • Use basic and advanced graphing features
  • Use web publishing features
  • Use File protection features
  • Create and use PivotTables
  • Use the Scenario Manager, Solver, and Goal Seeker Features
  • Use Object and Data Linking capabilities

Audience

This course is designed for people seeking to gain proficiency with this powerful spreadsheet application. In addition to providing real-world skills for workbook creation, editing, and analysis, this course will help you prepare for the Excel Expert level Microsoft Office User Specialist (Microsoft Office Specialist) Certification as well.

Minimum Prerequisites

You should be familiar with using personal computers and be comfortable with the mouse and keyboard. Basic typing skills is essential as well.

What will you learn?


Module 1: Getting Started with Excel

An Overview of Excel
Navigate in Excel
Select Data
Enter Data
Save a Workbook
Obtain Help

Module 2: Modifying a Worksheet

Move and Copy Data Between Cells
Fill Cells with Series of Data
Edit Cell Data
Insert and Delete Cells, Columns, and Rows
Find, Replace, and Go To Cell Data
Spell Check a Worksheet

Module 3: Performing Calculations

Create Basic Formulas
Calculate with Functions
Copy Formulas and Functions
Create an Absolute Reference


Module 4: Formatting a Worksheet

Change Font Size and Type
Add Borders and Color to Cells
Change Column Width and Row Height
Merge Cells
Apply Number Formats
Create a Custom Number Format
Align Cell Contents
Find and Replace Formats
Apply an AutoFormat
Apply Styles

Module 5: Developing a Workbook

Format Worksheet Tabs
Reposition Worksheets in a Workbook
Insert and Delete Worksheets
Copy and Paste Worksheets
Copy a Workbook

Module 6: Printing Workbook Contents

Set a Print Title
Create a Header and a Footer
Set Page Margins
Change Page Orientation
Print a Range

Module 7: Customizing Layout

Split a Worksheet
Arrange Worksheets
Freeze and Unfreeze Rows and Columns
Hide and Unhide Worksheets

Module 8: Creating and Applying Templates

Create a Workbook from a Template
Create a Custom Template
Working with Comments
Create a Hyperlink
Use Web-based Research Tools

Module 9: Creating and Modifying Charts

Create a Chart
Format Chart Items
Change the Chart Type
Create a Diagram

Module 10: Working with Graphic Objects

Insert Graphics
Create AutoShapes
Format Graphic Objects
Change the Order of Graphic Objects
Group Graphic Objects
Move, Copy, and Resize Graphic Objects


Module 11: Calculating with Advanced Formulas

Create and Apply a Name for a Range of Cells
Calculate Across Worksheets
Calculate with Date and Time Functions
Calculate with Financial Functions
Calculate with Statistical Functions
Calculate with Lookup and Reference Functions
Calculate with Logical Functions


Module 12: Sorting and Filtering Data

Sort Data Lists
Filter Data Lists
Create and Apply Advanced Filters
Calculate with Database Functions
Add Subtotals to a Worksheet


Module 13: Using Excel with the Web

Export Excel Data
Publish a Worksheet to the Web
Import Data from the Web
Create a Web Query

Module 14: Streamlining Workflow

Create a Macro
Edit a Macro
Customize Access to Excel Commands
Apply Conditional Formatting
Add Data Validation Criteria
Update a Workbook's Properties
Modify Excel's Default Settings

Module 15: Collaborating with Others
Protect Files
Share a Workbook
Set Revision Tracking
Review Tracked Revisions
Merge Workbooks
Adjust Macro Settings
Administer Digital Signatures

Module 16: Auditing Worksheets
Trace Cell Precedents
Trace Cell Dependents
Locate Errors in Formulas
Locate Invalid Data and Formulas
Watch and Evaluate Formulas
Group and Outline Data

Module 17: Analyzing Data
Create a Trendline
Create Scenarios
Perform What-If Analysis
Develop a PivotTable© Report
Develop a PivotChart© Report
Perform Statistical Analysis with the Analysis ToolPak


Module 18: Working with Multiple Workbooks
Create a Workspace
Consolidate Data
Link Cells in Different Workbooks
Edit Links


Module 19: Importing and Exporting Data
Export to Microsoft Word
Import a Word Table
Import Text Files

Module 20: Structuring XML Workbooks
Develop XML Maps
Import, Add, and Export XML Data
Manage XML Workbooks
Apply XML View Options