Microsoft Excel: Advanced Formulas & Functions Part I
"Formulas are the language of Excel; learn to speak it fluently, and you'll unlock a world of possibilities"
Join Online training Within Half day
From Your Home (Zoom Application)
DATE: 17 May 2025 (Saturday)
Duration
1 Day
time
- 9AM - 6PM
Seats Availability
- 50
Zoom Meeting
Individual Fee
RM 180
HRDCorp: RM400
- VENUE / PLATFORM
- Meals Provided?
- NO
- PC/Laptop Provided?
- NO
- oUR Instructor
Coach Asyraf
(Microsoft Office Specialist: Expert)
Lead Trainer, Data & Business Intelligence Consultant
Microsoft Office Specialist: Expert (Office 2019 & 365 Apps)
Microsoft Certified: Power BI Data Analyst Associate
Microsoft Certified Trainer (2020-2025)
PREREQUISITES
A working knowledge of Microsoft Excel.
Familiarity with basic formulas such as SUM, AVERAGE, and COUNT.
Experience using Excel spreadsheets for data entry, calculations, and formatting.
Language
50% English & 50% Bahasa Melayu
Certification
Certificate of Participation by Training Provider
Training Overview
Microsoft Excel is a crucial tool for data management and analysis, but unlocking its full potential requires mastering its advanced formulas and functions. This one-day training is designed to help participants develop proficiency in using Excel’s powerful formula capabilities to enhance accuracy, efficiency, and automation in their workflow. Through hands-on practice, participants will explore essential formula techniques, auditing tools, lookup and reference functions, logical and conditional functions, power functions, and statistical functions.
By the end of the course, attendees will be equipped with practical skills to efficiently manipulate data, troubleshoot formulas, apply logical conditions, and utilize advanced lookup methods. Whether working with large datasets or complex calculations, this training will provide the knowledge needed to optimize Excel usage for better decision-making and problem-solving.
Course Objectives
At the end of the Course, participants will be able to:
- Use formula auditing tools to trace, debug, and troubleshoot formulas effectively.
- Apply Excel shortcuts and best practices to enhance formula efficiency.
- Differentiate between absolute, relative, and mixed references in formulas.
- Utilize logical functions such as IF, AND, OR, NOT, and IFS for conditional analysis.
- Master lookup and reference functions, including VLOOKUP, HLOOKUP, and XLOOKUP.
- Implement statistical functions such as MEDIAN, MODE, RANK, LARGE and SMALL.
- Apply Power Functions for tabulating data using single and multiple criteria while preventing double counting.
- Convert formulas into values, update values dynamically, and automate calculations for efficiency.
- Explore the Formulas tab and Insert Function tool for discovering and implementing new functions.
- Improve data organization and readability using range names and formula-based structuring techniques.
Course Tentative
TIME | MODULES |
|---|---|
8:30 AM – 9:00 AM | REGISTRATION |
9:00 AM – 10:30 AM | Module 1: Formula and Function Tips and Shortcuts |
10:30 AM -12:00 PM | Module 2: Formula and Function Tools |
12:00 PM – 1:00 PM | Module 3: Conditional Functions |
1:00 PM – 2:00 PM | LUNCH HOUR |
2:00 PM – 3:00 PM | Module 4: LookUp and Reference Functions |
3:00 PM – 4:00 PM | Module 5: Power Functions |
4:00 PM – 5:00 PM | Module 6: Statistical Functions |
Note: The course duration is a guideline. Course topics and duration may be modified by the instructor based on the knowledge and skill level of the course participant.
Who Should Attend?
This course is ideal for:
Students |
Educators
Office Administrators
Entrepreneurs and Business Owners
Government/Business Professionals
View Course Outline
- Displaying and highlighting formulas
- Using Auditing tools
- Using entire row and column references
- Copying column formulas instantly
- Converting formulas to values with a drag
- Updating values without formulas
- Debugging formulas with the F9 key
- Enhancing readability with range names
- Tabulating data from multiple sheets
- Hierarchy of operations in formulas
- Formulas tab for locating functions
- Insert Function for learning about unfamiliar functions
- Extending the capabilities of AutoSum
- Absolute and relative references
- Using mixed references in formulas
- Using autocalculate in the status bar
- IF logical tests
- Expanding nested IF statements
- AND, OR, and NOT functions with IF
- Using IFS for multiple conditions
- Looking up information with VLOOKUP and HLOOKUP
- Finding approximate matches with VLOOKUP and HLOOKUP
- Finding exact matches with VLOOKUP
- Nesting lookup functions
- Using VLOOKUP with large tables
- Finding table-like information within a function using the CHOOSE function
- Using the SWITCH function for formula-embedded selection
- Locating data with the MATCH function
- Retrieving information by location using the INDEX function
- Using MATCH and INDEX functions together
- Tabulating data using a single criterion
- Tabulating data using multiple criteria
- Using MAXIFS and MINIFS
- Preventing double counting
- Finding the middle value with MEDIAN and most common value with MODE
- Ranking data without sorting with RANK and RANK.EQ
- Finding the largest and smallest values with the LARGE and SMALL functions
- Tabulating blank cells with the COUNTBLANK function
- Using COUNT, COUNTA, and the status bar
Training Packages
e-Books
Exercise Files
Tutorial Videos
Lifetime Group Support
Training Video Recording
How to Register
*** Note: Course content and schedule are subject to minor adjustments based on the needs and skill levels of participants.
