Functions And Formulas In Microsoft Excel

18-19 Mar, 2024, Melia Kuala Lumpur

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: MS Excel, Word, and PowerPoint (Basic to Advance 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

Melia Kuala Lumpur
16 Jalan Imbi, 55100 Kuala Lumpur, - 55100
Phone : (603) 2785 2828

https://www.melia.com/en/hotels /malaysia/kuala-lumpur/melia-kuala-lumpur

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 (Office 365 only)
  • AND & OR
12:00

MODULE 3: FINANCIAL FUNCTION

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

MODULE 4: TEXT CATEGORY FUNCTIONS

  • Clean
  • Concatenate
  • Excel Upper Lower and Proper Functions
  • Mid, Left & Right Functions
  • Substitute, Text & Trim
3:45

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
5:00 End of Day 1
DAY 2
9:00

MODULE 6: LOOKUP FUNCTIONS

  • Vlookup & Hlookup
  • Vlookup & True
  • Xlookup (Office 2021 and above)
  • Index & Match

10.45

MODULE 7: MATH AND TRIG CATEGORY FUNCTIONS

  • Sumif & Sumifs
  • Ceiling & Floor Functions
  • Round, Rounddown & Roundup functions
  • Subtotal
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