Functions And Formulas In Microsoft Excel

23-24 Sep, 2024, AC Hotel Kuala Lumpur by Marriott

Register Now
PDF Brochure

IPA Training is Registered with

Learn from the Best

Theresa Morgis
Microsoft Application Trainer
 
Theresa Morgis is certified Microsoft Trainer and a certified HRDF trainer. She has more than 20 years of training experience in Microsoft products. Theresa is well versed in Microsoft Application products: MSExcel, Word, and PowerPoint (Basic toAdvance Level).
 
Her years of experience in this field has also encouraged her to develop and customize her own training materials and she occasionally customises specific course manuals upon requests.
 
Theresa has conducted training for CEO’s, vice presidents, directors, accountants, managers, secretaries, production operators and clerical staffs.
 
Her Clients include Shell Malaysia, ASTRO, Bolton Bhd, SMBC Bank, Pos Malaysia, TNB, Petronas, Honda, Ingram Micro, Maybank Bhd, Bank Simpanan Nasional, Telekom Malaysia, Nippon Oil, RHB Bank, Public Bank Bhd, CIMB, Hilton Hotel, Touch N Go, PLUS, Ansat Broadcast, Sunway University, Monash University, UNITAR, Ambank, Zuellig Pharma, HERO and more.

Venue Details

AC Hotel Kuala Lumpur by Marriott
9 Jalan Lumut, Off Jalan Ipoh, 50400 Kuala Lumpur - 50400
Phone : (603) 4042 8000

https://www.marriott.com/en-us/hotels/kulka-ac-hotel-kuala-lumpur/overview/?scid=45f93f1b-bd77-45c9-8dab-83b6a417f6fe&y_source=1_MTQ2NDU 5MDEtNDgzLWxvY2F0a W9uLndlYnNpdGU%3D

Contact us

Juliany,
03 2283 6109
juliany@ipa.com.my

Phoebe,
03 2283 6100
phoebe@ipa.com.my 

FOR CUSTOMISED IN-HOUSE TRAINING
Jane,
03 2283 6101
Jane@ipa.com.my

ADDRESS 
A-28-5, 28th Floor, Menara UOA Bangsar, 
No.5, Jalan Bangsar Utama 1, 
59000 Kuala Lumpur
www.ipa.com.my

FOCUSING ON
  • Module 1 : Working with Name and Ranges
  • Module 2 : Logical Functions
  • Module 3 : Financial Function
  • Module 4 : Text Category Function
  • Module 5 : Date and Time Functions
  • Module 6 : Lookup Functions
  • Module 7 : Math and Trig Category Functions
  • Module 8 : Statistical Category Functions
  • Module 9 : Conditional Formatting with Functions
COURSE OBJECTIVES

This course is designed to help you master the art of Excel formulas and functions. You will learn how to transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Learn the essentials of working with financial, date, and logical functions. The course will cover the following topics:

  1. Name Range
  2. Financial Function
  3. Logical Functions
  4. Text Category Functions
  5. Date and Time Functions
  6. Lookup and Reference Category Functions
  7. Math and Trig Category Functions
  8. Statistical Category Functions

AFTER ATTENDING THIS COURSE YOU WILL RETURN TO YOUR JOB…
  1. Writing powerful and dynamic formulas from scratch.
  2. Helping to automate many of your tasks which you would otherwise do manually.
  3. Building formulas to analyse dates, text fields, values and arrays.
  4. Using the IF, INDEX, MATCH, and VLOOKUP functions so you can apply them in more complex ways.
  5. Applying the latest features of excel functions such as XLOOKUP, UNIQUE function, IFS, MAXIFS and lots more effectively.
  6. Increasing productivity by working faster and more efficiently.
  7. Avoiding complicated spreadsheets.

From the above points, you can easily understand the importance of possessing the knowledge of using excel formulas and functions. It simply doesn’t matter at what position you are in the business hierarchy, excel formulas and functions assist you to work faster, be more productive at the workplace, and save time for yourself as well.

WHO SHOULD ATTEND

This course is for those who have a good basic working experience of Microsoft Excel who want to dramatically improve their function and formula skills.

Data analysts, project managers, revenue managers, finance managers, human resources and office workers who use Excel regularly and wish to have an in-depth dive on critical and essential Excel functions and formulas with the aim of using and applying the correct tools and functions efficiently.

*Prerequisite: To ensure your success, we recommend that you have a good basic working knowledge of Excel. Knowl edge of basic formulas is definitely recommended. Participants can obtain this level of skill through our Excel Foundation, Intermediate and Advance courses.


METHODOLOGY

This course is Instructor led, group-paced, hands-on classroom-training with activities. Manuals are provided for each student for after-class reference.

DAY 1
9:00

MODULE 1: WORKING WITH NAME AND RANGES

  • Creating Names Using the Define Name Dialog Box
  • Creating Names Using the Name Box
  • Rules For Naming Name
  • Creating Names Automatically
  • Using Names in Formulas
  • Naming Constant
  • Deleting Names

10.45

MODULE 2: LOGICAL FUNCTIONS

  • • What is a Logical Function?
    • IF Function
    • Nested IF function
    • IFS (MS Office 2019 and above only)
    • AND & OR
    • IfError
12:00

MODULE 3: TEXT CATEGORY FUNCTIONS

  • Clean
  • Concatenate
  • Excel Upper Lower and Proper Functions
  • Mid, Left & Right Functions
  • Substitute, Text & Trim
1:00 Lunch
2:00

MODULE 4: LOOKUP FUNCTIONS

  • Vlookup & Hlookup
  • Vlookup & True
  • Index & Match
 

Version 2021 and above only

  • Xlookup
  • VSTACK & HSTACK
  • Filter and Sort Function
  • Unique Function
5:00 End of Day 1
DAY 2
9:00

MODULE 5: DATE AND TIME FUNCTIONS

  • Static Dates and Times
  • Dynamic Dates and Times
  • Date
  • Days
  • Yearfrac
  • Edate
  • EOmonth
  • Weekday & WeekNumber
  • Networkdays.Int & Workday.Intl

 

MODULE 6: MATH AND TRIG CATEGORY FUNCTIONS

  • Sumif & Sumifs
  • Ceiling & Floor Functions
  • Round, Rounddown & Roundup functions
  • Subtotal
10.45

MODULE 7: FINANCIAL FUNCTION

  • SYD
  • FV
  • PMT
1:00 Lunch
2:00

MODULE 8: STATISTICAL CATEGORY FUNCTIONS

  • Count
  • Counta
  • CountBlank
  • Countif & Countifs
  • Averageif & Averageifs
  • Large & Small Functions
  • Maxifs & Minifs (Office 365 Only)
3:45

MODULE 9: CONDITIONAL FORMATTING WITH FUNCTIONS

  • Greater than formula
  • Excel formulas to highlight duplicates
  • Formulas to highlight values above or below average
5:00 End of Course