Excel 2013 Advanced Formulas & Functions

Print in

Course Objective

Now a day, many jobs require advanced Excel skills. But 95% of Excel users use a mere 5% of the program’s power! Formulas make Excel smart. Without them, Excel is just a data keeping tool. But by using formulas, you can crunch data, analyze it and get answers to most complex questions. While anyone can use a simple SUM or IF formula, an advanced user would be able to seamlessly write & combine formulas like SUMIFS, SUMPRODUCT, INDEX, MATCH, VLOOKUP formulas. Apart from knowing the formulas, advanced Excel users know how to audit and debug them, and how to use which formula for which occasion (and they also know few alternatives for any given formula problem).
In Excel 2013 Advanced Formulas & Functions, participants will be shown some of the most challenging formulas and functions in Excel and how to put them to their best use. The course will cover how to perform advanced searching and data retrieval with Lookup functions, creating statistic reports with criteria applied using Statistical functions, extracting 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.

Course Outlines

  • IFs Functions
  • Information Functions
  • Statistical Functions
  • Maths Functions
  • Date Functions
  • Text Functions
  • Lookup and Reference Functions
  • Database Functions

Target Audience

  • People who have attended the Excel Advanced course
  • People who wants to advanced their skills in Excel
  • People who wants to learn how to solve problems in Excel
  • For Excel power users

Prerequisite

  • Must have attended Excel Advanced course or has been using Excel for the past five years continuously
  • Must know the basic Excel formulas and functions
  • Must know how to use Excel functions like IF, OR, AND, and nested functions

Methodology

This is a two-days hands-on course. The trainer will explain on the topic and then guide you through with step-by-step practice. You will be provided with real life scenario for individual topics so that you can apply what is being taught. After the hands-on practice on every topic, you’ll have a short Q & A session.

  • IFs Functions
    • COUNTIFS
    • SUMIFS
    • AVERAGEIFS
  • Information Functions
    • ISERROR
    • IFERROR
    • ISBLANK
    • ISNUMBER

  • Statistical Functions
    • MEDIAN
    • LARGE
    • SMALL
  • Maths Functions
    • SUMPRODUCT
    • INT
    • MOD

  • Date Functions
    • DAY
    • DAYS
    • MONTH
    • YEAR
    • WEEKDAY
    • WEEKNUM
    • DATEVALUE
    • NETWORKDAYS.INTL
    • WORKDAY.INTL
    • EDATE
    • EOMONTH
  • Text Functions
    • TEXT
    • VALUE
    • TRIM
    • LEN
    • LEFT
    • RIGHT
    • MID
    • FIND
    • SEARCH
    • REPLACE
    • SUBSTITUDE

  • Lookup and Reference Functions
    • VLOOKUP
    • HLOOKUP
    • Nested Lookup functions
    • CHOOSE
    • MATCH
    • INDEX
    • OFFSET
  • Database Functions
    • DCOUNT
    • DCOUNTA
    • DAVERAGE
    • DSUM
    • DMAX
    • DMIN
    • DGET
    • DPRODUCT

  • Problems Solving using Functions (assessment

 

For individial, you may submit your claim via http://www.skillsfuture.sg/credit.

Register online @ http://one.pa.gov.sg

SDF Applicant, please register @ https://www.skillsconnect.gov.sg

IT Centre reserves the right to reschedule the course without prior notice due to class size or unforeseen circumstances.