Excel 2007 Advanced

CW-052C Excel 2007 Advanced

Synopsis: Some features presented in this course are data consolidation, what-if analysis, collaboration, macros, templates and styles.   Prior to taking this class students should understand the use of large or multiple worksheets, advanced charting and conditional formatting, data table design and management of files.

Prerequisite:Successful completion of Excel 2007 Intermediate class or equivalent experience.

Length of Course: Self-paced, average 12-18 hours per course

Required Text: Clackamas Community College custom courseware will be provided at the first class.

Chapter 1 Data Consolidation, Links, and Formula Auditing

  1. Consolidate data from multiple worksheets
  2. Define the three-dimensional workbook
  3. Create three-dimensional workbook
  4. Link workbooks
  5. Create the documentation worksheet
  6. Restrict values to a drop-down list
  7. Validate data
  8. Audit formulas
  9. Set up a Watch Window

Chapter 2 What-If Analysis

  1. Create a one-variable data table
  2. Create a two-variable data table
  3. Solve problems with Goal Seek
  4. Use Scenario Manager
  5. Load the Solver Add-In
  6. Solve problems with Solver

Chapter 3 Collaboration and Workbook Distribution

  1. Track changes
  2. Enable simultaneous changes by multiple users
  3. Save workbooks in different formats
  4. Copy data to Word and PowerPoint
  5. Finalize documents
  6. Prepare workbooks for distribution
  7. Understand Excel options
  8. Work with the Quick Access Toolbar
  9. Add add-ins

Chapter 4 Templates, Styles, the Web, and Macros

  • Work with existing templates, themes, and styles
  • Create and apply custom styles for advanced formatting
  • Create and use a template
  • Create a Web page
  • Create Web queries
  • Create a macro with the Macro Recorder
  • Create macro buttons
  • Work with macro security
  • Understand the basics of VBA