详情

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.

Miracles of Excel Formulas and Functions