Ads 468x60px

Friday, August 3, 2012

Making your dashboards interactive [Dashboard Essentials]

Making your dashboards interactive [Dashboard Essentials]:
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.
Posted by at

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)