How to build Excel Dashboards – 1
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.
Lets begin Building Dashboard
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.
Download Excel 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.
NOTE: Make sure column names do not have any funky characters. Just keep it to simple alpha-numeric column names.
2
