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

In addition, you may like to complete the following recommended online courses:

  • Microsoft Office 2016 Intermediate Excel: Macros and Advanced Queries (Date and Lookup functions)
  • Using Financial Functions and What-If Analysis in Excel 2013 (covers Financial functions not covered in this workshop)

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 2019

TBA

Other resources

  • Online Learning - access online learning from anywhere at anytime in Career Tools.
  • Online Learning Place - register for a space in our Online Learning Place to complete your online learning without distractions, in your own time and at your own pace.