Excel Dashboard Reporting

Become a data visualisation expert and master the best ways to summarise and present data by creating dashboards in Excel.

Dashboards are typically graphical in nature, providing visualizations that help focus attention on key trends, comparisons, and exceptions. 

Developing a dashboard consists of three main stages – gathering the raw data, creating summaries of the data to be displayed and then linking these to the display sheet. Excel elements such as Tables for data storage, Pivot Tables and Pivot Charts for basic analysis, custom charts for more specific impact, and Sparklines and conditional formatting are all combined to create the single page report. 

By using the best techniques for these stages your dashboard should be easy to update and effective.

Key content

  • Review of data gathering options – including Get and Transform
  • Develop dashboards based on the content of a number of Pivot Tables and Charts
  • Understand the Pivot Table Data Model for analysing data stored in multiple tables versus the use of VLOOKUP functions to ‘combine’ data  
  • Use the GETPIVOTDATA function to retrieve values from a Pivot Table 
  • Use linked Slicers for dynamic summaries
  • Use formulas in Chart Staging areas
  • Design appropriate graphical displays – charts, conditional formats and sparklines
  • Concatenate useful dynamic label

Objectives

  • Use more advanced Pivot Table skills to support a Dashboard display 
  • Understand options to present data visually – charts, sparklines and conditional formatting 
  • Basic Dashboard design principles

Prerequisites

Excel Pivot Tables

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 Offie 2016 Intermediate Excel: Pivot Tables and Advanced Charts

Suitable for

This is an advanced level workshop suitable for those who have completed the Excel Pivot Tables course, 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 only. Mac Excel 2011 or 2016 does not support a number of the features used in this workshop. 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.

For 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

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

Thursday 2 May 2019, 1-4pm
Room 305, Level 3, 49 Symonds Street, Auckland

Monday 24 June 2019, 1-4pm (FULL)
Room 305, Level 3, 49 Symonds Street, Auckland

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.