Microsoft Excel - Advanced

Who is this course for?

This course is intended for participants who wish to gain more knowledge from the intermediate level of Excel. For participants who are working with nested formulas, perform what-if analysis, consolidating data and creating Pivot Tables to have in-depth understanding of how an electronic spreadsheet works.

Pre-requisites

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

• Able to switch between task applications
• Able to create simple to complex formulas and functions
• Able to validate data in a worksheet
• Able to apply filter data using Auto & Advanced Filters
• Able to clean duplicate records

Learning outcomes / benefits

Upon completion of this program, participants should be able to:

• Create subtotal using the Subtotal Function
• Analyze data using Pivot Tables
• Perform What-If Analysis
• Use VLOOKUP function to extract data
• Nesting INDEX and MATCH Function
• Combining & consolidating data
• Record and run a macro

Key content

Module 1: Getting the most from your data

Topic A - Outlining and Grouping Data

• Using Automatic Outlining
• Displaying and Collapsing Levels
• Grouping Data Manually
• Creating Subtotals

Module 2: What-if analysis

Topic A - Using Data Analysis Tools

• Using a One or Two Input Data Table
• Using Goal Seek

Topic B - Exploring Scenarios

• What is a Scenario?
• Creating a Scenario
• Saving Multiple Scenarios
• Creating a Scenario Summary Report

Topic C - Using Solver

• Understanding Solver
• Generating reports and scenarios with Solver
• Changing Solver values
• Managing Solver constraints
• Using Solver as a Goal Seek tool

Module 3: Advanced Excel task

Topic A - Working with Array Formulas

• What are Array Formulas?
• Defining Basic Array Formulas
• Using Functions within Array Formulas
• Using the IF Function in Array Formulas
• Using IFERROR with Array Formulas

Topic B - Using the VLOOKUP Function

• Understand VLOOKUP and HLOOKUP
• Using VLOOKUP to find data
• How to find an exact match with VLOOKUP
• Finding an approximate match with VLOOKUP
• Using VLOOKUP as an Array Formula

Topic C - Using the Advanced Function

• Using INDEX Function
• Using MATCH Function
• Combining MATCH & INDEX Functions

Topic D - Linking, Consolidating and Combining Data

• Linking Workbooks
• Consolidating Workbooks
• Combining Worksheets

Module 4: Advanced chart

Topic A - Create Advanced Chart

• Dual Axis Charts
• Creating Custom Chart Templates

Module 5: Analyzing data with Pivot Table, Slicer & Pivot Charts

Topic A - Create a Pivot Table

• Create Pivot Table Dialog Box
• Pivot Table Fields Pane
• Summarize Data in a Pivot Table
• Show Values as Functionality of a Pivot Table

Topic B - Filter Data by Using Slicer

• Insert Slicers Dialog Box

Topic C - Analyzing Data with Pivot Chart

• Creating a Pivot Chart
• Applying a Style to a Pivot Chart

Module 6: Enhancing workbooks

Topic A - Customizing Workbooks

• What is a Hyperlink?
• Inserting Hyperlinks
• Editing Hyperlinks
• Formatting Hyperlinks
• Using Hyperlinks in Excel

Module 7: Macros & form controls

Topic A - Macros

• Recording Macros
• Saving Macros Enabled Workbook
• Closing and re-opening a Macro Workbook
• Security Warning Message
• Playing a Macro
• Assigning a Keystroke to a Macro

Topic B - Form Controls

• Adding a Control to a Worksheet
• Assigning a Macro to a Control
• Using Form Controls

Module 8: Publishing & protection

Topic A - Finishing Workbook

• Publishing to PDF or XPS

Topic B - Protecting Worksheet and Workbook

• Protecting Current Sheet
• Protecting Entire Workbook
• File Protection

Methodology

Informative presentation, group discussion and presentation, hands-on practical session.