Useful Formulas and Functions (Microsoft Excel 2010/2013/2016) (7.5 hrs)

Course Code: CRS-N-0041442

Get a head start in acquiring the knowledge of using formulas in daily work as well as expand Microsoft Excel knowledge. This course provides participants with the answers to Excel questions. It is recommended for Excel users at all levels (basic / intermediate / advanced).

  • Learn about the formulas and functions to improve daily work
  • Obtain new tips irrespective of the Excel version used

Basics of formula and functions

  • Formula basics
  • Using cell references
  • Copy formula without changing cell reference
  • Transpose formula
  • Using nested functions

Statistical and logical functions

  • Perform calculation using COUNTIF
  • Perform calculation using SUMIF
  • Perform calculation using AVERAGEA
  • Using IF function to prevent division by zero
  • Using IsError function to avoid error display
  • Creating multiple conditions using nested IF
  • Using logical function OR, And within IF

Lookup and reference formulas

  • Using VLOOKUP to find specific data
  • Using HLOOKUP to find values in rows
  • Using MATCH and INDEX to retrieve data

Text formulas

  • Changing case of text
  • Append text and numerical value
  • Convert imported text format into numbers
  • Break imported date field into individual columns
  • Extract text within a cell

Date and time formulas

  • Perform addition to date fields
  • Calculate difference between two dates
  • Perform calculations with time fields

Array and database functions

  • Using array formulas
  • Calculate the difference between maximum and minimum values
  • Using frequency function to count responses
  • Using database functions DSUM and DCOUNT

Efficiency tips

  • Shortening worksheets names
  • Protecting cells containing formulas
  • Using data validation
  • Displaying formula syntax
  • Using auditing tools for errors checking
  • Tracing precedent and dependent
  • Adding comments to worksheet

Participants will benefit from the hands-on practical sessions during the course.

This course is suitable for Microsoft Excel users at all levels (basic / intermediate / advanced) who desire to pick up new tips to build advanced formulas in their spreadsheets for daily work.

Prerequisite

Participants must be users of Microsoft Excel 2010/2013/2016 for at least 1 year.

Duration : 1 day (7.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $350.00
Nett Fee Incl. 7% GST : $374.50
SDF Support* : $15.00

* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.

(A course in partnership with COMAT)

Key Benefits
  • Learn about the formulas and functions to improve daily work
  • Obtain new tips irrespective of the Excel version used
Course Contents

Basics of formula and functions

  • Formula basics
  • Using cell references
  • Copy formula without changing cell reference
  • Transpose formula
  • Using nested functions

Statistical and logical functions

  • Perform calculation using COUNTIF
  • Perform calculation using SUMIF
  • Perform calculation using AVERAGEA
  • Using IF function to prevent division by zero
  • Using IsError function to avoid error display
  • Creating multiple conditions using nested IF
  • Using logical function OR, And within IF

Lookup and reference formulas

  • Using VLOOKUP to find specific data
  • Using HLOOKUP to find values in rows
  • Using MATCH and INDEX to retrieve data

Text formulas

  • Changing case of text
  • Append text and numerical value
  • Convert imported text format into numbers
  • Break imported date field into individual columns
  • Extract text within a cell

Date and time formulas

  • Perform addition to date fields
  • Calculate difference between two dates
  • Perform calculations with time fields

Array and database functions

  • Using array formulas
  • Calculate the difference between maximum and minimum values
  • Using frequency function to count responses
  • Using database functions DSUM and DCOUNT

Efficiency tips

  • Shortening worksheets names
  • Protecting cells containing formulas
  • Using data validation
  • Displaying formula syntax
  • Using auditing tools for errors checking
  • Tracing precedent and dependent
  • Adding comments to worksheet
Learning Methodology

Participants will benefit from the hands-on practical sessions during the course.

Who Should Attend

This course is suitable for Microsoft Excel users at all levels (basic / intermediate / advanced) who desire to pick up new tips to build advanced formulas in their spreadsheets for daily work.

Prerequisite

Participants must be users of Microsoft Excel 2010/2013/2016 for at least 1 year.

Course Details

Duration : 1 day (7.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $350.00
Nett Fee Incl. 7% GST : $374.50
SDF Support* : $15.00

* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.

(A course in partnership with COMAT)

Course Application

Details Registration
January 8, 2020 - January 8, 2020 (9:00 am - 5:30 pm) Closed
February 5, 2020 - February 5, 2020 (9:00 am - 5:30 pm) Closed
March 6, 2020 - March 6, 2020 (9:00 am - 5:30 pm) Closed
August 3, 2020 - August 3, 2020 (9:00 am - 5:30 pm) Register Now
September 10, 2020 - September 10, 2020 (9:00 am - 5:30 pm) Register Now
October 13, 2020 - October 13, 2020 (9:00 am - 5:30 pm) Register Now
November 4, 2020 - November 4, 2020 (9:00 am - 5:30 pm) Register Now
December 1, 2020 - December 1, 2020 (9:00 am - 5:30 pm) Register Now