# 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
• 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.

Inquiry - Miracles of Excel Formulas and Functions