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