SA-EFLI-1: Excel Focus Learning Intermediate

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

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

Upon completion of this program, participants should be able to Create simple to complex formulas and functions, like:

• COUNTIFS Function
• SUMIFS Function
• IF Function
• Nested IF Function
• DATEDIF Function
• EDATE Function
• NETWORKDAYS Function
• 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

Unit 1: $ Symbols in Formulas, Effective Formatting, Logical Functions and Date Functions

Module 1: $ Symbols in Formulas & Functions
An absolute reference in Excel refers to a reference that is "locked" so that rows and columns will not change when copied. A mixed reference is a reference that refers to a specific row or column.

Topic 1: How to use the $ in your Formula?

• Using Absolute Addressing formula
• Using Mixed Referencing in formula

The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criterion. The SUMIFS function allows summation of cell values that meets a single or multiple criterion.

Topic 2: Evaluate Data Using Statistical and Math Functions

• Using COUNTIFS Function
• Using SUMIFS Function

Module 2: Effective Formatting and Logical Functions
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. An IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

Topic 1: Logical Function

• IFERROR Function
• IF Function
• Nested If Function

Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

Topic 2: Conditional Formatting

• Highlight Cell Rules
• Using Data Bars
• Using Icon Sets
• Creating New Rules with Formula

Module 3: Date Formulas
In this session, you will learn date functions that is used to handle dates within Excel. Some the functions taught will be calculation between 2 dates, contract date expiry calculation and working day function.

Topic 1: Working with Date Functions

• Date Calculation
• Add A Number to Current Date to Get New Dates
• Contract Date Expiry Calculation
• Calculate a duration Between Two Dates
• EDATE Function
• NETWORKDAYS Function

SA-EFLA-2: Excel Focus Learning Advanced

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.