Excel Advanced

Days 1    Code EX133   Fee £195

Excel Advanced

Course Details

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 the Ribbon. It includes tasks to use, record, edit, and debug macros. Students will also learn how to use the Personal Macro Workbook, custom functions, and buttons; create a macro buttons. 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.

Prerequisites

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

Course Outline

Lesson 1: Using Advanced Functions

  • Using V and H Lookup Functions
  • IF Functions (Nested, And, Or and ISERROR)
  • Using Round Function
  • Limited Precision

Lesson 2: Using Other Functions

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

Module 3: Using Worksheet Protection

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

Lesson 4: Using Conditional Formatting

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

Lesson 5: Consolidating Worksheets

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

Lesson 6: Creating/Revising PivotTables

  • Creating a PivotTable Report
  • Changing the Summary Function
  • Creating a Page Field Report
  • Creating a Pivot Chart Report
  • Multiple Consolidated Ranges

Lesson 7: 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

Lesson 8: Using Macros

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

Lesson 9: Recording Macros

  • Recording a Macro
  • Using Relative References
  • Deleting a Macro

Lesson 10: Editing Macros

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

Lesson 11: Creating Macro Buttons

  • Creating a Macro Button
  • Formatting a Macro Button
  • Moving /Sizing a Macro Button
  • Adding a Macro to the QAT

Lesson 12: Exporting and Importing Data

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

Request a Call Back

WebExcel Advanced