2 / 8
Miracles of Excel Formulas and Functions

# Knowledge of Microsoft Excel Intermediate is essential with the follow pre-requisites:

• Able to switch between task applications
• Able to “Create”, “Save”, “Open”, and “Print” spreadsheet
• Able to use the “Undo” function
• Insert and delete Row & Column
• 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

## Methodology

• This program will be conducted with interactive lectures and hands-on application for each participant.
• The Instructor will demonstrate all techniques using simple but realistic examples that enable participants apply what they learnt into their work.
• Sessions will include discussions, practical class work and Q & A session.
• Each participant will be assigned to a PC.

## Course Syllabus

### Module 1: Cell and Range References

• Creating an Absolute Reference

### Module 2: Statistical Functions

• COUNTIF Function
• COUNTBLANK Function
• RANK Function

### Module 3:  Math and Trig Functions

• SUMIF To Add Data That Satisfy Condition
• ROUND Function
• ROUNDUP Function
• ROUNDDOWN Function

### Module 4: The wonders of Text Functions

• Changing the Case of Text
• Combining Text from Multiple Columns
• Breaking Date Field into Individual Day, Month and Year
• Trimming Off Excess Spaces in A Cell
• Replacing existing data with new value in a cell using SUBSTITUTE Function
• Converts value to text with TEXT Function

### Module 5: Logical Functions

• Hiding formula error with IFERROR function
• Using If Function to make decision
• AND function
• Create Multiple Conditions Using Nested If
• Nested IF & AND Function

### Module 6 - Lookup & Reference Formulas

• Use VLOOKUP To Find Specific Data
• VLOOKUP Array Formula
• Use MATCH to Match Data
• Use INDEX to Retrieve Data
• Nested INDEX & MATCH

### Module 7: Date & Time Formulas

• Date Calculation
• Add A Number to Current Date To Get New Dates
• Calculate a duration Between Two Dates