Manchester Office Tel:  0161 408 0435

London OfficeTel: 0203 693 3945 

 

Microsoft Excel Advanced Training Course

Microsoft  Excel Training Courses

On this Microsoft Excel training course you will work with advanced database management, database functions, data validation & protection. You will learn how to work with PivotTables and PivotCharts, use the......

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.  Call us 0161 408 0435.

 

 

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
Understanding Relative and Absolute Cell References
Using Multiple Cell References
Using 3D References
Using Array Formulas

Module 2: Working with Named Ranges
Understanding Named Ranges
Defining Named Ranges
Editing Named Ranges
Deleting Named Ranges
Using Named Ranges in Formulas

Module 3: Using Advanced Functions
Using the PMT Function
Using the FV Function
Using VLOOKUP and HLOOKUP Functions
Using IF, AND, OR Functions

Module 4: Resolving Formula Errors
Tracing Formula Precedents and Dependents
Showing Formulas
Evaluating Formulas
Setting Error Checking Options
Using Error Option Buttons
Running an Error Check

Module 5: Managing Data
Consolidating Information
Removing Duplicates
Configuring Data Validation
Transposing Data
Converting Text to Columns

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

Module 8: Using Solver
Setting up the Worksheet
Running Solver
Generating Reports and Scenarios with Solver
Modifying Constraints
Setting Solver Options

Module 9: Analyzing Data
Enabling the Analysis ToolPak
Using Data Analysis Tools
Using Goal Seek
Using Data Tables

Module 10: Advanced PivotTable Features
Creating a Basic PivotTable
Creating a Basic PivotChart
Using the PivotTable Fields Pane
Adding Calculated Fields
Sorting Pivoted Data
Filtering Pivoted Data

Module 11: Using PowerPivot
Enabling PowerPivot
Importing Access Data
Importing Excel Data
Integrating Data with Relationships
Creating a PivotTable with PowerPivot Data

Module 12: Using Macros
Recording a Macro
Writing a Macro using the Visual Basic Editor
Editing a Macro
Running a Macro
Review Questions

 

Related Courses

In House Training Courses

Interactive Dashboards Using Microsoft Excel

An Excel Dashboard provides analysis and insight into what is happening in your business. They summarise large data sets in real time. If you are required to report complex information such as...

In House Microsoft Excel VBA Training Course

By learning to write Excel VBA code, repetitive and tedious tasks in Microsoft Excel can be performed quickly and accurately. The result is increased efficiency, performance & productivity which...

Microsoft Excel Intermediate Training Course

This Microsoft Excel Intermediate training course is intended for individuals who are self taught and wish to formalise and expand their knowledge of Excel or for those who have completed the Excel...

Microsoft Excel Introduction Training Course

This course is for new or users with little Excel experience. The course is designed to introduce the basic concepts of an excel spreadsheet. We look at spreadsheet terminology and become familiar...