Details

---

MIRACLES OF EXCEL FORMULAS AND FUNCTIONS

Course Duration: 2 Days

WHO IS THIS COURSE FOR?

This course targets audience who would like to explore the possibilities of Excel formulas and functions. This is also for those who wish to gain the skills and understand the formulas and functions syntax.

PRE-REQUISITES

Knowledge of Microsoft Excel Intermediate is essential with the following 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
• Print a spreadsheet with headers and footer
• Insert, Delete and Rename a worksheet
• Able to create basic formulas like addition, substraction, multiplication and division
• Able to use basic functions like Sum, Average, Count, Max and Min functions

KEY CONTENTS

Unit 1: Cell and Range References

• Creating an Absolute Reference

Unit 2: Statistical Functions

• COUNTIF Function
• COUNTBLANK Function
• RANK Function

Unit 3: Math and Trig Functions

• SUMIF to add data that satisfy condition
• ROUND Function
• ROUNDUP Function
• ROUNDDOWN Function

Unit 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
• Convert value to text with TEXT Function

Unit 5: Logical Functions

• Hiding formula error with IFERROR Function
• Using IF Function to make decision
• AND Function
• Create multiple condition using Nested IF
• Nested IF & AND Function

Unit 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

Unit 7: Date & Time Formulas

• Date calculation
• Add a number to current date to get new dates
• Calculate duration between two dates

METHODOLOGY

This program will be conducted with interactive lecture and hands on application for each participant. Session will include discussion, practical class work and Q&A session.