Microsoft® Excel Advanced Topics

Length: 1 day
Software Version:  2007/2010
Prerequisites: Familiarity with the Windows operating system.
Course Level:  Introductory through intermediate topics

Course Description:  You will automate some common Excel tasks, apply advanced analysis techniques to more complex data sets, troubleshoot errors, collaborate on worksheets, and share Excel data with other applications.

 Course Objectives:

  • enhance productivity and efficiency by streamlining the workflow.

  • collaborate with other workbook users.

  • audit worksheets.

  • analyze data.

  • work with multiple workbooks.

  • import and export data.

  • integrate Excel data with the web.

  • structure workbooks with XML.

Course Content:

Lesson 1: Streamlining Workflow

Update Workbook Properties

Create a Macro

Edit a Macro

Apply Conditional Formatting

Add Data Validation Criteria

 

Lesson 2: Collaborating with Other Users

Protect Files

Share a Workbook

Set Revision Tracking

Review Tracked Revisions

Merge Workbooks

Administer Digital Signatures

Restrict Document Access

 

Lesson 3: Auditing Worksheets

Trace Cells

Troubleshoot Invalid Data and Formula Errors

Watch and Evaluate Formulas

Create a Data List Outline

 

Lesson 4: Analyzing Data

Create a Trendline

Create Sparklines

Create Scenarios

Perform a What-If Analysis

Perform a Statistical Analysis with the Analysis ToolPak

 

Lesson 5: Working with Multiple Workbooks

Create a Workspace

Consolidate Data

Link Cells in Different Workbooks

Edit Links

 

Lesson 6: Importing and Exporting Data

Export Excel Data

Import a Delimited Text File

 

Lesson 7: Integrating Excel Data with the Web

Publish a Worksheet to the Web

Import Data from the Web

Create a Web Query