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