Course Length: 3 x days

What is a Dashboard?

Dashboards give you unprecedented visibility into your work. You can get a quick, visual status update on your projects or drill down into important details, all in one place. Dashboards can boost your organisation’s speed by allowing you and your team to see more, manage more, and communicate more.

Dashboards track KPIs, metrics, and other data points in one visual, central place. They give you a high-level view of work, helping you make quick decisions and keeping everyone up to date. A dashboard’s visual nature simplifies complex data and provides an at-a-glance view of current status or performance in real time.

Dashboards are made up of tables, charts, gauges, and numbers. They can be used in any industry, for almost any purpose. For example, you could make a project dashboard, financial dashboard, marketing dashboard, and more.

Course Overview:

This course will provide you with in-depth coverage of the individual functions and tools that can be used to create compelling Excel reports. We will walk through creating stunning, visually appealing and dynamic reports that are appealing as well as effective. Featuring a comprehensive review of a wide array of technical and analytical concepts, this essential guide helps you go from reporting data with simple tables full of dull numbers to presenting key information using high-impact, meaningful reports and dashboards that will wow management both visually and substantively.

Prerequisites:

  • MS Excel Advanced
  • It is also important that everyone in class has a full understanding of Tables, Pivot Tables, Conditional Formatting and Sparkline’s. As such we use the first day as a primer for the upcoming course context by covering all advanced automations and synergies between these tools.

 

Day 1:

Creating a dashboard is not the same as creating a standard table-driven analysis. It is tempting to jump right in and start building right away, but a dashboard requires far more preparation than a typical Excel report. To support this concept, the first part of day 1 is aimed at the theory behind dashboard creation.

This includes but is not limited to the following:

  • Defining the difference between Dashboards and Reports
  • How to establish user requirements for Dashboard content
  • Define Goals, Performance Measures and Audience Expectations o Determine the Layout Strategy of your Dashboard
  • Managing Data Sources effectively
  • Defining Dimensions, Filters and Drill-down mechanisms to slice and dice your dashboard output..

We investigate the purpose of data modelling and functions to create analysis tables that various dashboard components can refer to.

We also look at documenting the process to ensure that new users of the dashboard system are able to implement and modify the dashboards as needed.

We look at modifying charts and driving them with data models to make them more dynamic. This includes the use of Macros to automate what is displayed in dashboard content with easy to use interfaces and driving changes in display that can be customised to suit the user’s needs.

We also look at non-VBA related ways of automating Charts with the use of Chart Feeders and Form Controls (i.e. the use of check boxes, spin buttons and other such user controls)

The final day focuses on integration between other data sources into Excel, (i.e. Access databases) as well as integration between Word and PowerPoint.

We also look at the Power View modelling tool exclusive to Excel 2013, as a quick and easy alternative to creating customised dashboards.

The final day would then consist of a Dashboard Project that gives the delegate an opportunity to apply what they have learnt.