Microsoft Excel 2007 / 2010 Level 3

Course Aims:

This course is aimed at people who want expand their knowledge into some of the more advanced calculation and analytical capability of Microsoft Excel.

Course Pre-requisites:

Students who wish to attend this course must have a good knowledge of Excel 2007/2010 and be competent at working with basic formulas and functions, multiple sheets and multiple files.

Course Objectives:

On completion of this course delegates will be able to:

• Manage and enhance large worksheets
• Use Logical and Lookup functions
• Set data restrictions using data validation
• Use the Subtotal feature
• Grouping information with Outline view

Course Content:

Introduction and Objectives

Using Logical, Lookup & Round Functions

• Using the VLOOKUP/HLOOKUP Functions
• Using IF, Nested Ifs, ISERROR, AND, OR & ROUND Functions

Using Range Names

• Jumping to a Named Range
• Assigning Names
• Using Range Names in Formulas

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

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

Using Worksheet Protection

• Unlocking Cells In a Worksheet; Protecting & Unprotecting a Worksheet & Workbook, Setting Manual & Automatic Calculation

Creating/Revising PivotTables

• Creating a PivotTable Report
• Enhancing your Pivot Table
• Adding & Selecting Fields
• Refreshing a PivotTable Report
• Adding, Moving, Deleting PivotTable Report Fields

Working with Advanced Filters and Database Functions

• Use the Advanced Filter Feature to Display and Extract Data from Tables

Database Functions(Dsum, Daverage, Dcounta

Appendix – Custom Views

• Creating, Displaying and Deleting Custom Views

Action planning

Review of Programme

Related Courses

Excel 2007/2010: Level 4

Microsoft® Certified Application Specialist: Using Microsoft® Office Excel 2007/2010