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

Objectives

  • 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

Prerequisite

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.

Notes

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.

Cost

Free for University of Auckland and UniServices staff

Registration

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

All others, please see our Registration page for more information.

Dates, times and venues for scheduled workshops 2018

Monday 9 July 2018, 1-4pm - CANCELLED
Room 305, Level 3, 49 Symonds Street, Auckland

Monday 30 July 2018, 1-4pm
Room 305, Level 3, 49 Symonds Street, Auckland

Wednesday 19 September 2018, 9am-12pm
Room 305, Level 3, 49 Symonds Street, Auckland

Thursday 11 October 2018, 1-4pm
Room 305, Level 3, 49 Symonds Street, Auckland