Making your dashboards interactive [Dashboard Essentials]

Posted on August 2nd, 2012 in Charts and Graphs , Excel Howtos , Learn Excel - 27 comments

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

There are 2 aspects to interactivity:

  1. What users see (controls, slicers etc.)
  2. How it works in background (formulas, pivots, tables etc.)

Section 1: Adding interactivity to your dashboards

There are many techniques to add interactivity to your dashboards.  Lets look at each of them closely.

Using Data Validation to add drop-downs to a cell

This is the easiest way to get started. Using data validation feature in Excel, we can restrict only a set of values in a cell. When you do this, Excel shows a small drop down box (combo-box) inside the cell so that you can pick one of the possible values. Like this:

Data validation drop down list in Excel - Adding interactivity to dashboards

Demo of what you can do:

An example report show casing flu trends in US, various states & cities between 2003 – 2009. For more, click here.

Flu trends dashboard in Excel - Data validation in play

Learn how to use data validation drop-downs:

Example Dashboards with data validation drop downs

Using Form Controls to add interactivity

Almost all computer users are familiar with form controls. We see them every day – scroll bars, check boxes, option buttons, buttons – pretty much all programs in your computer are ripe with form controls. But do you know you can add the same controls to your Excel worksheet?

You can use these controls on worksheets to help select data. For example, drop-down boxes, list boxes, spinners, and scroll bars are useful for selecting items from a list. Option Buttons and Check Boxes allow selection of various options. Buttons allow execution of VBA code.

By adding a control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the control. You can use that numeric value in conjunction with the Offset, Index or other worksheet functions to return values from lists.

Excel form controls - adding interactivity to your dashboards

Demo of what you can do:

[Watch the demo on our YouTube channel]

 Learn how to use form controls

Example dashboards using form controls

Using Slicers to add interactivity

Slicers, a new feature added in Excel 2010 can be used to add interactivity to your dashboards & reports. Slicers are like visual filters. So you can see all available options as small boxes and you can click which option you want.

Demo of Slicers in action:

Using slicers as interactive elements - example - making dashboards interactive

 

Learn how to use Slicers

Example Dashboards using Slicers

Using Click-able cells as interactive elements

With a few lines of VBA code, you can turn every cell in Excel in to a potential input option. When user clicks on a particular cell, you can treat that as interaction and modify your dashboard (or chart). This is a very powerful and intuitive way to use in dashboards. See below example.

Demo of what you can do:

Grammy Bump Chart in Excel - Using click-able cells as interactive elements in your dashboards

 Learn how to use click-able cells

Example dashboards using click-able cells type of interactivity

Using Hyperlinks to add interactivity

Many of you know that you can type any text in a cell and press CTRL+K to convert it to a hyperlink to another part in your workbook. But Hyperlinks can trigger macros upon mouse hover. This is a powerful technique first mentioned by Jordan at OptionExplicitVBA.

By using this behavior, we can create an interactive report that gets updated upon mouse hover. See this demo:

Demo of what you can do:

Interactive dashboard using hyperlinks - making interactive dashboards in Excel

Learn how to set up dynamic hyperlinks

Example dashboards using interactive hyperlinks

Using VBA / Macros to add interactivity

Of course, you can add active x or VBA events to add interactivity to your dashboards. This gives you lot of control on what you want and enables you to do more. That said, using VBA to provide interactivity requires that your audience must enable macros when they view your work.

There are many ways to add interactivity thru VBA. Some popular methods are,

  • Adding buttons or assigning macros to drawing shapes, images
  • Overlapping buttons or shapes on maps, floor plans etc. and driving events on click
  • Using worksheet or active-x controls and adding events (like mouseover, click etc.)

Note: Both click-able cells & interactive hyperlinks also require VBA to be enabled. But the amount of code they require is quite less.

Demo of what you can do

Picture Calendar using Excel - Adding interactivity using VBA to your dashboards

Learn how to use VBA & Macros to add interactivity

Example Dashboards using VBA Macro based interactivity

Using Timelines to add interactivity [Excel 2013]

Starting Excel 2013, Microsoft is introducing a new feature called as Time lines. Timelines allow you to interactively select a range of dates. I have not yet written any articles on this feature. But here is a short demo on how they work:

