The Advanced Excel workbook

The WARP! Computer Training Advanced MS-Excel workbook was designed for people who have a grounding in basic spreadsheeting skills, and in particular a familiarity with Microsoft Excel. Your trainees will be lead, step-by-step, through a variety of the more advanced features of Excel including outlines and groups, simple database management and pivot tables. Exercises are illustrated with many detailed screens shots, making it easy for you to give valuable feedback about the goals your learners are trying to achieve. Courseware for Excel 2010, Excel 2007, Excel 2003, Excel XP 2002, Excel 2000 and Excel 97.

Table of contents: (Advanced Excel 2010 courseware)

Chapter What your students will learn

Worksheets, Workbooks and Workspaces
  • Switching between Worksheets in a Workbook
  • Renaming a Worksheet
  • Inserting a new Worksheet
  • Deleting a Worksheet
  • Moving a Worksheet
  • Copying a Worksheet
  • Selecting more than one Worksheet
  • Changing the default number of Worksheets in a new Workbook
  • Opening multiple Workbooks
  • Switching between open Workbooks
  • Saving a Workspace

Cell References
  • Watching how a Relative Cell Reference changes when it is moved
  • Absolute Cell References
  • Mixed Cell References
  • Changing the Cell Reference Types in a formula automatically
  • Specifying a Cell Range
  • Specifying a Cell Union
  • Specifying a 3D Reference to a cell in another workbook (External Reference)

Names, Comments and Labels
  • Introduction to Names
  • Naming a cell
  • Naming a cell range
  • Using Named cells in formulas
  • Using a Named range in a formula
  • Applying Names to formulas
  • Attaching a Comment to a cell
  • Hiding a cell Comment
  • Labeling a cell
  • Using Labels in formulas

Outlines and Groups
  • Summarizing a worksheet using an Outline
  • Clearing an Outline
  • Summarizing a worksheet using Groups
  • Grouping Rows
  • Grouping Columns

Formulas and Functions
  • Adding Formulas to a spreadsheet
  • Introduction to Logical Functions
  • IF - Logical Function
  • AND - Logical Function
  • Editing a complicated function
  • Protecting a worksheet

Error Handling
  • Attaching input Data Validation to a cell
  • Using the IS validation functions in formulas
  • Tracing a spreadsheet Error to its source - Formula Auditing

Scenario and Goal Seeking
  • Applying a Scenario to a spreadsheet
  • Adding a new Scenario to a spreadsheet
  • Using Goal Seek to solve a problem

Pivot Tables
  • Summarizing table data using a Pivot Table
  • Reorganizing the fields in a Pivot Table
  • Filtering Pivot Table data
  • Generating a Chart from Pivot Table data
  • Pivot Table Design Templates

Simple Database Management
  • Sorting a database
  • Using AutoFilter
  • Removing an AutoFilter
  • Specifying a Custom AutoFilter
  • Turning Filters off
  • Using Advanced Filters
  • Advanced Filters to copy filtered records to another worksheet
  • Viewing a database with a Form
  • Using Subtotals to summarize data
  • Hiding data using Outlines
  • Removing Subtotals from a worksheet
  • Consolidating data

Macro Basics
  • Creating simple Macros
  • Keyboard Shortcut
  • Recording a Macro
  • Saving a Macro to a Workbook
  • Running a Macro
  • Macro Security - Enabling Macros
  • Trust Center - Clearing Trusted Documents
  • Enabling Macros for this session