Manchester Office Tel:  0161 408 0435

London OfficeTel: 0203 693 3945 

 

In House Microsoft Excel Advanced Training Course

Microsoft  Excel Training Courses

On this Microsoft Excel training course (all versions including  2007, 2010, 2013) you will work with advanced database management, database functions, data validation & protection. You will learn how to work with PivotTables and PivotCharts, use the analytical features of Excel and how to record & run Macros to automate

 

This course package includes free Training Needs Analysis (TNA) for Microsoft Excel  using Gmetrix testing software to assess training  requirements. For more details, see our TNA page or call us 0161 408 0435.

 

Versions: 2007, 2010, 2013

 

Course Type: In- House, Instructor led.

 

Duration: 1 day

 

 

Target Audience

This Microsoft Excel Advanced training course is for individuals who have the knowledge and experience to create complex spreadsheets and want to become familiar with more advanced tools & techniques as well as automating repetitive tasks. You will work with advanced database management, database functions, data validation & protection. You will learn how to work with PivotTables and PivotCharts, use the analytical features of Excel and how to record & run Macros to automate repetitive tasks

 

Course Content

(Content may vary depending on software version)

Module 1: Advanced Formula Tasks

 Module 8: Using Solver

Understanding Relative and Absolute Cell References

 Setting up the Worksheet

Using Multiple Cell References

 Running Solver

Using 3D References

 Generating Reports and Scenarios with Solver

Using Array Formulas

 Modifying Constraints

 

 Setting Solver Options

Module 2: Working with Named Ranges

 

Understanding Named Ranges

Module 9: Analyzing Data

Defining Named Ranges

Enabling the Analysis ToolPak

Editing Named Ranges

Using Data Analysis Tools

Deleting Named Ranges

Using Goal Seek

Using Named Ranges in Formulas

Using Data Tables

 

 

Module 3: Using Advanced Functions

Module 10: Advanced PivotTable Features

Using the PMT Function

Creating a Basic PivotTable

Using the FV Function

Creating a Basic PivotChart

Using VLOOKUP and HLOOKUP Functions

Using the PivotTable Fields Pane

Using IF, AND, OR Functions

Adding Calculated Fields

 

Sorting Pivoted Data

Module 4: Resolving Formula Errors

Filtering Pivoted Data

Tracing Formula Precedents and Dependents

 

Showing Formulas

Module 11: Using PowerPivot

Evaluating Formulas

Enabling PowerPivot

Setting Error Checking Options

Importing Access Data

Using Error Option Buttons

Importing Excel Data

Running an Error Check

Integrating Data with Relationships

 

Creating a PivotTable with PowerPivot Data

Module 5: Managing Data

 

Consolidating Information

Module 12: Using Macros

Removing Duplicates

Recording a Macro

Configuring Data Validation

Writing a Macro using the Visual Basic Editor

Transposing Data

Editing a Macro

Converting Text to Columns

Running a Macro

 

Review Questions

Module 6: Outlining and Grouping Data

 

Outlining Data

 

Showing and Hiding Outline Details

 

Grouping Data

 

Creating Subtotals

 

Removing Outlining and Grouping

 

 

 

Module 7: Working with Scenarios

 

Creating Scenarios

 

Loading Scenarios

 

Merging Scenarios

 

Editing Scenarios

 

Creating a Scenario Summary Report

 

Deleting Scenarios

 

Related Courses

In House Training Courses