Excel Dashboards for Business Intelligence training course

Excel Dashboards for Business Intelligence

Unit 1: What is a dashboard?

Dashboards are a type of report. They help turn often large amounts of data into a digestible page that enables easy analysis. This form of data analysis can be part of Business Intelligence (BI).

Why use a dashboard?

Many companies have access to large amounts of data, perhaps in a data warehouse or spread across multiple file servers. Decision makers often want to cut through this data and look for patterns. Dashboards are an excellent way to turn large amounts of data into usable and actionable reports.

A dashboard is a visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. A dashboard consists of three key attributes.

1. Displays data graphically (such as in charts). Provides visualizations that help focus attention on key trends, comparisons, and exceptions.
2. Displays only data that is relevant to the goal of the dashboard.
3. Contains predefined conclusions relevant to the goal of the dashboard and relieves the reader from performing their own analysis.

Common features of a dashboard

Dashboards will often have the following features:

• A single page. One page of information gives a high-level analysis. This one page view forces the most prominent trends to the front.
• Highly visual. To enable quick takeaways, the data is represented in a very visual way.
• Interactivity. Simple controls on the dashboard allow the user of the report to modify the view, perhaps choosing different parameters.
• Timely. Dashboards will be most effective by having a method to be as up to date as possible.
• Multi-format. To offer different views on the same data, different formats of data representation will be used, combining charts, tables, images and other visualisations.
• Relevant. Only data appropriate to the dashboard is going to be displayed.

Why use Excel to create dashboards?

There are a wealth of BI tools available to help create dashboards. However the variety of tools available, the ubiquity of Excel on people's computers, combined with the ability to export these interfaces to the Web make it a straightforward tool to use. A formalised BI platform could be overkill and too expensive for a company's needs. Excel is familiar and most companies have already budgeted for installing Microsoft Office.

Preparing to build a dashboard

Before you start

Before you create a dashboard, you should take time to research and define the following points:

• Define the message - what is the purpose?
• Establish the audience - what level of user is going to consume the report?
• Define the measurements - which metrics will support the message?
• List the data sources - build an inventory of data feeds you will need to create the measurements.

Separating data from presentation

Many people rush in creating charts and directly working with the data. This will work in the short term but you will find as you move forward it becomes harder and harder to maintain. Strive to separate the data sheet from the presentation sheet. Better still, have at least three sheets:

• Data -source data, cleaned but not extensively formatted. Often driven by a refreshable data feed. Data is often repetitive, with recurring items. Sometimes referred to as a ‘flat file'
• Analysis - interstitial ‘helper' tables that coerce the source data into a usable format. Often aggregating, totalling etc. This could be in the form of a PivotTable, or a cross-tabular table you have compiled with formulas and functions.
• Presentation - the formatted report that users will consume. Could be charts, simple tables, images, one line of commentary etc.

How to use this manual

Everyone will have a different use case, a different audience and a different set of reporting metrics required for their industry. In this manual you will walk through a number of Excel features commonly used in dashboard reporting. You can mix and match these features in any combination to create your own reports. In the appendix you will find three projects to give you some ideas of how you could develop a company dashboard. You should select the most suitable components for your report.

Different Excel versions

This manual focuses on the 2010 and 2013 versions of Excel. Most of the following is also possible in 2007, apart from a few PivotTable features such as Slicers and Timelines.