SA-EFLA-2: Excel Focus Learning Advanced

Name: SA-EFLA-2: Excel Focus Learning Advanced
Brand: Microsoft

RM600.00

This course is intended for participants who wish to gain more knowledge from the advanced level of Excel. Participants who are working with lots of formulas and create a report to understand the necessary technique on how an electronic spreadsheet works.

Focus Learning is the course that provides additional value by increasing the learning outcomes of your training efforts and providing additional performance returns on your investment. This course is conducted in a workshop format, which is training based entirely on instructor-led hands-on driven classes. Our trainers thoroughly guide and perform hands-on lab exercises with the attendees to give them the maximum learning experience possible.

Training Duration: 2 Days

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

Download Course Outline

This course is intended for participants who wish to gain more knowledge from the intermediate level of Excel. Participants who need handling databases, using filter and sorting. Those who creates Pivot Table to have in depth understanding of how an electronic spreadsheet works. Participants who need to search data using VLOOKUP and nested VLOOKUP function and performs what if analysis and consolidating data.
Upon completion of this program, participants should be able to Create simple to complex formulas and functions, like:

Validate data in a Worksheet

Filter data using Auto & Advanced Filters

Clean Duplicate Records

TRIM Function

Data Extraction Function

Change Case Functions

Use Subtotal Function

Create PivotTable

VLOOKUP Function

NESTED VLOOKUP Function

Data Consolidation

Scenario Manager

Data Table

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

Able to maneuver with the mouse – Point, Click, Drag and Double Click

Able to switch between tasks applications

Able to create a spreadsheet with simple formatting

Able to “Create”, “Save”, “Open”, and “Print” spreadsheet

Able to use the “Undo” function

Able to insert and delete Row & Column

Able to print a spreadsheet with headers and footers applied

Able to Insert, Delete and Rename a worksheet

Able to create basic formulas like addition, subtraction, multiplication, and division

Able to use basic functions like Sum, Average, Count, Max, and Min function

Able to use functions like COUNTIFS, SUMIFS, IF, Nested IF, DATEDIF, EDATE, and NETWORKDAYS function

Able to Validate data in a Worksheet

Able to apply Filter data using Auto & Advanced Filters

Able to use Clean Duplicate Records

Able to use TRIM, LEFT, MID, RIGHT, UPPER, LOWER, PROPER Functions

Able to use Subtotal and Create PivotTable
Module 1: Ensuring Database Integrity and Handling Text Data Functions
Data validation is a feature in Excel used to control what a user can enter a cell.

Topic 1: Data Validation

What Is Data Validation?

Validating Your Data

Types of Validation and Alerts

In this topic, you will learn text functions that is used to handle text within Excel. Some these functions will trim off excess spaces, combines text multiple ranges and/or strings, segregating data functions and change case function.

Topic 2: Handling Text Data

Segregate Data Using the Text to Columns Wizard

TRIM Function

SUBSTITUTE Function

CONCATENATE Function

Data Extraction Function

Change Case Functions

Module 2: Handling Database and Subtotal Function
Dynamic tables in excel are the tables where when a new value is inserted to it, the table adjust its size by itself. In this topic, you will learn how to filter records using Auto Filter. Normal or simple filter is used to only filter the selected data items.

Topic 1: Creating Table

Creating Table

Auto Filter

Styles and Quick Style Sets

Adding New Record

Creating New Field

Customizing Row Display

Table Modification Options

Summary Functions in Tables

Advance filter is used to filter selected data item like normal filter along with an additional feature (Copy filtered data in another location. Besides filter, you will learn to remove duplicate records and sorting of data.

Topic 2: Sort and Filter Data

Difference Between Sorting and Filtering

Sorting Data

Advanced Filtering

Removing Duplicate Values

The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.

Topic 3: Subtotals and Grouping Data

Creating Subtotals

Using Automatic Outlining

Grouping Data Manually

Displaying and Collapsing Levels

Module 3: Analyzing Data with PivotTable, Slicer and PivotChart
A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns.

Topic 1: PivotTable

Create PivotTable Report

Show Values as Functionality of a PivotTable

Slicers provide buttons that you can click to filter PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.

Topic 2: Slicer

Insert Slicer Dialog Box

Filtering Data

Pivot Chart in Excel is an in-built tool in Excel which helps you out to summarize selected rows and column of data in a spreadsheet. It is the visual representation of a pivot table or any tabular data which helps to summarize & analyze the datasets, patterns, and trends.

Topic 3: PivotChart

Creating PivotChart

Applying a Style to a PivotChart

Module 4: Searching Data with VLOOKUP Functions
VLOOKUP is an Excel function to look up and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. It is used as helper function in VLOOKUP.

Topic 1: VLOOKUP & MATCH Function

Using VLOOKUP Function

Finding an Exact Match with VLOOKUP

Finding an Approximate Match with VLOOKUP

Using VLOOKUP as an Array Formula

Using MATCH Function

Nested VLOOKUP and MATCH Function

Module 5: Data Consolidation and Protection
You can use Excel's Consolidate feature to combine your worksheets (located in one workbook or multiple workbooks) into one worksheet.

Topic 1: Linking, Consolidating, and Combining Data

Linking Workbooks

Consolidating Workbooks

Combining Worksheets

To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.

Topic 2: Worksheet and Workbook Protection

How to Protect your Worksheet?

How to Protect your Workbook?

Module 6: What If Analysis and Hyperlinks
Scenario Manager is a built-in Excel tool that allows users to deal with changing up to 32 variables (cells) simultaneously. A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results.

Topic 1: Exploring Scenarios

Creating a Scenario

Saving Multiple Scenarios

Creating a Scenario Summary Report

A data table is one of the What-If Analysis tools that allows you to try out different input values for formulas and see how changes in those values affect the formulas output. Data tables are especially useful when a formula depends on several values, and you would like to experiment with different combinations of inputs and compare the results.

Topic 2: Data Table

1-Input Table

2-Input Table

A hyperlink is a quick access to related information in another file, worksheet tab or on a web page.

Topic 3: Hyperlinks

Inserting Hyperlinks

Editing Hyperlinks

Using Hyperlinks in Excel

SA-EFLA-2: Excel Focus Learning Advanced

WHAT WILL YOU LEARN

OBJECTIVES

PREREQUISITES

COURSE MODULE

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