HOW TO BUILD DASHBOARDS INFO CAPTOR

 Introduction

Purpose

Complete guide on building dashboards using Excel, Access and any other Database as the data source.

The tutorial will start with instructions on building a dashboard using Excel

Files.

The purpose is not just to build any dashboard but a good and useful dashboard that is modular in design and easy to maintain and scale it.

 

Excel Dashboards

Databases are often under lock-n-key of the IT Department and not possible to connect directly due to security reasons. So if you plan to build a Dashboard prototype or a Dashboard Mockup it makes sense to export the data to Excel and build it.

Modular Dashboard Design

Lot of the online tutorials and Dashboard e-books teach you to build dashboard in Excel file. They provide you with templates, you fill in the information and build the dashboard using Excel charting techniques.

If you use Excel Templates for your real dashboarding needs then you may end up with any of the below problems.

·         Big Bulky and bloated excel files

·         Undocumented Macros

·         Zero verification on the data authencity

·         No way to refresh the dashboard (lost productivity)

“Best Practise for Dashboard Design”

Modular Design - Keep Data and Presentation Separate

How? – Use Excel only for storing your data. Nothing more. Do not build any visualization in the same place where your Data resides.

Benefits of this approach

·         Your dashboard or presenation layer is independent –

Dashboard definition is stored separately

·         Increased productivity – When you have fresh data just refresh the dashboard, no redevelopment needed

·         No Messy Excel Macros or coding knowledge required – Just drag and drop tables and visually build Tables, Charts, Speedometer, Gauges, Dials, Thermometers and more charts

·    Simplified Distribution and Presentation – You can Export the Dashboard to PDF or HTML and just send them as attachments 

     Save Time - Just build the dashboard presentation layer only once and automate the refreshes

Data Preparation

Export your transaction data to Excel file by running a report or requesting your IT department.

There are certain rules to be followed when you get the data in Excel files.

Here is a sample Excel dump of Order Entry data.

The first row is always treated as the column names.

If you inherit an Excel file with pivots and charts all over the places then try to copy just the raw data in another worksheet in the same file or create another file. If it is not possible to keep the first row as the column names then you could define “named ranges”. In the dashboard designer it is possible to access the data using Cell ranges but then future updates to the Excel file could break the dashboard, for e.g if the data goes beyond the

Cell range.