Course Overview:

This intensive training course has been designed to provide delegates with a solid understanding of advanced Excel 2007, 2010, 2013 or 2016 tools and concepts in order to increase their knowledge and productivity.

Course Objectives:

Users with a good understanding of Microsoft Excel 2007, 2010, 2013 or 2016 who want to expand on their capabilities in a short space of time. Delegates should have a good working knowledge and understanding of the concepts covered in the Introduction and Intermediate courses.

• Grouping & Outlining and Subtotals
• What-If Analysis
• PivotTables
• Charting Pivoted Data
• Advanced Excel Tasks
• Creating HLOOKUP and VLOOKUP Functions
• Introduction to Macros in Excel

Course Outlines:

MODULE 1:  Grouping & Outlining and Subtotals

Learning outcomes:

Useful Excel functions to summarize a spreadsheet making the data easier to identify and explain.

Topics covered:

• Outlining and Grouping Data
• Using the Subtotals Tool

MODULE 2:  What-If Analysis

Learning outcomes:

Tools designed to help with forecasting and finding solutions to complicated problems involving your spreadsheet’s data and formulas.

Topics covered:

• Exploring Scenarios
• Goal Seek and Data Tables
• Using Solver

MODULE 3:  PivotTables

Learning outcomes:

 Creating Pivot Tables in order to display and summarise large quantities of data effectively.

Topics covered:

• Getting Started with PivotTables
• Displaying Data in a PivotTable
• Formatting a PivotTable
• Using the Classic PivotTable Layout
• Slicers

MODULE 4:  Charting Pivoted Data

Learning outcomes:

Introducing Pivot Charts, adding specialist pivot charts to the already excellent Pivot Table feature.

Topics covered:

• Getting Started with PivotCharts
• Using the PivotChart Tools Tabs
• Formatting a PivotChart

MODULE 5:  Advanced Excel Tasks

Learning outcomes:

Here we will look at sharing Excel files along with adding hyperlinks and Custom AutoFill Lists.

Topics covered:

• Excel and Hyperlinks
• Using Custom AutoFill Lists
• Sharing Workbooks

MODULE 6:  Creating HLOOKUP and VLOOKUP Functions

Learning outcomes:

Exploring the different Lookup functions, ideal add further functionality to a spreadsheet to reference particular data quickly and effectively.

Topics covered:

• Using the VLOOKUP Function
• Using the LOOKUP Function

MODULE 7:  Introduction to Macros in Excel

Learning outcomes:

Explore how to create your own Macros and introducing Visual Basic for Applications, the application Macros are created in.

Topics covered:

• Recording and Playing Macros
• Copying and Deleting Macros
• Visual Basic and Macros