MICROSOFT EXCEL - ADVANCED
MICROSOFT EXCEL - ADVANCED MICROSOFT OFFICE STANDARD Microsoft Office Application Training Selangor, Malaysia, Kuala Lumpur (KL), Shah Alam Training, Workshop | Iconic Training Solutions Sdn Bhd
MICROSOFT EXCEL – ADVANCED
DURATION: 2 DAYS

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, performs what if analysis, consolidating data and creates Pivot Table to have in depth understanding of how an electronic spreadsheet works.

PREREQUISITES
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
  • Have attended Microsoft Excel – Foundation & Intermediate Level; OR
  • 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
 
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
Unit 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
Unit 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
 
Unit 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
  • Understanding 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 the INDEX Function
  • Using the MATCH Function
  • Combining the MATCH and INDEX functions
 
Topic D: Linking, Consolidating, and Combining Data
  • Linking Workbooks
  • Consolidating Workbooks
  • Combining Worksheets
 
Unit 4: Advanced Chart
Topic A: Create Advanced Chart
  • Dual Axis Charts
  • Creating Custom Chart Templates

Unit 5: Analyzing Data with PivotTable, Slicer and PivotCharts
Topic A: Create a PivotTable

  • Start with Questions End with Structure
  • Create PivotTable Dialog Box
  • PivotTable Fields Pane
  • Summarize Data in a PivotTable
  • Show Values As Functionality of a PivotTable 
Topic B: Filter Data by Using Slicers
  • Insert Slicers Dialog Box 

Topic C: Analyzing Data with PivotChart

·Creating PivotChart

·Applying a Style to a PivotChart


 
Unit 6: Enhancing Workbooks
Topic A: Customizing Workbooks
  • What is a Hyperlink?
  • Inserting Hyperlinks
  • Editing Hyperlinks
  • Formatting Hyperlinks
  • Using Hyperlinks in Excel
Unit 7: Macros and Form Controls
Topic A: Macros
  • Recording Macros
  • Saving to 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

  • What is a Form Control?
  • Adding a Control to a Worksheet
  • Assigning a Macro to a Control
  • Using Form Controls
 

Unit 8: Publishing & Protection

Topic A: Finishing Workbook

  • Publishing to PDF or XPS
Topic B: Protecting Worksheet and Workbook
  • Protecting the Current Sheet
  • Protecting an Entire Workbook
  • File Protection

METHODOLOGY
Informative presentation, Group Discussion & Presentations, Hands on Session Practical Session

Please leave your enquiry here, we will reply as soon as possible.


*only support gif, jpeg, jpg, png, pdf
Switch To Desktop Version