Interactive Charts

Interactive Charts with Checkboxes and VBA

Wednesday, April 1, 2009 by Jon Peltier 16 Comments

Wednesday, April 1, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

By definition an interactive dashboard contains methods for users to control which information is displayed. This can be accomplished in many ways. In Interactive Charting by Combo Box or Checkbox I wrote about using checkboxes and worksheet formulas to do this, and I elaborated on the technique in Interactive Charts with Checkboxes and Formulas.

There are a couple of shortcomings with the checkboxes-plus-formulas approach. If a series is not displayed, there is still a space for its bars in a column chart. Also, the legend key (marker in the legend) for the series is still present even if the label is hidden.

By using VBA and checkboxes together, these shortcomings can be avoided. This technique makes use of Excel’s default behavior, which does not show data in a chart if the data range is hidden. This behavior can be changed on a chart by chart basis, so that changing it for one chart does not change it for other charts.

Checkboxes and VBA

Start with a line or column chart that includes all of the series you will allow the user to select from (A, B, and C in this example). Add a checkbox from the Forms toolbar for each series.

Posted: Wednesday, April 1st, 2009 under VBA.
Tags: Dynamic Charts, Interactive Charts, VBA.
Comments: 16

Microsoft MVP Logo

Interactive Charts with Checkboxes and Formulas

Tuesday, March 31, 2009 by Jon Peltier 19 Comments

Tuesday, March 31, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

An interactive dashboard by definition includes mechanisms for users to control aspects of the display. There are many ways to accomplish this. In Interactive Charting by Combo Box or Checkbox I wrote about using checkboxes to do this. That used a very simple example, which I will explore further in this article.

There are a couple of ways to achieve this interactive control. There is an easy method that relies on worksheet formulas to alter the contents of the source data range of each series, and there is a more involved technique that uses VBA to programmatically adjust the chart’s source data. This post will describe the approach that uses worksheet formulas.

Checkboxes and Worksheet Formulas

Start with a column chart that includes all of the series you will allow the user to select from (A, B, and C in this example). Add a checkbox from the Forms toolbar for each series.

Posted: Tuesday, March 31st, 2009 under Dynamic Charts.
Tags: Dynamic Charts, Interactive Charts.
Comments: 19

Microsoft MVP Logo

Accordion Chart for Jorge

Thursday, February 12, 2009 by Jon Peltier 22 Comments

Thursday, February 12, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Jorge Camoes wrote in Focus + Context (a Bar Chart Is Not a Skyscraper) that tall bar charts are a waste of space. If you plan to show everything (say, all fifty state names), you may as well just use a table. I’m not sure I completely agree, but part of his point is that you shouldn’t overwhelm a reader with too much data all at once, because it will obscure the information.

Jorge used a data set Nathan provided for his Poverty in the US visualization project. The chart Jorge dislikes looks like this:

Big old ugly bar chart

It’s not really terrible, though I won’t deny it’s dull and maybe a bit cluttered. It’s more informative if less glossy than the infographic that was favored in Nathan’s challenge. But Jorge thinks that showing all of the state names was too much.

In some cases, an effective display might be to show two charts, the top five and the bottom five, and ignore the data in between.

Five Worst Bars
Five Best Bars

But Jorge also wanted something interactive. So I put together a little dynamic chart that had all fifty bars but highlights the five closest to the current mouse location. As you move the mouse up and down along the chart, the highlighted region moves with the mouse.

Interactive Bar Chart

Pretty cool, and the actual chart (see below for a download) is cooler than the static image above.

Well, Jorge is a pretty tough customer. He doesn’t just want to highlight the bars under the mouse. He wants to expand them, and shrink the unhighlighted bars. The effect is rather like an accordion. It was all I could do to keep up with Jorge’s requirements, but I cobbled together an example that I think he’ll be happy with.

Not only are a handful of bars under the mouse highlighed, but the highlighted bars are big and fat, while the unhighlighted bars are small and unobtrusive. And it’s completely interactive.

Interactive Accordion Chart

Again, the dynamic chart in the sample download is way cooler than this screen shot. An advantage of the accordion chart is that the unhighlighted bars can be compressed, shrinking the chart without shrinking the highlighted region. This ability to fit the same dynamic information into a smaller space would make this a good candidate approach for an interactive dashboard.

You can download Accordion.zip, which contains two Excel 2003 worbooks. NoAccordion1.xls is the plain version, with highlighting but no accordion-like expansion and contraction. Accordion1.xls is the deluxe version, where the highlighted region expands to grab your attention, while the unhighlighted bars shrink into the background. Open each workbook with macros enabled, then click on the chart to activate the animation.

The data is on the second worksheet of each workbook, along with a set of helper columns with the calculations that make the charts work. The code is unlocked, and you’ll be surprised how simple it is. Hardly a dozen lines in each workbook.

Update – 12 February 2009

Colin has noted that the accordion chart above would be better if all bars were labeled. I’ve adjusted the Accordion chart so the unhighlighted bars have lighter labels.

Improved Interactive Accordion Chart

I’ve added labels for unhighlighted bars in the NoAccordion chart. In response to Colin’s other suggestions, I’ve also changed the mouse actions so that mousing past the bars doesn’t move the highlighted region, but instead clicking on a bar or label toggles that bar between highlighted and unhighlighted status. In this chart I’ve highlighted the New England states.

