Excel Advanced

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