Microsoft Excel: Advanced Formulas & Functions Part II
"Learning Excel is like learning a new language; with formulas and functions, you can communicate insights that drive decisions."
Join Online training Within Half day
From Your Home (Zoom Application)
DATE: 31 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
Strong foundation in Excel, including knowledge of basic and intermediate functions.
Experience working with formulas such as IF, VLOOKUP, HLOOKUP, and basic date/time functions
Familiarity with cell references, named ranges, and conditional formatting.
Language
50% English & 50% Bahasa Melayu
Certification
Certificate of Participation by Training Provider
Training Overview
This one-day intensive training is designed for professionals who want to deepen their understanding of advanced formulas and functions in Microsoft Excel. The course covers key mathematical, date/time, array, reference, text, and information functions, providing participants with the skills needed to solve complex data challenges efficiently. By mastering these functions, users can enhance their productivity and create more dynamic and automated spreadsheets.
Participants will explore various mathematical rounding techniques, work with powerful date and time functions, manipulate data using array formulas, reference remote cells, process text effectively, and leverage information functions for error checking and data validation. Through hands-on exercises, participants will gain practical experience in applying these advanced Excel capabilities to real-world scenarios.
Course Objectives
At the end of the Course, participants will be able to:
- Apply advanced rounding techniques using ROUND, ROUNDUP, and ROUNDDOWN functions.
- Utilize mathematical functions such as MOD, INT, and TRUNC for precise data manipulation.
- Work efficiently with date and time functions to automate calculations.
- Use NETWORKDAYS and WORKDAY functions for business date calculations.
- Implement array formulas to perform complex data analysis.
- Extract and transform data using text functions like FIND, MID, and CONCAT.
- Create dynamic drop-down lists using INDIRECT and Data Validation.
- Retrieve and reference data using OFFSET and INDIRECT functions.
- Utilize information functions for error handling and data validation.
- Enhance reporting capabilities with functions like TEXT, REPT, and LEN.
Course Tentative
TIME | MODULES |
|---|---|
8:30 AM – 9:00 AM | REGISTRATION |
9.00 AM – 10.30PM | Module 1: Math Functions |
10:30 AM -12:00 PM | Module 2: Date and Time Functions |
12:00 PM – 1:00 PM | Module 3: Array Formulas and Functions |
1:00 PM – 2:00 PM | LUNCH HOUR |
2: 00 PM – 3:00 PM | Module 4: Reference Functions |
3: 00 PM – 4:00 PM | Module 5: Text Functions |
4:00 PM – 5:00 PM | Module 6: Information 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
- Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
- Working with MROUND, CEILING, and FLOOR for specialized rounding
- Using the INT and TRUNC functions to extract integer data
- Finding the remainder with MOD and using MOD with conditional formatting
- Exploring practical uses for the RAND and RANDBETWEEN functions
- Converting a value between measurement systems with CONVERT
- Using the powerful AGGREGATE function to bypass errors and hidden data
- Using the ROMAN and ARABIC functions to display different number systems
- Understanding Excel date and time capabilities in formulas
- Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions
- Using the TODAY and NOW functions for dynamic date and time entry
- Identifying the day of the week with the WEEKDAY function
- Counting working days with the NETWORKDAYS function
- Determining a completion date with the WORKDAY function
- Tabulating date differences with the DATEDIF function
- Calculating end-of-month and future and past dates with EDATE and EOMONTH
- Extending formula capabilities with array formulas
- Counting unique entries in a range with an array formula
- Determining frequency distributions with the FREQUENCY function
- Flipping row and column orientation with TRANSPOSE
- Building analysis via regression techniques with TREND and GROWTH
- Using array formulas and the MATCH function for complex lookups
- Getting data from remote cells with the OFFSET function
- Returning references with the INDIRECT function
- Using INDIRECT with Data Validation for two-tiered pick list scenarios
- Locating and extracting data with the FIND, SEARCH, and MID functions
- Extracting specific data with the LEFT and RIGHT functions
- Using the TRIM function to remove unwanted spaces in a cell
- Using ampersands and CONCATENATE to combine data from different cells
- Using the CONCAT and TEXTJOIN to combine data from different cells
- Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
- Adjusting character content with the REPLACE and SUBSTITUTE functions
- Using the utility text functions: TEXT, REPT, and LEN
- Extracting information with the CELL and INFO functions
- Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
- Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
- Tracking and highlighting formula cells with the ISFORMULA function
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.
