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

Course details
Fee £230
Days 2
Code EX3

There are currently no dates scheduled for this course.

However it can still be provided on a closed-company basis – or we may schedule it on demand. Please contact us for further information.

e-mail us
or call us on
0845 650 6500

Excel Advanced

The Excel 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

  • This course helps prepare delegates for the ECDL Module 4 - Spreadsheets examination and the Microsoft Office Specialist Excel Expert examination

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 - Working with the Data Series

  • Linear Series
  • Data Series
  • Creating a Growth Trend

Module 2 - Using Advanced Functions

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

Module 3 - Using Other Functions

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

Module 4 - Scenario and Goal Seek

  • Creating Scenarios
  • Using Goal Seek

Module 5 - Using Worksheet Protection

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

Module 6 - Using Conditional and Custom Formats

  • Applying Conditional Formats
  • Adding a Conditional Format
  • Deleting a Conditional Format
  • Creating a Custom Format

Module 7 - Using Templates

  • Working with Templates
  • Saving a Workbook as a Template
  • Editing a Template
  • Inserting a New Worksheet
  • Deleting a Template
  • Creating Default Templates

Module 8 - Consolidating Worksheets

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

Module 9 - Using Auditing Tools

  • Displaying the Formula Auditing Toolbar
  • Displaying/Removing Dependent 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 10 - Working with Views

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

Module 11 - 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 12 - Using Macros

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

Module 13 - Recording Macros

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

Module 14 - Editing Macros

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

Module 15 - Using Custom Toolbars and Menus

  • Creating a Custom Toolbar
  • Adding the Custom Button to a Toolbar
  • Assigning a Macro to a Button
  • Deleting a Custom Toolbar
  • Creating a Custom Menu

Module 16 - Creating Macro Buttons

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

Module 17 - Creating/Revising PivotTables

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

Module 18 - Exporting and Importing Data

  • Exporting Data to Other Applications
  • Exporting to XML
  • Importing Data
  • Removing the Query Definition
  • Importing Dynamic Data from the Web