SA-EXA-2: Microsoft Excel Advanced

Name: SA-EXA-2: Microsoft Excel Advanced
Brand: Microsoft

RM500.00

Master Microsot Office Excel's full potential by learning how to work with others, automate hard or repetitive activities, and create and apply elaborate formulae and functions using conditional logic.

To help you get the most out of your Excel experience, this Microsoft Office Excel Advanced course expands on the foundational and intermediate skills offered in the Excel Foundation and Excel Intermediate courses.

Training Duration: 2 Days

Certificate Of Completion Available
Group Private Class
VILT Class Available
SBL-Khas Claimable

Download Course Outline

This SA-EXA-2: Microsoft Excel Advanced 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.
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 using:

Goal Seek

Solver

Input Table

Scenarios

Use VLOOKUP function to extract data

Nesting INDEX and MATCH Function

Combining & Consolidating Data

Record and Run a Macro
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

Have attended Microsoft Excel – Foundation & Microsoft Excel - Intermediate Levels; OR

Able to switch between task applications

Able to create simple to complex formulas and functions, like:

COUNTA, COUNTIF & COUNTIFS Function

AVERAGEA, AVERAGEIF & AVERAGEIFS Function

SUMIF & SUMIFS Function

IF Functions

Nested Functions

Database Function

Able to validate data in a Worksheet

Able to apply Filter data using Auto & Advanced Filters

Able to clean Duplicate Records
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

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

Module 4: Advanced Chart
Topic A: Create Advanced Chart

Dual Axis Charts

Creating Custom Chart Templates

Module 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 Slicer

Slicers

Insert Slicers Dialog Box

Topic C: Analyzing Data with PivotChart

Creating PivotChart

Applying a Style to a PivotChart

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 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

Module 8: Publishing & Protection
Topic A: Finishing Workbook

Publishing to PDF

Topic B: Protecting Worksheet and Workbook

Protecting the Current Sheet

Protecting an Entire Workbook

File Protection
GRC-101: An Analytical Journey with Microsoft Power BI

This GRC-101: An Analytical Journey with Microsoft Power BI custom developed instructor-led adventurous workshop is designed for audiences to be equipped with the necessary knowledge and skills to kickstart their analytical journey with Microsoft Power BI.

GRC-107: Building A Data Literate Culture In Your Organization

When information becomes a valuable asset, data becomes a strategic priority. Data is becoming a competitive differentiator for top organizations as they focus on digital transformation while still in the early stages of adoption. Data serves as a significant catalyst for a company's digitalization and transformation activities.

PL-300T00: Microsoft Power BI Data Analyst Training Certification Course

This PL-300T00: Microsoft Power BI Data Analyst course will discuss the various methods and best practices that are in line with business and technical requirements for modeling, visualizing, and analyzing data with Power BI. Check out our Microsoft Power BI Training here.

SA-EXA-2: Microsoft Excel Advanced

WHAT WILL YOU LEARN

OBJECTIVES

PREREQUISITES

COURSE MODULE

Recommended Courses Upon Completion

For a private in-house, please ask us for a quotation.