Improved Interactive Bar Chart

These workbooks are Accordion2.xls and NoAccordion2.xls, found in Accordion2.zip.

Update 2 – 12 February 2009

Dick Kusleika has made an adaptation to this chart that always displays the user’s home state. As he so astutely points out, “Does anyone really look at the top or bottom five before he finds his own state?” Read about it in Ego Charts.

Posted: Thursday, February 12th, 2009 under Dynamic Charts.
Tags: Chart Events, Interactive Charts, VBA.
Comments: 22

Microsoft MVP Logo

Display One Chart Dynamically and Interactively

Thursday, November 6, 2008 by Jon Peltier 53 Comments

Thursday, November 6, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Once again, I rely on Chandoo for inspiration for a charting tutorial. In Select and Show One Chart from Many he shows how to let a user choose which of several charts to show, in an interactive display. He uses data validation to enable the user to select from a finite list of charts, and uses a little-known image linking trick in Excel to show the selected chart. Basically, selecting an item changes a dynamic range so it refers to the range beneath the selected chart, and the picture is linked to this dynamic range.

Dynamic Final Report

This linked-picture trick is related to Excel’s Camera tool, which means two things. First, it’s pretty cool. Second, using it more than a few times will consume resources and slow down the workbook’s response. If you are linking to one picture of many, this is the way to do it, but if you merely want to show different set of data in your chart, it’s more than you need. In this post I will show you an easy and resource-friendly way to achieve the same effects in your dynamic report.

The Report Layout

I’ve set up my report as shown below. Cell F2 contains the selected quantity to be plotted, and I’ve named the cell “WhichChart”. The original data is in B17:E25, and a second data range linked to the first is in B28:E36. The borders around parts of the range B28:E36 show the chart source data range of the working chart, which I will discuss shortly.

Dynamic Report Layout

One or both of the data ranges can be moved to a different place on this sheet, or onto another sheet, so that the focus is on the chart itself.

Data Selection

To make the selection of a chart more flexible, I changed the data validation list source of cell F2 (“WhichChart”) so it refers to the range C17:E17 rather than a hard-coded list (see the dialog below). This way, if I change one of the headers, I don’t need to update the data validation list.

Dynamic Report Data Validation

I generally prefer using a dropdown control or a listbox. A dropdown always shows the drop down arrow icon, so the user knows he can choose something even when the cell is not selected. A listbox shows all options without having to go to all the trouble of dropping down a list. But data validation is pretty easy to use, so it’s really a matter of choice.

The Dynamic Chart

I then constructed a chart that contained all of Chandoo’s data and the formatting from Chandoo’s three charts. I used B18:B25 for the category (X-axis) labels, C17:E17 for the series names, and C18:E25 for the series Y data. To make the chart title dynamic, I selected the title, typed = in the formula bar, then selected the range C17:E17, and pressed Enter. The chart title then displayed the formula =Report!$C$17:$E$17. When multiple cells are selected like this, Excel concatenates the cell values, inserting a space between non-blank items.

Dynamic Report Original Chart

The Dynamic Data

The second data range is linked to the original data, with a twist. It compares the column header to the selection in F2 and shows or hides the column as appropriate. I selected the range C28:E36 with C28 the active cell, entered this formula, then pressed Ctrl+Enter to enter the formula into the entire selected range:

=IF(C$17=WhichChart,C17,"")

This hides the headers and Y data for the non-selected column of data. I changed the source data of the chart so that B29:B36 (purple outline) is used for the category (X axis) labels, C28:E28 (green outine) for the series names, and C29:E36 (blue outline) for the series Y data. I also changed the title link formula to =Report!$C$28:$E$28. The outline colors correspond to the colors that Excel highlights these ranges when the chart’s plot area or chart area is selected. When Sales is selected, the data range looks like this (with only the Sales column displayed):

Dynamic Report Data

and the chart looks like this:

Dynamic Report Intermediate Chart

Final Touches

This isn’t quite perfect yet, but it’s easy to finish. First, apply a custom number format of 0;;; to the data labels in the chart, so the zero values (corresponding to the blank cells) are not shown. Second, format any one of the series in the chart so that it has an overlap of 100, so that all columns overlap, and there is no horizontal space wasted for columns with no data. Here is the finished chart:

Dynamic Final Report

Download this dynamic chart (in a zipped workbook).

Posted: Thursday, November 6th, 2008 under Dynamic Charts.
Tags: Chandoo, Dynamic Charts, Interactive Charts.
Comments: 53

Microsoft MVP Logo

Ballistics Animation

Sunday, August 24, 2008 by Jon Peltier 7 Comments

I’ve done a handful of animations lately:

This example shows the position and path of a moving projectile which is launched at a given velocity and angle. The math is high school physics, with a little calculus and trig thrown in. If you want to see that, download the workbook (link below), or consult a textbook or a smart kid.

The workbook has a data entry area and my usual animation controls. When the angle and velocity are entered, a results table shows how long the projectile will be in flight, how long it will take to get to maximum altitude, and what altitude and distance the projectile will reach. Some assumptions were made to simplify the model, as in all physics problems. The ground is flat at sea level, and the effect of air resistance is neglected.

Ballistics Animation Demo