Microsoft Excel: Advanced Formulas and Functions

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.

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

Related Courses

Advanced Pivot Table Techniques in Microsoft Excel

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

Microsoft Excel: Advanced Formulas and Functions

Course Duration:

2 Days (9.00am – 5.00pm)

Course Fee Details:

$545.00 (incl. of 9% GST)

SkillsFuture Credits & UTAP claimable

SDF grant entitled, $2/hr x 14hrs = $28
Eligible for Singaporeans, and PR employees

Course Code: TGS-2020503211

Course Schedule

Course NameCourse CodeVenueMarch 2024April 2024May 2024June 2024July 2024August 2024September 2024
Microsoft Excel: Advanced Formulas and FunctionsTGS-2020503211JTC Summit11-12-06-07-24-25-18-19
TGS-2020503211Mountbatten Square-25-26-03-04-05-06-

Nett Course Fee
$545.00