Microsoft Excel: Advanced Formulas and Functions

Nett Course Fee
$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.

Complex formulas and how to nest/combine functions 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

You must have attended Excel Advanced course or have been using Excel for the past three years continuously, with basic knowledge on Excel formulas and functions.

Course Objectives

Learn and understand how to use those functions in the following function categories:
- Logical Functions
- Information Functions
- Descriptive Statistical Functions
- Rounding & Math Functions
- Date Functions
- Text Functions
- Lookup & Reference Functions
- Database Functions (optional)
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