Microsoft Excel: Advanced Formulas and Functions

Nett Course Fee
$545

Please be advised that as of 1st January 2025, there will be changes to both course fees and subsidies available.

Nett Course Fee (effective 1st January 2025)
$654

Course Description

Microsoft Excel: Advanced Formulas and Functions will cover how to perform advanced searching and data retrieval with Lookup functions, creation of statistic reports with criteria applied using Statistical functions, extraction of required text from given string using Text functions, and many more.

The course focuses on practical examples that will help users easily transit to using these formulas and functions in real-world scenarios. Participants will be shown some of the most challenging formulas and functions in Excel and how to put them to their best use.

Example like how to do an enhanced searching in LookUp Functions. Complex formulas in Excel will be taught too.

Prerequisites

To enrol in this course, you should possess the following:

(≥ 16 years old); or
1 year working experience

Course Objectives

Learn and understand how to use those functions in the following function categories:

IFs Functions
– Information Functions & Statistical Functions
– Math Functions & Date Functions
– Text Functions
– Lookup and Reference Functions
– Database Functions
– Boolean Logic
Learn how to mix and match functions
Learn how to solve real-life problem using functions
Learn how to automate your work using functions
Learn how to analyze problems and derive solutions
Strengthen your Excel skills with formulas and functions
Increase productivity by automating your work

Course Outline

The Logical and IFs Functions

IF, OR, AND, NOT
COUNTIFS, SUMIFS, AVERAGEIFS

Information Functions

IFERROR, ISERROR,
ISBLANK, ISNUMBER, ISTEXT
TYPE

Statistical Functions

MEDIAN, LARGE, SMALL

Math Functions

ROUND, ROUNDUP, ROUNDDOWN
CEILING, FLOOR, INT
TRUNC, MOD, SUMPRODUCT

Date Functions

DAY, DAYS, MONTH, YEAR
WEEKDAY, WEEKNUM, DATEVALUE
NETWORKDAYS, WORKDAY, EDATE, EOMONTH, DATE

Text Functions

TEXT, VALUE, TRIM, LEN
LEFT, RIGHT, MID
FIND, SEARCH, REPLACE, SUBSTITUTE

Lookup and Reference Functions

VLOOKUP, HLOOKUP, CHOOSE
MATCH, INDEX, OFFSET

Database Functions (optional)

DCOUNT, DCOUNTA, DGET
DAVERAGE, DSUM, DMIN, DMAX

This course explores the potential of Excel’s PivotTables as a robust analytical tool, teaching how to effectively summarize, sort, count, chart, and navigate data complexity, ultimately enabling the creation of PivotTables from various sources, incorporating calculated fields, result filtering, and formatting for improved readability.

Course Duration:
1 Day (9.00am – 5.00pm)

View course details here

Microsoft Excel: 99 Pro Hacks and Tips

Uncover the abundance of lesser-known productivity hacks within Microsoft Excel to enhance efficiency, avoid common pitfalls, and unlock the application’s full potential for a more effective work experience.

Course Duration:
1 Day (9.00am – 5.00pm)

View course details here