Creating interactive dashboards using Excel VBA – Tutorial

Home/Excel Dashboards/Creating interactive dashboards using Excel VBA – Tutorial

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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:

Creating interactive dashboards using Excel VBA

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:

Form Controls:

We can use Form Controls available in the Excel to provide the user interface and link it to an object.
Interactive Dashboards- form controls

Charts and Sparklines:

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.
dashboards - charts and sparklines-pivot tables

Tables, Pivot Tables and Slicers:

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.
dashboards-tables pivot tables

VBA

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.

excel vba programming - create interactive dashboards

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.

Creating interactive dashboards using Excel VBA - drop downs

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

Creating interactive dashboards using Excel VBA - populate list

» Add the Following code in Dashboard Module for ComboBox_Change Events : We are capturing the user selected Category and Region.

Creating interactive dashboards using Excel VBA - events

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

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource External VBA Reference

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

34 Comments

  1. Asha June 22, 2015 at 8:24 PM - Reply

    I would like training in excel dashboard. Do you offer this? Thanks. Asha

    • PNRao June 23, 2015 at 11:06 PM - Reply

      Hi Asha,
      Yes, we are going to start our online classes in couple of weeks. We will inform you before we launch our program.

      Thanks-PNRao!

  2. Trish July 20, 2015 at 5:25 AM - Reply

    Hi! Please notify me as well.
    It would be very appreciated.
    Thank you so very much. Regards,

    • PNRao July 20, 2015 at 1:24 PM - Reply

      Hi Trish,

      Thanks for your interest to learn VBA. Sure, we will inform once we are ready.

      Thanks-PNRao!

  3. Darren September 9, 2015 at 12:46 AM - Reply

    Hi, are you ready to offer training?
    Thanks.

    • PNRao September 9, 2015 at 1:07 AM - Reply

      Hi Darren,

      We are creating VBA online classes (Launching on 1st Oct 2015) and then we are going to prepare online course for ‘Developing Dynamic Dashboards using MS Excel’ (Planning to launch on 1st November 2015).

      Please let us know if you are interested in any of the above courses, so that we can inform you when we launch our classes.

      Thanks-PNRao!

      • Darren September 17, 2015 at 1:21 AM - Reply

        Yes, I am interested in both the VBA and Dynamic Dashboard courses.
        Do I need to learn any basics as a pre-requisite?

        • PNRao September 17, 2015 at 12:25 PM - Reply

          Hi Darren, The pre-requisite is MS Excel, it is good to have basic understanding of Excel and its utilities. However, we will cover basics of Excel, before going to the actual topic.

          Thanks-PNRao!

  4. Moataz October 1, 2015 at 8:10 AM - Reply

    I’m interested in VBA online classes and Developing Dynamic Dashboards using MS Excel online course need more details.
    Thanks,
    Moataz

    • PNRao October 2, 2015 at 1:19 PM - Reply

      Hi Moataz,

      We are creating VBA online classes (Launching at the earliest) and then we are going to prepare online course for ‘Developing Dynamic Dashboards using MS Excel.

      We will inform you when we launch our classes.

      Thanks-PNRao!

  5. Mark October 2, 2015 at 1:51 PM - Reply

    I am interested as well. Please let me know when.

    Thanks!

    • PNRao October 2, 2015 at 2:31 PM - Reply

      Thanks for showing interest on VBA Classes. We are working on it. It will be available soon. We will notify you once we launch our classes.

      Thanks-PNRao!

  6. Manjunath Channappa October 12, 2015 at 6:27 PM - Reply

    count me in as well pls…

  7. Manjit October 15, 2015 at 11:35 AM - Reply

    Please let me know i am interested in learning VBA dynamic dashboard.

    Regards

  8. Ruquaiya October 26, 2015 at 12:44 PM - Reply

    I’m interested in VBA online classes and Developing Dynamic Dashboards using MS Excel online course need more details.
    Thanks,
    Ruquaiya

    • PNRao November 3, 2015 at 4:11 PM - Reply

      Thanks for showing interest on VBA Online Classes and Dashboards. We are working on it. It will be available soon. We will notify you once we launch our classes.

      Thanks-PNRao!

  9. Dhiraj November 4, 2015 at 12:32 PM - Reply

    Dear Sir,

    First of all thanks for sharing such a valuable information …

    I want to be part of your online programmes so please let me know detailed information..

    Wishing you a Very Happy Dipawali in advance..

    Have a great day..

    Dhiraj

    • PNRao November 7, 2015 at 11:24 AM - Reply

      Sure, we will inform you while launching our online courses.
      Thanks-PNRao!

  10. Julie November 9, 2015 at 6:35 PM - Reply

    I am also interested in VBA and dynamic dashboards.

    Thanks and best regards,
    Julie

    • PNRao November 16, 2015 at 4:11 PM - Reply

      Hi, Thanks for showing interest on our online courses. We are working on the program, will inform you when we launch the course.
      Thanks-PNRao!

  11. AR November 15, 2015 at 9:35 PM - Reply

    When is the next course on dynamic dashboards using excel.

    • PNRao November 16, 2015 at 4:03 PM - Reply

      Hi All,
      Thanks for showing interest on our online course, we are working hard to launch this program as soon as possible, we are planning to launch on Jan 1st 2016.

      Thanks-PNRao!

  12. Max November 20, 2015 at 4:53 PM - Reply

    Hi
    I am also interested in VBA and dynamic dashboards.

    Thanks and best regards,

    Max

  13. harjeet singh November 30, 2015 at 8:08 PM - Reply

    Hi, I am currently working as a engineer ia company that is manufacturing based .The machine that i undertake in operation have lots of calculation like current and voltage to calculate power heat input mass and many more so help me how i can make userform,

  14. harjeet singh November 30, 2015 at 8:11 PM - Reply

    sir i also want to learn my contact is plz inform me

  15. Rich December 2, 2015 at 5:08 PM - Reply

    Hi
    I am also interested in VBA and dynamic dashboards.

    Thanks and best regards,

    Rich

  16. Jeya February 18, 2016 at 9:39 PM - Reply

    I like to learn dashboard through dvd.pls suggest me any DVD available in market

  17. Zahida March 30, 2016 at 2:16 PM - Reply

    Hi
    I am also interested in VBA and dynamic dashboards.

    Thanks.
    Best regards,
    Zak

  18. Varsha July 12, 2016 at 12:08 AM - Reply

    Hi,

    I tried to execute this code, till drop-down part I’m able to get output but further that I am not able to link my charts.
    Please help me as early as possible

  19. prachi August 25, 2016 at 11:38 AM - Reply

    I am also wiling to learn dashboard creation, can you arrange the same again?

  20. Udit Kapoor July 5, 2017 at 5:33 PM - Reply

    Hello!

    I have my MBA in Business Analytics. I’m interested in learning VBA Dashboards using VBA.
    Kindly assist me for the same.

    Thanks!
    Udit Kapoor

  21. SHESADEV SHA August 3, 2017 at 4:07 PM - Reply

    HI,
    I want to learned excel VBA DASHBOARD AND courses through online.

    Please intimate me when your batch should be start .

    Thanks
    Shesadev

  22. K.UmaRajesh October 13, 2017 at 4:08 PM - Reply

    Hi Sir, I am Rajesh, I work mostly with Excel and I want to expertise in Excel. Please help!

Leave A Comment