Microsoft Excel 2007: Formulas and Functions

Course Overview

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

Save hours of endless web browsing for the right course, if this Microsoft training course isn't suitable, use our advanced search function and you could be attending your next Microsoft training course in 3 simple clicks.

Related Courses

• Microsoft Excel 2007: Upgrade New Features
• Microsoft Excel 2007: Introduction for Beginners (1 day)
• Microsoft Excel 2007: Visual Basic for Applications (VBA) Programming (2 day)
• Microsoft Excel 2007: Advanced (1 day)
• Microsoft Excel 2007: Intermediate (1 day)
• Microsoft Excel 2007: Intermediate (2 day)
• Microsoft Excel 2007: Advanced
• Microsoft Excel 2007: Formulae & Functions
• Microsoft Excel 2007: Charts, Pivot Tables & Macros
• Microsoft Excel 2007: Intermediate and Advanced Training Course

Audience

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

Skills Gained

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

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