Excel Formulae and Functions

Master fifteen of the top functions used by analysts today by attending this  advanced level workshop which focuses on creating powerful, effective formulas.

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 writing techniques and functions that deal with a variety of spreadsheet data. Make this powerful application work for you. Confidence using absolute and mixed cell references is a must!

Ideal for anyone involved in organisational data, finances or those dealing with recorded results.

Key content

  • Understanding date calculations including EDATE and WORKDAY
  • ‘Nesting’ functions such as IF with AND and IFERROR
  • Using VLOOKUP and the INDEX and MATCH functions that can replace it
  • Using INDIRECT for concatenated cell refs and data validation
  • Using functions to manipulate text – CONCATENATE, FIND, LEN, REPLACE
  • Summarising data with criteria - SUMIFS and COUNTIFS
  • Working with Array formulas

Objectives

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

Prerequisites

Excel Essentials

Alternatively, for University of Auckland and UniServices staff, please access the following Online learning courses to ensure you meet the minimum competency requirements for this workshop:

  • Microsoft Excel 2016 Essentials: Formulas and Functions

Suitable for

This is an advanced level workshop suitable for those who have completed Excel Essentials, prescribed online learning courses or who are competent with the prerequisite topics. Before registering for this workshop, it is your responsibility to ensure that you are competent with all the prerequisite topics.

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.

Please be aware that a Cancellation policy applies to these workshop sessions. 

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