A Data Analysis and Dashboard Showcase with Microsoft Excel
This post is about a showcase. Don’t expect too much. I will not provide a detailed how-to tutorial. All you can learn from this post is that you should not toss Excel as a matter of principle, when you have to create Business Intelligence tools and dashboards. Agreed, Excel has its limitations especially with regards to the access to larger external databases. And Excel’s chart engine has some limitations as well. Nevertheless Excel provides the flexibility to create compelling and professional dashboards.
Recently a Lithuanian company invited me to conduct a training to their marketing department. The focus of this training shall be on how to create Excel models including dashboards and visualizations made to stick. I haven’t done trainings for quite a while, but I do remember that the perfect start for a training is a demonstration of what it will be about and what the participants will be able to do afterwards. Therefore I created a showcase based on the Lithuanian Census 2001.
“The Lithuanian Census of 2001? Really? Isn’t that data by far outdated?”
Sure, but this doesn’t matter. This post is not about the data, it is about the possibilities of Microsoft Excel. I am convinced you can easily imagine how much value a dashboard like this could add to your daily business, if you would use this model for analyzing your customer base, sales figures, revenues and so forth.
Here is a screenshot of the dashboard:
Looks interesting? Download the file:
Download Lithuania Census Dashboard (Excel 97 - 2003, 505.5 K)
The dashboard is divided into 3 main sections:
- Drill-down section – the user input interface
(top of the dashboard)- Slicing and dicing of the displayed data by selecting dimensions from drop-down-lists
- Context-sensitive drop-downs for the economic activities. The list of subcategories always corresponds with the selected main category.
- Visualization part I – the country-wide results – all counties at a glance
(left part of the dashboard)- A heatmap of Lithuania by county visualizes the geographical distribution of the data by flood-filling: the higher the value the darker the fill color and vice versa.
- The corresponding sorted bar chart below the heatmap shows the results for all counties and allows a direct comparison.
- The red border color on the map and the red bar highlight the selected county (if any, see below).
- The radio buttons on top let the user toggle between the absolute numbers and the numbers relative to the total population of the counties.
- Visualization part II – the results for a selected county or Lithuania in total
(right part of the dashboard)
- The drop-down-list on top of this part of the dashboard can be used for selecting one county or Lithuania in total.
- A structured table and bar chart visualizes the distribution of all economic activities by category and subcategory.
- The tornado chart shows the population pyramid by gender.
- The pie charts provide a quick overview about the distribution of gender and area type.
- The dimensions selected for drill-down (if any) are highlighted in red on all 4 charts. Thereby the user is able to immediately identify the values selected for drill-down.
This model has been realized with nothing else than Microsoft Excel. No add-in or other additional software needed. Just plain old Excel with a couple of lines of VBA-code and some advanced Excel features and tricks:
- Combo boxes and option buttons to handle the user inputs and drill-downs and create a convenient user-experience
- A simple one line VBA procedure in combination with a named range and a CHOOSE function to provide the context sensitivity regarding the drop-down-lists for category and subcategory of the economic activity
- Array formulas to consolidate the data according to the user inputs (slicing and dicing)
- A small VBA routine and a color assignment table to flood-fill the heatmap according to the displayed data
- Combination of tables and charts to save real estate on the dashboard and to maximize the amount of information displayed
- A simple, but effective way of sorting with formulas to provide the sorted bar chart (distribution by county)
- Charts with dummy series to highlight the selected county and the selected dimensions in red
- A simple chart hack to create the tornado chart for visualizing the population pyramid by age bucket
I agree, all this requires advanced Excel skills. As mentioned above, I will not provide a detailed tutorial for all these techniques. But every single one of them has already been described somewhere on the internet. This proves that generally everybody is able to build a dashboard like this with Microsoft Excel.