Course Information

Date20 May 2020, Wed
Time9:00 AM - 5:00 PM
VenueRoyal Plaza on Scotts Singapore Hotel Including Tea breaks and International Gourmet Buffet Lunch at Award winning Carousel Restaurant (Halal Certified)
FeeS$ 500 (Nett)
9767 9686 / 6278 9785

Registration is on a 1st come 1st serve basis. Register early to avoid disappointment.


I've been making full length films and documentaries for many years. We use professional equipment and…
Nay Win Tun, Video Editor
Perfect Your Elevator Pitch through Digital Storytelling
Very through and comprehensive
1 day Workshop on The Motivational Conversation - Rodney Woulfe
I will recommend this to my colleagues who have negative attitude towards their life, so that they can…
– Nanyang Technological University
1 day Influencing Difficult People in the Workplace by Ms Nancy Yeo
Nancy is very clear, engaging and insightful. She is able to tap on her vast experience to share with…
– WWW Cargo Pte Ltd
1 day Influencing Difficult People in the Workplace by Ms Nancy Yeo
‹ back to previous page
May 2020

Mastering Advanced Functions in Microsoft Excel 2016

Participants will learn to use advanced functions and formulas of Microsoft Excel 2016 to improve productivity and streamline their operational work.

This course will help you quickly master the functions that are going to give you the biggest efficiency gains. Many of the functions are considered advanced, but when you’ve finished the course, you’ll know that everything is easy once you know how. This course covers the fundamentals for each function as well as some less obvious uses for them. It’s these more advanced techniques that will really set you apart from the crowd.



Learn how to automate common tasks using functions and formulas, leverage on Excel’s advanced functionality to simplify and streamline their day-to-day work, apply advanced analysis techniques to data sets using Database functions and Lookup functions.

At the completion of this course you should be able to:

  • Use a range of text functions to covert text cases, extract characters and format dates
  • Use date and time functions to covert dates, calculate the number of days between two dates and find the end date of a project
  • Use a variety of maths and statistical functions to find the number of records that match multiple conditions, find the highest and lowest data for a given set of conditions
  • Use SUMPRODUCT function to multiplies ranges or arrays together and returns the sum of products
  • Use logical functions to test whether a person meet their sales target, find out the commission rate for each sales person based on total sales
  • Use lookup and reference functions to extract data items from a database and retrieve data from a 2-way table based on year and product name
  • Use CHHOSE function to display information based on the result retrieved from a database by VLOOKUP function
  • Use OFFSET functions to sum up the a data range in a data summary table based on the user input


Chapter 1: Text Functions

  1. Using UPPER / LOWER / PROPER Functions
  2. Using LEFT / RIGHT / MID Functions
  3. Using FIND / LEN Functions
  4. Using TRIM Function
  5. Using TEXT Function

Chapter 2: Date and Time Functions

  1. Using TODAY / NOW Functions
  2. Using DAY / MONTH / YEAR Functions
  3. Using DATE Function
  4. Using WEEKNUM Function
  5. Using WEEKDAY Function
  6. Using NETWORKDAYS Function
  7. Using WORKDAY Function
  8. Using EDATE Function
  9. Using EOMONTH Function

Chapter 3: Maths Functions

  1. Using ROUND / ROUNDUP / ROUNDDOWN Functions
  2. Using SUMIF and SUMIFS Functions
  3. Using SUMPRODUCT Function

Chapter 4: Statistical Functions

  1. Using COUNTIF / COUNTIFS Functions
  2. Using AVERAGEIF / AVERAGEIFS Functions
  3. Using LARGE / SMALL Functions
  4. Using RANK.AVG / RANK.EQ Functions

Chapter 5: Logical Functions

  1. Using IF Function
  2. Nesting IF Function
  3. Using OR and AND Functions
  4. Using IFERROR Function

Chapter 6: Database Functions

  1. Understanding Database Fundamental
  2. Defining Name for Cell and Range
  3. Using DCOUNT / DAVERAGE / DSUM / DMAX / DMIN Functions

Chapter 7: Lookup and Reference Functions

  1. Using ROW / COLUMN Functions
  2. Using HLOOKUP Function
  3. Using VLOOKUP Function
  4. Using INDEX / MATCH Functions
  5. Using CHOOSE Function
  6. Using OFFSET Function
  7. Using INDIRECT Function


  • This is an intermediate to advanced level course suitable for anyone who intend to enhance their knowledge in using various Excel functions.
  • This course is designed for senior officers, executives and decision makers in government, corporate and private organizations who want to learn how to use Excel functions effectively to increase the productivity of their work.
  • Participants must have adequate Excel knowledge and a good working experience using Microsoft Excel 2016.



“Course is in good pace and I learnt many useful features which I can apply to my work quickly.”

“Valene is an effective trainer who highlights the key points and illustrate using good examples.”
~ FairPrice


Classroom based demonstration with hands-on Exercises using Microsoft Excel 2016.

  • Participants are required to bring a laptop equipped with Windows version Microsoft Excel 2016
  • This course is NOT for MAC version Microsoft Excel users


Microsoft Excel Expert and Microsoft Certified Trainer: Valene Ang

Valene Ang is an experienced certified IT trainer with over 19 years of experience in training professionals from various organisations. She received her degree in Business Computing from the University of Southern Queensland, Australia. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS).

Valene has extensive training and course development experience. She customized many Microsoft Office training courses for corporate clients, assist them in business data analysis and provide dynamic report solutions. Her training focuses on 100% hands-on exercises as well as providing practical solutions to real life Excel related problems.

Valene conducted many Microsoft Excel, PowerPoint and Word training in Singapore, Malaysia and China. Her corporate clients include CPF, PUB, MOM, PSA, IRAS, DFS, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING, FairPrice and many more.

10 Years of successful track records

Our clients share our passion for people development. We take a long term view of relationships and the
deep and sustained impact has been greatly fulfilling. We have trained over 26,000 learners to date.

NUS - National University of Singapore
PSA - The World's Port of Call
PUB - Water for All Conserve, Value, Enjoy
Singapore Airlines
Singapore Tourism Board
Standard Chartered
WDA - Singapore Workforce Development Agency