Microsoft Excel 2007 Level 3 Advanced

1 day

Course Overview

Now that you have a firm foundation of basic and intermediate skills you are ready to advance your training in Microsoft Office Excel 2007. You can run calculations on data, sort and filter numeric data and more. This course will further your knowledge with more advanced Excel options such as automating common tasks, advanced analysis on more complex data sets, worksheet collaboration and sharing Excel data across other applications.

Course Objective

Learn to automate common tasks in Excel, apply advanced analysis to complex data sets, collaborate with others using Excel worksheets, and share Excel data across other applications.

Target student

This course is for the student who seeks the skills to create macros, Excel collaboration, worksheet data auditing and analysis, merging multiple data sources, and data import and export. Additionally, this course may be used to prepare for the Microsoft Certified Application Specialist exam in Microsoft Office Excel 2007, and has an established basic knowledge of Excel data management.

Prerequisites

Systematix courses, Microsoft Office Excel 2007 Level 1, Microsoft Office Excel 2007 Level 2, are recommended or similar knowledge and experience.

Course Outline

Streamlining Workflow

  • Create a Macro
  • Edit a Macro
  • Apply Conditional Formatting
  • Add Data Validation Criteria
  • Update a Workbook’s Properties
  • Modify Excel’s Default Settings

Collaborating with Others

  • Protect Files
  • Share a Workbook
  • Set Revision Tracking
  • Review Tracked Revisions
  • Merge Workbooks
  • Administer Digital Signatures
  • Restrict Document Access

Auditing Worksheets

  • Trace Cells
  • Troubleshoot Errors in Formulas
  • Troubleshoot Invalid Data and Formulas
  • Watch and Evaluate Formulas
  • Create a Data List Outline

Analyzing Data

  • Create a Trendline
  • Create Scenarios
  • Perform What-If Analysis
  • Perform Statistical Analysis with the Analysis ToolPak

Working with Multiple Workbooks

  • Create a Workspace
  • Consolidate Data
  • Link Cells in Different Workbooks
  • Edit Links

Importing and Exporting Data

  • Export Excel Data
  • Import a Delimited Text File

Using Excel with the Web

  • Publish a Worksheet to the Web
  • Import Data from the Web
  • Create a Web Query

Structuring Workbooks with XML

  • Develop XML Maps