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:

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.

Learn how to use data validation drop-downs:

• Adding data validation drop downs in Excel – Introduction & Examples
• Cascading Drop downs – load values in 2nd list depending first list
• Making data validation list dynamic

Example Dashboards with data validation drop downs

• Flu trends dashboard in Excel
• Visualizing Survey Results using Panel Charts
• Sales Analysis charts in Excel – lots of examples
• Personal Expense Trackers
• Sales Dashboards – lots of examples
• Excel Salary Survey – Dashboards – lots or examples

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.

Demo of what you can do:

[Watch the demo on our YouTube channel]

 Learn how to use form controls

• Introduction to various form controls & Examples
• Using check boxes with charts – example & tutorial
• Using scroll bar control – simple mortgage payment calculator in Excel

Example dashboards using form controls

• KPI Dashboards using Excel
• Customer Service Dashboard in Excel
• Excel Salary Survey Dashboards – lots of examples
• Sales Dashboards in Excel – lots of examples

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:

Learn how to use Slicers

• Using slicers to make a dynamic dashboard in Excel
• Overview of slicers & other new features in Excel 2010
• Using slicers to select one of many scenarios in your models

Example Dashboards using Slicers

• Dynamic Dashboard using Slicers
• Excel Salary Survey Dashboards – lots of examples

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:

 Learn how to use click-able cells

• Showing details up on selecting a cell

Example dashboards using click-able cells type of interactivity

• Interactive sales chart in Excel
• Displaying product reviews on demand
• Grammy bump chart in Excel
• Customer service dashboard in Excel
• Excel Salary Survey Dashboards – lots of examples

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:

Learn how to set up dynamic hyperlinks

• Interactive dashboards using Excel Hyperlinks – tutorial & explanation
• Video tutorial on Interactive hyperlinks
• Excel hyperlinks – basics, syntax & more

Example dashboards using interactive hyperlinks

• Excel Salary Survey Dashboards – lots of examples
• Periodic table of elements in Excel [Option Explicit VBA]

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

Learn how to use VBA & Macros to add interactivity

• Introduction to VBA, Excel Macros
• Using VBA Macros to make a picture calendar
• Dynamic Pivot Chart using VBA Macros

Example Dashboards using VBA Macro based interactivity

• MLB Pitching Statistics Dashboard
• India’s world cup cricket victory in a dashboard
• Interactive Sales chart using Excel
• Sales analysis charts in Excel – multiple examples
• Excel Salary Survey dashboards – multiple examples
• Visualizing Roger Federer’s Wimbledon victory – Excel VBA Dashboard

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:

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:

• Using INDEX formula in your lookups
• The imposing INDEX [Excelhero]

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.

• Introduction to SUMIFS formula, examples & explanation
• Introduction to SUMPRODUCT formula, examples & explanation
• Formula Forensics 007 – Sumproduct
• Advanced SUMPRODUCT examples
• More on SUMPRODUCT, SUMIFS, COUNTIFS, SUMIF, Array formulas

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:

• Introduction to picture links – examples, information & uses
• Picture links in practice – example dashboards & charts

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:

• Introduction to Pivot Tables
• 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:

• Introduction to conditional formatting
• 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.