About This Training Course

This 2-day Advanced Microsoft Excel Training Course is designed for people who have a good working knowledge of Microsoft Excel features, and are wanting to further their knowledge and skills using the advanced functions of MS Excel. Participants must be confident users of Excel and able to create, manage and tab between multi-sheet workbooks.

Subjects Covered

This Advanced Microsoft Excel Training Course will cover the following subjects:

Auditing Tools
• Overview of Auditing Tools
• Tracer Arrows, Dependent and Precedent Arrows
• Removing Tracer Arrows
• Tracing Cells Causing Errors
• Understanding Error Messages
• Overcoming Error Messages

Advanced Functions
• Nested IF Functions
• AND, OR functions
• Using an AND Condition with IF
• Lookup Functions - VLOOKUP, HLOOKUP, Match and Index
• String Functions
• Round Function
• Array formulas

Database/Lists
• Creating a Database List
• Adding/Removing Records
• Adding / Removing Fields
• Sorting Records by Multiple Fields
• Performing a Custom Sorts
• Sorting from Left to Right
• Using a Data Form to find and edit data
• Creating Subtotals in a List
• Removing Subtotals from a List

Advanced Filters
• Creating a Criteria
• Using a Criteria
• Showing All Records
• Using Comparison Criteria
• Using an Advanced AND or OR Conditions
• Extracting Filtered Records
• Using Database Functions

Senario & Goal Seek
• Using the Scenario Manager
• Creating, Displaying and Editing a Scenario
• Creating a Scenario Summary Report
• Using Goal Seek

Problem Solving
• Using Solver to find solutions to spreadsheet problems
• Savings a Solver Solution as a Scenario
• Restoring the Original Values
• Changing a Constraint
• Creating a Solver Report
• Using Scenarios to View Solutions

Consolidating Worksheets

Overview of Consolidating data
• Consolidating by Position or by Category

Working with Views
• Using Views
• Creating a Custom View
• Displaying a View
• Editing a View
• Deleting a View

Report Manager
• Working with the Report Manager
• Using Sheets in a Report
• Printing a Report
• Using Scenarios and Views in a Report

Creating Pivot Table Reports
• Creating a PivotTable
• Adding PivotTable Report Fields
• Editing and Refreshing a PivotTable Report
• Changing The Summary Function
• Moving Pivottable Report Fields
• Hiding And Unhiding Items
• Pivot Table Options
• Adding Multiple Data Fields
• Custom Calculations In Pivot Tables
• Creating Calculated Fields and Calculated Item
• Grouping/Ungrouping Items
• Sorting A Pivot Table
• Creating PivotCharts
• Creating Interactive PivotTables - Web
• Adding Fields To A PivotTable - Browser

Macros
• Defining Macros
• Recording and running a Macro
• Personal Workbook
• Visual Basic Editor Understanding Your Code
• Running Code In VBA Environment
• Recording a Relative Record
• Debugging a Macro
• Correcting Errors
• Assigning A Macro To A Menu
• Deleting A Macro From A Menu
• Assigning A Macro To a Button

Creating an Online Form
• Creating A Form
• Adding Controls
• Selecting Controls
• Moving A Control
• Adding Data To Controls
• Setting Up For On-Line Use

Training Course Outcomes

At the end of this Advanced Microsoft Excel Training Course, participants will have a broad knowledge of Excel

Prerequisites

Participants must be confident users of Excel who are able to create and manage multiple sheet workbooks.

Other Information

Multiple-day course sessions are conducted 8-days apart to enable participants’ greater opportunity to absorb and practice learned topics, and cement transfer of skills to their everyday job roles. Our experience is that this Advanced Microsoft Excel Training Course structure assists with the most effective learning and skills retention.

If you need more information contact Startnextweek.com.au on 1300 728 102