Creating Dynamic Dashboards in Excel

For many businesses, making the leap to Business Intelligence tools is not an easy process. After years of relying on pages and pages of Excel data as their primary performance reporting, moving from an informational perspective to an insight perspective is not just about changing toolsets. It also requires an education process and a learning period to realise the benefits of better visualization. And this is where Excel can be used to provide a working proof of concept as a stepping stone towards fully integrated BI into both processes and decision making.

The following video outlines a working dynamic dashboard built in Excel.

This dashboard was developed in three versions:

VBA version- uses Visual Basic for Applications and pivot tables. It is faster than the other two but relies on VBA [recorded macros] to perform some tasks.
VBA-Free version - uses pivot tables and the GETPIVOTDATA function. This version is slower than the VBA version.
LookUp Table Version - uses a spreadsheet database [ a table of data in the workbook] and lookup functions (VLOOKUP, MATCH, OFFSET) to get the data for the dashboard report. Spreadsheet databases require more maintenance and are slower.

Using Excel to develop prototypes of executive dashboards provides a flexible and cost efficient environment to gain insight into the value of data visualization and as an interim step to full BI Dashboard tools. Once the first dashboard has been created, it is simply enough to create multiple alternative user interfaces.

The most important element of the dashboard is to not get carried away with the graphs at the cost of the primary function of the dashboard - to answer key business questions.