Classroom/ Online: Yes/ Yes
Scheduling Date(s):
1) Dec 04, 2024 (classroom)
2) Feb 12, 2025 (classroom)
3) Jun 04, 2025 (classroom)
4) Sep 05, 2025 (classroom)
5) Dec 05, 2025 (classroom)
Note: Please click specific date for detailed venue and course fee etc.
Unleashing the Power in Excel 2016/2019/365 Functions
Microsoft Excel offers a wide range of built-in functions to help you track your finances, investments, sales, and many more. This course will equip participants with the knowledge and skills they need for working with Excel formulas and functions. At the end of the course, participants will be able to use Excel functions more quickly, effectively and wisely.
Objective
Participants will learn how to use relative, absolute, and mixed references in formulas, and how to perform calculations using Math, Statistical, Date & Time, Text, Lookup & Reference, Database, and Logical functions. Participants will be taught how to correct formula errors using Formula Auditing Tools such as Trace Precedents and Trace Dependents, Evaluate Formula and Watch Window. Participants will also learn how to hide various common error messages using IFERROR function.
Outline
Lesson 1: Understanding Cell References
1.1 Relative Reference
1.2 Absolute Reference
1.3 Mixed Reference
1.4 Defining and Using Names in Formulas
Lesson 2: Using Text Functions
2.1 Using LEFT Function
2.2 Using RIGHT Function
2.3 Using MID Function
2.4 Using FIND Function
2.5 Using UPPER Function
2.6 Using LOWER Function
2.7 Using PROPER Function
2.8 Using TRIM Function
2.9 Using TEXT Function
Lesson 3: Using Date and Time Functions
3.1 Using TODAY and NOW Functions
3.2 Using DAY, MONTH and YEAR Functions
3.3 Using WEEKNUM Function
3.4 Using WEEKDAY Function
3.5 Using NETWORKDAYS Function
3.6 Using EDATE Function
3.7 Using EOMONTH Function
Lesson 4: Using Maths and Statistical Functions
4.1 Using ROUND Function
4.2 Using ROUNDUP Function
4.3 Using ROUNDDOWN Function
4.4 Using COUNTIF Function
4.5 Using SUMIF and AVERAGEIF Functions
4.6 Using COUNTIFS Function
4.7 Using SUMIFS And AVERAGEIFS Functions
4.8 Using LARGE and SMALL Functions
4.9 Using RANK Function
4.10 Using 3-D Reference
Lesson 5: Using Database Functions
5.1 Understanding Database Functions
5.2 Using Database Functions
Lesson 6: Using Logical Functions
6.1 Using IF Function
6.2 Using OR and AND Functions
6.3 Using IFERROR Function
 
Lesson 7: Using Lookup And Reference Functions
7.1 Using HLOOKUP Function
7.2 Using VLOOKUP Function
7.3 Using COLUMN Function
7.4 Using INDEX Function
7.5 Using MATCH Function
Lesson 8: Understanding Formula Auditing Tools
8.1 Trace Precedents and Trace Dependents
8.2 Error Checking
8.3 Evaluate Formula
8.4 Watch Window
1.1 Relative Reference
1.2 Absolute Reference
1.3 Mixed Reference
1.4 Defining and Using Names in Formulas
Lesson 2: Using Text Functions
2.1 Using LEFT Function
2.2 Using RIGHT Function
2.3 Using MID Function
2.4 Using FIND Function
2.5 Using UPPER Function
2.6 Using LOWER Function
2.7 Using PROPER Function
2.8 Using TRIM Function
2.9 Using TEXT Function
Lesson 3: Using Date and Time Functions
3.1 Using TODAY and NOW Functions
3.2 Using DAY, MONTH and YEAR Functions
3.3 Using WEEKNUM Function
3.4 Using WEEKDAY Function
3.5 Using NETWORKDAYS Function
3.6 Using EDATE Function
3.7 Using EOMONTH Function
Lesson 4: Using Maths and Statistical Functions
4.1 Using ROUND Function
4.2 Using ROUNDUP Function
4.3 Using ROUNDDOWN Function
4.4 Using COUNTIF Function
4.5 Using SUMIF and AVERAGEIF Functions
4.6 Using COUNTIFS Function
4.7 Using SUMIFS And AVERAGEIFS Functions
4.8 Using LARGE and SMALL Functions
4.9 Using RANK Function
4.10 Using 3-D Reference
Lesson 5: Using Database Functions
5.1 Understanding Database Functions
5.2 Using Database Functions
Lesson 6: Using Logical Functions
6.1 Using IF Function
6.2 Using OR and AND Functions
6.3 Using IFERROR Function
 
Lesson 7: Using Lookup And Reference Functions
7.1 Using HLOOKUP Function
7.2 Using VLOOKUP Function
7.3 Using COLUMN Function
7.4 Using INDEX Function
7.5 Using MATCH Function
Lesson 8: Understanding Formula Auditing Tools
8.1 Trace Precedents and Trace Dependents
8.2 Error Checking
8.3 Evaluate Formula
8.4 Watch Window
Who should attend
- This intermediate level course is for frequent Excel users who wish to learn how to increase their productivity in daily work with effective Excel functions.
- Participants must have some intermediate level Excel knowledge and a few years working experience using Excel.
Methodology
This is a one-day hands-on practice course
Testimonials
Went through all topics thoroughly. Trainer is with very clear instructions.
PUB
The workshop is full score! Trainer is excellent!
Ngee Ann Polytechnic
I learnt more formulas in excel that I can use for my work and it would hlep efficiency. Ms Valene is knowledgeable in this aspect. and she deliver it clearly
IP Academy
PUB
The workshop is full score! Trainer is excellent!
Ngee Ann Polytechnic
I learnt more formulas in excel that I can use for my work and it would hlep efficiency. Ms Valene is knowledgeable in this aspect. and she deliver it clearly
IP Academy
Profile of Valene Ang
Valene Ang is a Microsoft Certified Trainer (MCT) with a degree in Business Computing. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS). She has broad experience in corporate IT training and course materials development.
Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outlines and course materials, assisting corporate clients in business data analysis and providing dynamic report solutions. Her training focuses on providing practical solutions to real life Excel problems.
Valene conducted many Microsoft Office training in Singapore, Malaysia and China. Her corporate clients include NOL, PSA, IRAS, DFS, CPF, PUB, MOM, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING Singapore, Nanyang Polytechnic, Singapore Polytechnic, Republic Polytechnic, Denza (ShenZhen) and etc..
