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

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.

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

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

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

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.