using time lines to add interactivity to your dashboard reports

Section 2: Behind interactivity – What you need to know in Excel

Now that you know various techniques for interactivity, lets understand various building blocks that help you get there.

Use tables to hold your data

One of the premises of interactivity is that your data can change. When this is the case, I suggest you to set up all your data in tables. Tables allow you to keep data that can grow (or shrink) and write formulas referring to whole range.

Learn how to use tables [Excel 2007 and above only]

Use INDEX formula

INDEX formula helps you extract a portion (single cell, range) from a list of values that you want to use for further calculations or charting. The syntax is simple.

INDEX(range of values, row, column)

Example: INDEX(A1:A10,5) returns A5

Note: Index returns a reference to A5, not the value itself. So you can use INDEX where ranges are expected. For ex. INDEX(A1:A10,5) : INDEX(A1:A10,9) same as A5:A9

Fore more on INDEX formula:

PS: You can also use OFFSET formula in this situations. Please keep in mind that OFFSET is volatile and hence can slow down your workbooks if you use it alot.

Use lookup formulas

Interactive dashboards require formulas that dynamically lookup a set of values among heaps and return them to charts, summaries etc. This is where lookup formulas come handy. Check out our LOOKUP page for comprehensive information on this.

Use SUMIFS, SUMPRODUCT

SUMIFS & SUMPRODUCT formulas will become your best friends when it comes to extracting summaries from mountains of data based on user interaction. Once you master these, you can analyze & visualize any amount of data with ease.

Use Picture links

Picture links are live snapshots of ranges of cells. If you create a picture link from cells A1:D5, then although it looks like a picture, it is a live image of the cells A1:D5. So when the cells change, the picture gets updated too, thus creating interactive effect.

For more on picture links:

Use Pivot tables

Pivot tables can process large volumes of data and give you desired summaries with in split seconds. They are by nature not dynamic (if data or criteria changes, you need to refresh them). Starting Excel 2010, you can use Slicers to interactively update pivot tables (hence pivot charts) . Even in earlier versions, you can use simple macros to automatically refresh pivot tables whenever users modify a form control or do something else. This allows for powerful dashboard reporting all the while keeping your calculation engine light weight.

For more on pivot tables:

Use conditional formatting

Conditional formatting plays an important role in interactive dashboards by highlighted changed portions of worksheet. This further improves the interactive feel and guides users attention.

More on conditional formatting:

Do you make your dashboards interactive?

I love keeping my workbooks, models & dashboards interactive. Simple features like form controls, slicers can add a lot of wow factor to your workbooks.

What about you? Do you make interactive dashboards & charts? What are your favorite techniques? Please share using comments.

Now, if you excuse me, I will go and resolve a fight between my daughter and son. They both want remote control the TV even though it is switched off.

More on Dashboards: Check out Excel Dashboards page & resources for making dashboards page.

