Excel Data Analysis

Learn to use options in Excel to summarise and analyse lists of data including subtotals, simple PivotTables, statistical functions and Excel's powerful Analysis Tools.

A key aspect to successful data analysis is understanding how to store data effectively. This workshop explores how to store data effectively and includes a variety of methods for summarising and analysing data.

Key content

  • Effective table design for data storage and analysis
  • Using Data Validation with tables and summaries to ensure accuracy
  • Formula driven conditional formatting
  • Using PivotTables to analyse data and understand their limitations
  • Recap of SUMIFS and COUNTIFS etc as an alternative to PivotTables
  • Adding subtotals where appropriate
  • New Histogram, Pareto and Box and Whisker charts in Excel 2016
  • Regression analysis and trend lines - calculated and graphical
  • Using the Analysis Tools plug in to create summary statistics


  • Understand data storage with a view to future analysis
  • Summarise lists of data using subtotals, simple PivotTables and functions
  • Access Excel's powerful Analysis Tools to provide statistical data at the click of a button


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: Creating, Editing, and Saving Workbooks 
  • Microsoft Excel 2016 Essentials: Formatting Data
  • Microsoft Excel 2016 Essentials: Formulas and Functions

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

  • Microsoft Office 2016 Intermediate Excel: Working with Data
  • Microsoft Excel 2016 Essentials: Data Presentation Strategies
  • Microsoft Office 2016 Advanced: Power Pivot Custom Formatting, Fills and Forms (for custom lists, conditional formatting and advanced filter)
  • Microsoft Office 2016 Apps and What If Analysis (covers all What-If Analysis options not covered in this workshop)

Suitable for

This is an intermediate level workshop suitable for those who have completed the 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.


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


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

Thursday 28 March 2019, 9am-12pm (FULL)
Room 305, Level 3, 49 Symonds Street, Auckland

Friday 24 May 2019, 9am-12pm (FULL)
Room 305, Level 3, 49 Symonds Street, Auckland

Monday 8 July 2019, 1-4pm
Room 305, Level 3, 49 Symonds Street, Auckland

Friday 13 September 2019, 9am-12pm
Room 305, Level 3, 49 Symonds Street, Auckland

Wednesday 6 November 2019, 9am-12pm
Room 305, Level 3, 49 Symonds Street, Auckland

Other resources

  • Excel SME Clinics - held regularly to provide you with access to the skills and expertise of our Excel Subject Matter Experts. SME Clinics give you the opportunity to ask questions, request guidance or assistance in solving a particular problem.
  • 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.