Microsoft Excel 2016: Creating Digital Dashboards – Level 4

This course will provide you with in-depth coverage of the individual functions and tools that can be used to create compelling Excel reports.

You will:

• Analyse large amounts of data and report the results in a visually attractive and effective way.
• Use different perspectives to achieve better visibility into data.
• Slice data into various views on the fly.
• Automate redundant reporting and analysis processes.

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.
• 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. 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 2

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 can 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).

Day 3

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, as a quick and easy alternative to creating customized dashboards.

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