27 Responses to “Making your dashboards interactive [Dashboard Essentials]”

  1. Jeff Weir says:

    Chandoo, this is an outstanding post.

  2. 5antiago says:

    I just love pivot tables, in particular I use them as a conduit to raw data (in a separate file) all he time.  It's such a nice feeling to press "Refresh All" and watch every chart across multiple sheets magically update 😀

  3. Susan says:

    Thank you for another terrific article, Chandoo! I love using pivot tables and slicers, but one of our key clients is still on Excel 2007. Can they still view one of my awesome spreadsheets without getting Excel 2010? It's a local government client, so an upgrade for them is out of the question, sadly.

  4. Thanks for the mention - and I really love this post! However - not that I really mind - but it's "Jordan" - although "Jordon" has a nice ring to it!

    Also, not to be a shameless self promoter, but my latest use of the Interactive Hyperlink technique is here:

    http://optionexplicitvba.blogspot.com/2012/06/period-table-of-elements-in-excel.html 

    I'm posting it because, well, I'm really proud of what I've made and I think it's pretty cool. I'm also wondering if anyone else on Chandoo has made use of this technique?

    Finally, I can't wait for timelines. They really open a whole new world of possibilities. 

  5. John Omohundro, CPA says:

    Wow!  This must be the holy grail of Excel Interactivity (tm)!!  Great post, Chandoo...don't ever move this page, because it's getting bookmarked for future reference!

  6. Chandoo,
    This is such an amazing post. A whole lot of stuff to refer to and learn for a month (maybe more!).
    Thank you so much.

  7. Cesarino says:

    Chandoo,
    One million of THANKS.

  8. I'm going to throw this up here, in case anyone is interested. I've created a FAQ on rollovers which you might find helpful an addition to the tutorials here, on Chandoo.

    Excel Rollover Mini Faq

  9. [...] Send mails using Excel VBA & Outlook [ 22,294 ] Customer Service Dashboard in Excel [ 18,136 ] Making your dashboards interactive [ 15,294 ] Extract numbers from text in Excel [ 18,490 [...]

  10. Adewale Oke says:

    This is a great insight to Excel capabilities. Please don't move this page, because it is bookmarked for reference.

    Chandoo, Many thanks!!!!!!!!!!!!!!!!!!!!!

  11. [...] How to make your charts & dashboards interactive – Detailed how to guide [...]

  12. JC says:

    Hi,
    Great work Chandoo. How do you get to protect the worksheet where the chart is and at the sane time be able to select from a list and dynamically update the chart? e.g. exactly what you do on the flu trends demo.
    Thanks.
    JC

  13. Brooky says:

    Hi Chandoo

    This is VERY useful and very clear. Thank you! I am trying to make my first interactive dashboard and what I'd like to do is have a number of charts, all of which change when I use a form control to select a different category. So for three graphs and a couple of bar charts (none of which are very complicated) I want buttons at the top so you can click (for example) red cars, blue cars, yellow cars or all cars, and it will show the exact same charts but either for all cars or for a subset of cars. Can you do that via one button or do I have to make a button per chart?

    Thanks!

    P.S I'm going to tell my colleagues about this amazing site.

  14. Suzie says:

    I keep getting the "ambigious name detected" for the highlightseries function in my Macro so my cell 'valSelOption' never populates. Do you know whats going on? I did everything on this post, i cant figure out why it won't work.

  15. Suzie says:

    This is related the above comment:

    Link to a pic of the code i used:
    http://i43.tinypic.com/2vmfsr5.jpg

    Link to a pic of the data where 'valSelOption' doesn't populate:
    http://i39.tinypic.com/2v16ips.jpg

    Link to where i am putting the hyperlink:
    http://i42.tinypic.com/ifpg1k.jpg

  16. Suzie says:

    And when i click on the 'arrow' for the hyperlink i get an error that says, 'Cannot open the sepcified file'

  17. Misha says:

    Dear Chandoo,
    Its really amazing what u do using only excel tools. I need ur advice very much, cause very soon I have to present my interactive dashboard to my supervisor: I want my dashboard to be consisted of 2 controls (date range and chart type). I succeeded to create date range control field based on which user may interact with the data. However I have some difficulties to assign chart type control to my dashboard.
    Could you plz bring an example of a dashboard where one control is data range and another is chart type like the one u have first interactive chart (sales and cost) tutorial.
    Hope that u understand my question!
    Thanks and regards,
    misha

  18. Vikash says:

    Hi Chandoo,

    I can simply say this are some pure magics which you are doing with the excel.

    I need some support in creating a tool where there is a chart for number of years (say 60 years) and I want a slider/timeline option where the user is able to select a range of years and then a detail graph appears below for the year range selected.

    Your help will do a great favor to me.

    Looking forward to hear from you.

    Thanks,
    Vikash

  19. Nurul Rofi' Itsnaini says:

    Hi Mr. Chandoo,,

    Thanks about sharing Your template Form Dasboard, Very Amazing and Details Form, i can Use for Reporting and analyse in Bussiness Costumer Goods..make its Simple and View Helicopter to Analyse in the Market...

    Regards,
    Nurul

  20. Hari Krishna says:

    All I can is just awesome !!! Very soon I'll be coming to Vizag for training in excel and VBA. Thanks

  21. […] more on learning how to setup interactive features on your dashboards, checkout this Making Your Dashboard Interactive section of Chandoo’s […]

Leave a Reply