e-academy – IT training excellence in Cardiff, Newport, Bristol and South Wales

Course details
Fee £230
Days 2
Code EX073
Course dates
Feb6
Mar 
Apr 
May 
Jun 
Jul 

Click on a course date to book
e-mail us
or call us on
0845 650 6500

Excel 2007 Advanced

The Excel 2007 Advanced training course is available at our Cardiff training centre in Wales, which is also within easy reach of Newport, Bristol and South WalesMicrosoft Certified Gold Partner for Learning Solutions

Overview

This course introduces the skills necessary for working with pivot tables. Tasks include creating, revising, and formatting pivot tables, and adding pivot table fields. The course also introduces the skills necessary to utilize macros, and customize toolbars and menus. It includes tasks to use, record, edit, and debug macros. Students will also learn how to use the Personal Macro Workbook, custom functions, and custom toolbars and buttons; create a macro button and custom menus. The course also aims to introduce a range of additional features to improve the knowledge of the learner and increase his/her portfolio of spreadsheet skills

Examination information

  • There is currently no examination associated with this course

Prerequisites

Before attending this course, students must have:

  • Attended the Excel Introduction and Intermediate courses or have a very good working knowledge of the subject

Course outline

Module 1: Using Advanced Functions

  • Lookup Functions
  • Nested IF Functions
  • Using Round Function
  • Limited Precision

Module 2: Using Other Functions

  • Function Argument
  • Financial Functions
  • Logical Functions
  • Date Functions

Module 3: Working with Tables

  • Using Tables
  • Creating a Table
  • Creating a Total Row
  • Using Data Validation

Module 4: Working with Data Series

  • Creating a Linear Series
  • Creating a Data Series
  • Creating a Growth Trend

Module 5: Using Scenario and Goal Seeking

  • Using Scenario Manager
  • Creating a Summary Report
  • Using Goal Seek

Module 6: Using Worksheet Protection

  • Protecting a worksheet
  • Creating Allow-Editing Ranges
  • Deleting Allow-Editing Ranges
  • Protecting Workbook Windows
  • Unprotecting Workbook Windows
  • Assigning a Password

Module 7: Using Conditional and Custom Formats

  • Applying Conditional Formats
  • Adding a Conditional Format
  • Deleting a Conditional Format
  • Creating a Custom Format
  • Using Data Bars

Module 8: Consolidating Worksheets

  • Using Consolidated Worksheets
  • Consolidating by Category
  • Consolidating by Position

Module 9: Using Templates

  • Working with Templates
  • Saving a Workbook as a Template
  • Deleting a Template

Module 10: Working with Views

  • Using Views
  • Creating a Normal View
  • Creating a Custom View
  • Displaying a View
  • Deleting a Custom View

Module 11: Creating/Revising PivotTables

  • Creating a PivotTable Report
  • Changing the Summary Function
  • Creating a Page Field Report
  • Creating a Pivot Chart Report
  • Publishing PivotTable Reports to the Web

Module 12: Sharing Workbooks

  • Saving a Shared Workbook
  • Viewing Users Sharing a Workbook
  • Viewing Shared Workbook Changes
  • Highlighting Changes
  • Managing Conflicting Changes
  • Adding a History Worksheet
  • Reviewing Tracked Changes
  • Merging Shared Workbook Files

Module 13: Using Data Tables

  • Working with Data Tables
  • Creating a One Variable Table
  • Creating a Two Variable Table

Module 14: Using Auditing Tools

  • Displaying the Formula Auditing Toolbar
  • Displaying /Removing Dependant Arrows
  • Displaying/Removing Precedent Arrows
  • Removing all Tracer Arrows
  • Using the Trace Error Button
  • Tracing Cells Causing Errors
  • Using the Error Checking Button
  • Using the Evaluate Formula Button
  • Using the Watch Window

Module 15: Using Macros

  • Opening a Workbook Containing Macros
  • Running a Macro
  • Opening the Visual Basic Editor Window
  • Using the Visual Basic Editor Window

Module 16: Recording Macros

  • Recording a Macro
  • Using Relative References
  • Assigning a Macro to a Menu

Module 17: Editing Macros

  • Writing a New Macro
  • Copying Macro Commands
  • Editing Macro Commands
  • Typing Macro Commands
  • Running a Macro from the Code Window

Module 18: Creating Macro Buttons

  • Creating a Macro Button
  • Formatting a Macro Button
  • Moving /Sizing a Macro Button

Module 19: Exporting and Importing Data

  • Exporting data to other Applications
  • Importing Data
  • Removing the Query Definition
  • Importing Dynamic Data from the Web

Module 20: Customising Excel Preferences

  • Setting Edit Options
  • Setting Manual Calculations
  • Setting Popular Options