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?
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