Shared Applications
Skip Navigation Links
Excel 2007 - Advanced
Period: Flexible
Duration:3 days Normal, (2 days fastrack)   Duration(Hours): 18
Venue:3 Abiola Segun Ajayi Street, Off Muri Okunola Street, V/Island, Lagos


Course Overview

The advanced level course is the highest level of all the excel courses.  Some of the topics covered are advanced data manipulation, named ranges, advanced linking of multiple worksheets, advanced formulas and functions, and Pivot Tables


You should have attended an intermediate excel course, or have acquired some experience and understanding of excel due to regular usage

Target Audience

This course is targeted at individuals (Students, Youths, and staffs of organizations) who have intermediate experience in creating and manipulating worksheets, and desire to extend their practical experience to an advanced level. 

Course Goals and Objectives

At the end of the Training, participants will be able to carry out several operations with excel, some of which are:

  • Use range names and Table Lookup Functions
  • Advanced Linking and consolidation of worksheets
  • Link worksheets to word documents
  • Perform AutoFilter and advanced filters
  • Create complex formulas and use advanced and nested functions
  • Create and modify complex charts and graphs
  • use Scenarios and Goal Seek
  • work comfortably with templates, data tables and solve problems
  • Use automatic formatting and styles
  • Create, update and delete Pivot Tables and Pivot Charts
  • Protect sheets and workbooks
  • Use macros

Course Materials

Each participant will receive a course manual (or book) and all the course examples.

Course Outline

1) Summarizing and Outlining Data

  • Grouping/ungrouping Data
  • Outlining Data
  • Using advanced Subtotals
  • Viewing Grouped and Outlined Data
  • Introduction to SUMIF function

2)  Complex Formulas and Advanced Functions

  • Text Functions (Concatenate, Find, Fixed, Trim, Replace, etc)
  • Math & Trig Functions (sum, sumif, subtotal, etc)
  • Statistical Functions (count, countif, max, min, rank, etc)
  • Lookup & Reference Functions (vlookup, transpose, index, match, hyperlink,  etc)
  • Financial (PMT, FV, etc)
  • Date & Time Functions
  • Creating Internal Links (within the same workbook) using: Paste Special, Formulas and Named Ranges

3)  Advanced Data Manipulation – Using Lookups & Data Tables

  • Creating related worksheets
  • Creating and manipulating range names
  • Using the VLOOKUP Function
  • Using Match & Index functions
  • Creating Data Tables
  • Advanced SUMIF function
  • Advanced Data Validation

4) External Linking & Sharing & Consolidation

  • Linking workbooks
  • Sharing workbooks
  • Linking to other documents (msword, powerpoint)
  • Consolidating worksheets by category or position

5) Pivot Tables and Pivot Charts

  • Create a Pivot Table
  • Format a Pivot Table
  • Update/Delete PivotTables
  • Performing calculations within a Pivot Table
  • Create and format Pivot Charts

6) Exporting and Importing Data

  • Export/Import Text Files
  • Export/Import XML files
  • Export/Import Database Files
  • Export/Import web data

8) Illustrations, Basic Charts, and Text graphics

  • Inserting Pictures, Clipart, Shapes & SmartArt
  • Column chart, Bar chart, Pie chart, etc
  • TextBox, Word Art, Object, Symbol, etc

9) Using What-If Analysis

  • Using Scenarios Manager to create different scenarios to estimate the best and worst outcomes
  • Using Goal to adjust values in order to meet future projections
  • Using solver to explore complex projections
  • Using Data Table

10) Templates

  • Using, creating, opening and editing templates
  • Create a default Template

11) Advanced Data Formatting and Conditional Formatting

  • Formatting: Cell size, visibility and organizing sheets
  • Format Painter
  • Conditional Formatting: Create, Clear, & Manage Cell Rules
  • Conditional  Formatting: Date Bars, Color Scales, Icon Sets
  • Freezing the top row, Freezing the first column, Freezing the top row and the first column at the same time

12) Advanced Data Sorting and Filtering

  • Custom Sort
  • AutoFilter and multiple queries

13) Macros and Custom Functions

  • Recording and Running a macro
  • Creating a Macro Textbox and assigning a Macro
  • Working with VBA code
  • Creating Functions

14)  Protection

  • Protecting a workbook
  • Protecting a worksheet or worksheet elements
  • Removing workbook protection
  • Allowing selective editing of a protected worksheet