Microsoft Excel 2007 Level 3

Objectives
On completion of this course delegates will be able to:

Manage and enhance large worksheets
Use Logical and Lookup functions
Consolidate data on a worksheet by category and position
Set data restrictions using data validation
Use the Subtotal feature
Group information with Outline view
Create and edit pivot tables
Apply custom formats and use conditional formatting
Insert and delete comments into a worksheet
Protect the worksheet


Course Content

Using Logical, Lookup & Round Functions
Using the VLOOKUP/HLOOKUP Functions
Using IF, ISERROR, AND, OR & ROUND Functions

Using Auditing Tools
Displaying the Auditing Toolbar
Displaying/Removing Dependent Arrows
Displaying/Removing Precedent Arrows
Removing Tracer Arrows
Tracing Cells Causing Errors

Working with Outlines
Applying, Expanding & Collapsing an Outline
Modifying & Clearing Outline Settings
Using Auto Outline

Consolidating Worksheets
Consolidating By Category & Position

Using Data Validation & Subtotals
Validating Data
Creating a Custom Error Message
Removing a Data Validation
Creating Subtotals in a List

Using Conditional & Custom Formats
Applying, Changing, Adding & Deleting a Conditional Format

Working with Comments
Using Worksheet Protection
Unlocking Cells In a Worksheet; Protecting & Unprotecting a Worksheet & Workbook, Setting Manual & Automatic Calculation

Creating/Revising Pivot Tables
Creating a PivotTable Report
Adding & Selecting Fields
Refreshing a PivotTable Report
Adding, Moving, Deleting PivotTable Report Fields

Working with Advanced Filters
Use the Advanced Filter Feature to
Display and Extract Data from Tables
Dfunctions

Appendix– Custom Views
Creating, Displaying and Deleting
Custom Views