This tutorial explains creating interactive dashboards using Excel VBA with example file which you can download. We will see the basic things that we need to understand to create dashboards and then we will learn how to create stunning and rich visualized dashboards.
We will develop a simple interactive dashboard, I will take the last updated simple sample dashboard and enhanced it by using VBA.
In this tutorial:
- What is a Dashboard? And what are the Interactive Dashboards?
- Concepts we should understand to develop Interactive Dashboard:
- Practical Learning – Creating Dashboards using Excel VBA:
- Tools for building Rich Interactive Visualized Dashboards:
What is a Dashboard? And what are the Interactive Dashboards?
Dashboard is a kind of report or a simulation tool to displaying important data or information at a single place to get the quick understanding of the data. Dashboards are visually displayed with combination of Tables, Charts and Pivot tables and Insights for Analyzing the Data. Dashboards helps to quickly understand the key metrics and delivered as simulation tool for quick data analysis.
When we deal with large amount of data (time series/ historical) or data with more number of categories, Fields/Columns/Variable or Records, we need to provide interactivity to understand the data in-depth. For example, you may be developing a dashboard to understand the sales of a Super Market. Assume, you have the sales data for last 5 years, and it has 5000 stores across the world. You can’t understand the each by developing a simple dashboard with this large amount of data. You have to provide different options to choose different categories of the Super Market. For example, if you want to understand the data based on the demographic variables, you should provide the user interface to select Countries, Regions, States, Income Levels, Age Groups and Gender etc…
Concepts we should understand to develop Interactive Dashboard:
We can develop the interactive dashboards using Excel. We should know the following concepts to build Interactive Dashboards:
We can use Form Controls available in the Excel to provide the user interface and link it to an object.
Charts will help you to provide the graphs for the required metric to quickly understand the respective data. You can make them interactive by using VBA or linking the From Controls with Charts.
Tables and Pivot Tables help you to present the data in table format, Pivot tables help you to summarize the data. We can use slices and VBA to develop interactive pivots.
You can develop the rich visualized dashboards using VBA. as discussed above it helps make the charts and table more interactive. And also helps to filter the data or change the data source. You can get the external data using VBA and provide many mare advanced and interactive features.
Practical Learning – Creating Dashboards using Excel VBA
You can download the example file [Sample Interactive Dashboard (Sales Data).xlsm] and have a look into this.
Steps for Creating interactive dashboards using Excel VBA
- Design the dashboard : First you have to prepare a rough design of you dashboard based on your customer requirement. And it should approved by your customer before you start developing the dashboard
- Plan: Once you are clear with your requirement and design, you have to plan how to achieve it. you can write simple steps to develop the dashboard
- Develop: Start developing your plan and use the VBA if required. Generally developers use VBA to provide dynamic feature to the user
- Test and deliver: Check if all the features are working correctly or not and fix if there are any bugs. You can provide a test copy to user for testing, and rquest user feedback. Finally you can deliver once you are done with the changes.
How It’s become interactive?
I have used the last updated simple sample dashboard and done the following changes.
» Saved the sample file as macro enable workbook (i.e; .xlsm format): you must save the file as macro enabled, otherwise it will not save your VBA code.
» Added two ComboBoxes in Dashboard Worksheet and name them as cmbRegion and cmbCategory. The first one (cmbRegion ) is for providing the drop-down list to the user to select regions. And the second one (cmbCategory) is for providing the drop-down list to the user to select Categories.
» Added the following code for ThisWorkbook Moduel for Workbook_Open event: It will populate the drop-down list with required list item while user opens the file every time.
» Add the Following code in Dashboard Module for ComboBox_Change Events : We are capturing the user selected Category and Region.
» You are done! This is a simple dashboard using VBA, we will see creating complex dashboards later.
Tools for Developing Rich Interactive Dashboard
Excel is the most widely used tools for developing regular dashboards. If you have large amount of data and you have many tools to develop rich visualized tools. The following are the most widely used Dashboard development and BI Tools. Qlikview , TIBCO Spotfire and Tableau are rich visualized BI Tools. They are quick and can deal with large amount of data.