SA-EFLA-1: Excel Focus Learning Advanced

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