SA-EFLA-1: Excel Focus Learning Advanced

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

RM400.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: 1 Day

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

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: 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 2: 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 3: 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