Microsoft Excel Advanced Functions

Course Details

This Advanced Functions course is designed for people who want to master complex calculations using the built in Microsoft Excel Functions.

Duration:

1 Day

Audience:

This course is designed for anyone responsible for creating advanced solutions, including power users, project managers and analysts.

Prerequisites:

For best results, participants should have attended or be familiar with all the topics covered in the Excel Intermediate course including working with named ranges.

Excel 2016

Logical Functions

This session covers using the logical functions. IF() is one of the most useful functions, but it is not the only one.

  • IF, COUNTIF, COUNTIFS
  • SUMIF, SUMIFS
  • AND, OR and NOT

Lookup and Reference Functions

In this session you will learn how to search for and extract important data. There are three different types - lookup functions find and copy data from a particular cell; reference functions retrieve more general information like how many rows or columns are in a range.

  • VLOOKUP, HLOOKUP
  • MATCH
  • INDEX
  • CHOOSE
  • OFFSET
  • INDIRECT
  • COLUMN, ROW

Date and Time Functions

If you need to calculate with dates and times, Excel provides a number of useful functions.

  • DATE, DAY, MONTH, YEAR
  • HOUR, MINUTE, SECOND
  • WEEKDAY
  • WORKDAY
  • NETWORKDAYS
  • WEEKNUM
  • DATEDIF
  • EOMONTH
  • EDATE

Text Functions

Excel gives you specialised functions to manipulate text rather than performing numerical calculations. You may want to replace a character in a word, capitalise a name, or count the number of letters in a cell.

  • ONCATENATE, TRIM,
  • VALUE, LEN
  • LEFT, RIGHT, MID
  • FIND, SEARCH

Auditing Functions

These functions let you retrieve information about the type of content found in any particular cell. Most will be used in conjunctions with other functions.

  • ISBLANK
  • ISNUMBER, ISTEXT
  • ISERROR
  • ISLOGICAL
  • ISFORMULA

Arrays

  • Creating an array

The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the way the functions's arguments are entered.

  • SUMPRODUCT

Excel 2013

Logical Functions

This session covers using the logical functions. IF() is one of the most useful functions, but it is not the only one.

  • IF, COUNTIF, COUNTIFS
  • SUMIF, SUMIFS
  • AND, OR and NOT

Lookup and Reference Functions

In this session you will learn how to search for and extract important data. There are three different types - lookup functions find and copy data from a particular cell; reference functions retrieve more general information like how many rows or columns are in a range.

  • VLOOKUP, HLOOKUP
  • MATCH
  • INDEX
  • CHOOSE
  • OFFSET
  • INDIRECT
  • COLUMN, ROW

Date and Time Functions

If you need to calculate with dates and times, Excel provides a number of useful functions.

  • DATE, DAY, MONTH, YEAR
  • HOUR, MINUTE, SECOND
  • WEEKDAY
  • WORKDAY
  • NETWORKDAYS
  • WEEKNUM
  • DATEDIF
  • EOMONTH
  • EDATE

Text Functions

Excel gives you specialised functions to manipulate text rather than performing numerical calculations. You may want to replace a character in a word, capitalise a name, or count the number of letters in a cell.

  • ONCATENATE, TRIM,
  • VALUE, LEN
  • LEFT, RIGHT, MID
  • FIND, SEARCH

Auditing Functions

These functions let you retrieve information about the type of content found in any particular cell. Most will be used in conjunctions with other functions.

  • ISBLANK
  • ISNUMBER, ISTEXT
  • ISERROR
  • ISLOGICAL
  • ISFORMULA

Arrays

  • Creating an array

The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the way the functions's arguments are entered.

  • SUMPRODUCT

Excel 2010 / Excel 2007

Logical Functions

This session covers using the logical functions. IF() is one of the most useful functions, but it is not the only one.

  • IF, COUNTIF, COUNTIFS
  • SUMIF, SUMIFS
  • AND, OR and NOT

Lookup and Reference Functions

In this session you will learn how to search for and extract important data. There are three different types - lookup functions find and copy data from a particular cell; reference functions retrieve more general information like how many rows or columns are in a range.

  • VLOOKUP, HLOOKUP
  • MATCH
  • INDEX
  • CHOOSE
  • OFFSET
  • INDIRECT
  • COLUMN, ROW

Date and Time Functions

If you need to calculate with dates and times, Excel provides a number of useful functions.

  • DATE, DAY, MONTH, YEAR
  • HOUR, MINUTE, SECOND
  • WEEKDAY
  • WORKDAY
  • NETWORKDAYS
  • WEEKNUM
  • DATEDIF
  • EOMONTH
  • EDATE

Text Functions

Excel gives you specialised functions to manipulate text rather than performing numerical calculations. You may want to replace a character in a word, capitalise a name, or count the number of letters in a cell.

  • ONCATENATE, TRIM,
  • VALUE, LEN
  • LEFT, RIGHT, MID
  • FIND, SEARCH

Auditing Functions

These functions let you retrieve information about the type of content found in any particular cell. Most will be used in conjunctions with other functions.

  • ISBLANK
  • ISNUMBER, ISTEXT
  • ISERROR
  • ISLOGICAL
  • ISFORMULA

Arrays

  • Creating an array

The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the way the functions's arguments are entered.

  • SUMPRODUCT

Event Properties

Event Date 26-10-2017
Event End Date 26-10-2017
Individual Price $495.00
Course Code XLADF
Location
Corporate Training Solutions Australia
Level 19, Waterfront Place, 1 Eagle St Brisbane, QLD 4000 Australia
Corporate Training Solutions Australia
We are no longer accepting registration for this event

Group Rate

#Registrants Rate/Person($)
3 470.25
4 445.50
$495.00

Location Map

Share this event: