Excel 2007 Advanced

1 Day  Course

Objectives

By the end of the day you will know how to:

·         Use advanced functions

·         Create Lookups and data tables

·         Manage large lists of data

·         Create and use PivotTables and PivotCharts

·         Export and import data

·         Analyse data effectively

·         Create basic macros

·         Use conditional formatting

Prerequisites

You should have ideally attended our Excel Intermediate level course, or have sufficient experience and understanding of Excel, especially complex formulae.

Course Contents

Unit 1: Advanced functions

Topic A: Logical functions

Topic B: Math and statistical functions

Topic C: Financial functions

Topic D: Displaying and printing formulas

Unit 2: Lookups and data tables

Topic A: Using lookup functions

Topic B: Using MATCH and INDEX

Topic C: Creating data tables

Unit 3: Advanced list management

Topic A: Validating cell entries

Topic B: Exploring database functions

Unit 4: PivotTables and PivotCharts

Topic A: Working with PivotTables

Topic B: Rearranging PivotTables

Topic C: Formatting PivotTables

Topic D: PivotCharts

Unit 5: Exporting and importing

Topic A: Exporting and importing text files

Topic B: Exporting and importing XML data

Topic C: Querying external databases

Unit 6: Analytical options

Topic A: Goal Seek and Solver

Topic B: The Analysis ToolPak

Topic C: Scenarios

Topic D: Views

Unit 7: Macros and custom functions

Topic A: Running and recording a macro

Topic B: Working with VBA code

Topic C: Creating functions

Unit 8: Conditional formatting and SmartArt

Topic A: Conditional formatting with graphics

Topic B: SmartArt graphic