Excel 2007 Advanced

Course Length

Full day

Prerequisites

Excel 2007 Intermediate or equivalent experience

Course Description

This class is designed for the user interested in the more advanced features of Excel. Users will learn how to use data validation, perform what-if analyses, create, run, edit, and delete macros. Custom views, formula auditing, array formulas, and PivotTables are also included.

Course Topics

  • Create a validation rule and validation list
  • Circle invalid data, validation circles, and data validation
  • Consolidate data
  • Combine duplicates and add values
  • Create, display, edit, and delete a scenario
  • Create a scenario summary report
  • Use goal seek
  • Create a one and two-variable data tables
  • Specify the macro security level
  • Enable macro content for a workbook
  • Save a workbook containing macros
  • Run, record, and delete macros
  • View the macro code
  • Create, edit, and delete a macro button on a worksheet
  • Add a macro to the quick access toolbar
  • Reset the quick access toolbar
  • Change cell references in a macro from absolute to relative
  • Create, show, and delete a custom view
  • Trace precedents and remove precedent arrows
  • Trace dependent cells & remove dependent arrows
  • Evaluate and show formulas
  • Trace an error
  • Check for common errors
  • Keep a watch on important cells
  • Create an array formula to calculate a single result
  • Change calculation options
  • Create a PivotTable
  • Change the view of the PivotTable field list window
  • Add, remove, and rearrange fields
  • Refresh a PivotTable report
  • Change the data source
  • Move, clear, and delete a PivotTable report
  • Change the number format of a data field
  • Change the summary function or custom calculation for a value field

What Participants Receive

Each participant receives a guide containing step-by-step instructions and practice exercises.