Excel Formulae and Functions

This is an advanced course focusing on creating powerful, effective formulas. 20 of the top functions used by analysts are covered including 6 new functions introduced in Excel 2016.

Most users are familiar with just the tip of the iceberg in this application. This is an opportunity to expand your calculation repertoire with advanced formula techniques and functions that deal with a variety of spreadsheet data. Make this powerful application work for you.

Ideal for anyone involved in organizational data, finances or those dealing with recorded results. Confidence using absolute and mixed cell references a must!

Key content

  • Recapping nested IF and logical functions and IFERROR
  • Using the new logical functions IFS and SWITCH
  • Understanding date calculations including EDATE and WORKDAY
  • Recapping VLOOKUP and the more flexible INDEX and MATCH functions that can replace it
  • Utilising INDIRECT for concatenated cell refs and data validation
  • Using the new TEXTJOIN function and a recap of functions to manipulate text – CONCAT, FIND, LEN, REPLACE
  • Summarising data with criteria - SUMIFs and COUNTIFs and the new MAXIFS and MINIFS
  • Working with Array formulas


  • Insert a variety of these top worksheet functions
  • Understand how to 'nest' functions for maximum efficiency
  • Utilise different cell references, names and Table Structured References where appropriate


Excel Intermediate

Suitable for

This is an advanced level workshop which is suitable for those who have completed the prerequisite or who are competent with the topics covered in that workshop. Before registering, it is your responsibility to ensure that you are competent with the topics covered in the prerequisite workshop.

Workshop attendees should be able to use absolute and mixed cell references confidently and be familiar with the way that Functions can be entered.


PC and Mac computers are available on a first-come, first-served basis. If you require a Mac, please contact us so that we can make the necessary arrangements. Excel 2016 is used. Also suitable for 2013 users.


Free for University of Auckland and UniServices staff
Free for University of Auckland postgraduate students
$125+GST for others


If you are University of Auckland staff, please visit Career Tools to view session availability and register on a session.

For all others, please see our Registration page for more information on how to register or withdraw and our Cancellation Policy.

Dates, times and venues for 2018

January - March 2018

Dates will be published here in the new year. Alternatively request a custom workshop.