It is not difficult to build interactive charts in Excel that allow a user to selectively highlight a subset of the data for detailed examination. Excel’s semi-intuitive worksheet controls make this technique suitable for dynamic dashboard reports.
Background
Last week in Series Lines: Useful or Chart Junk?, I wrote about Excel’s “Series Lines” feature, and how it seems like a good way to clarify the data in a stacked column chart, until you implement it and realize it just adds chart junk to your chart. I proposed a panel chart to show the stacked chart data more clearly.
Gregor Aisch commented that my panel approach did not allow point-to-point comparisons as effectively as a simple line chart (despite the line chart’s clutter). So I put together the following example of an interactive chart that allows a user to compare a couple series in context of the whole data set.
Here is the original stacked column chart, with series lines cluttering up the space between columns.
Here is the panel-style chart I constructed to show the trends more clearly. As Gregor has pointed out, comparing individual point values (for example, 2008 for alpha and for epsilon) is difficult. Also, if there are many categories (alpha through omega, for example), the data will be compressed horizontally and will be impossible to read.
Our interactive chart will look like this. The user can selectively highlight data using the two listboxes next to the chart.
Interactive Chart: Data
We’ll use this data for our exercise. A1:G7 contains the data used in the previous discussions. A9:G11 contains repeated data for two of the series in the upper table. These are selected using Excel’s INDEX() worksheet function and the index values in H10 and H11.
Cell A10 contains the formula
=IF($H10>1,INDEX(A$1:A$6,$H10),"")which is copied into A11. Cell B10 contains the formula
=IF($H10>1,INDEX(B$1:B$6,$H10),NA())which is copied into C10:G10 and B11:G11. The way these formulas work is as follows:
If the index in column H is 1, column A looks blank and columns B:G contain #N/A, which is not plotted in a line or XY chart. If the index is greater than 1, the label from column A and the values from columns B:G are displayed in that row. By changing the values in H10:H11, we can change the data in their rows. If this data is plotted, then changing H10:H11 will change the chart.
Worksheet Controls
It’s nice to be able to modify a chart by changing numbers in a couple cells, but this becomes tedious after a while. Fortunately, Excel has a number of helpful controls that we can drop onto the worksheet to do this in an easier and more natural way. For choosing from among a list of items (alpha through epsilon), we can use a Combo Box or a List Box.
In Excel 2003 and earlier, you can access these controls by displaying the Forms toolbar. In Excel 2007 and later, you need to display the Developer tab*, then pick from among the Forms controls under the Insert button. (There are also ActiveX versions of these controls, and they have more extensive formatting capabilities, but ActiveX controls can misbehave.
* To show the Developer tab, you have to open Excel Options, and find the setting for the Developer tab.
In Excel 2007: Click on the big Office button in the top left of the window and select Excel Options; check the box in front of Show the Developer tab in the Ribbon.
In Excel 2010-2013: Click on the File tab and select Options; click the Customize Ribbon button on the left, and in the right list, check the box in front of Developer.
Here is the Combo Box button on the Developer tab.
And here is the List Box button.
Both the Combo Box and List Box display a list of items for the user to select. I prefer the List Box, because you can several items even without clicking on the control. The Combo Box only shows one item at a time, until you click the control. Of course, if there isn’t room to display a List Box, then the Combo Box works fine.
When you click on the control button in the ribbon (or on the Forms toolbar in ancient Excel), the cursor turns into a crosshair, and you can draw your control just like drawing any other shape. In this example I’ve made two identical controls, one for each series we allow the user to highlight. You could display two list boxes side by side, two combo boxes side by side, two combo boxes one above the other, or any other arrangement that works in the available space on the worksheet.
Even if I’m using multiple controls, I usually make one and format it, then make as many copies as I need, either by using Copy and Paste or by Ctrl-dragging. After copying, I change specific formats (such as the cell link, below).
When you right click on a control and choose Format Control, you are presented with this dialog. In a combo box or list box, the input range contains the list of items displayed in the control. The cell link is the cell that contains the index number of the selected list item, starting with 1 for the first item in the list. This is how the index values in H10:H11 will be changed.
With the controls formatted and the formulas already in place, play with the controls and make sure the data changes as expected. Note that multiple controls can use the same cell link. Changing one control will change the value in the linked cell, and consequently change any other controls using this cell. Likewise, changing the value in the cell will change the setting in the control.
Building the Interactive Chart
The data and the controls are all set up, so now we create our charts. Select A1:G6, and insert a line chart. Resize and format the chart as appropriate.
Now format all the series to de-emphasize them. Thin lines, small markers, light gray color.
It’s easier to keep these gray series, and add duplicate series with bolder formatting for the series we want to highlight. The lighter data is hidden behind the bolder series.
To add the highlighted series, copy A9:G11, select the chart, and use Paste Special to add the data as new series, with the First Row and First Column check boxes both checked. Format these series to stand out compared to the light gray background data, and add a series name data label to the last point of each.
Now arrange the controls next to the chart. The list boxes have ugly black borders, but I hid them behind unfilled rectangles with light gray lines, and I colored the chart area border to match.
In fact, you can relate the list boxes to the appropriate highlighted series by coloring the gray rectangles around the list boxes to match the series.
Using the Interactive Chart
The user can now highlight and compare two series, without interference from the rest of the data which is still shown in the chart. If no other series are selected in the list boxes (or combo boxes), the corresponding data values are all #N/A, so the points don’t even appear in the chart.
Any two series can be selected for comparison. Note how easily the opposite trends of series gamma and epsilon can be seen in this chart. In the original line chart it was very difficult to resolve these series, because the other series were not de-emphasized by shading them gray. Yet here we can still see where these two series lie compared to the overall spread of the data.
Daan says
Sweet!
I might keep the series name data labels for the muted versions so you have a better sense of what you are selecting (or you can better decide which category you want to select).
Calvin Graham says
Good to see a controls+formula based version of doing this. You can of course do this with some quick VBA and Event macros but Excel 2007 is always trying to prevent code/macros from running and sometimes going back to this way of doing things makes more sense.
Plus it’s a great gateway for people to see charts and interactivity working together. A well made dashboard ot set of charts you can play with adds so much. The other switcheroo to add to a timeline like this I suppose is a “years or months?” breakout option.
Jon Peltier says
Daan –
In cases where there are not too many series, the labels can be left in place.
Calvin –
In an ancient article I wrote about using controls to switch between units in time series. See Dynamic Charting By Dates (broken link: page no longer available) in TechTrax online magazine.
derek says
Where the number of options is intuitively ordered and small (as here, if the options really were “alpha to epsilon”, or unordered and very small (say, “England”, “France”, “Spain”) I often prefer spinners to list boxes, because they allow faster flipping between views.
Mladen says
Great read and smart approach to highlighting desired data. As Daan I would also prefer to keep dimmed series names, off course, depends of overall readability.
John Broggio says
Ummm – didn’t you not so long ago say that the y-axis should always start at 0?
Jon Peltier says
John –
What I’ve said is that, for a bar or column chart, the Y axis must contain zero. This is because data is encoded by the lengths of the bars. In a chart with markers, it is the position of the markers along a scale, so the requirement to start at zero is not relevant. Of course, modifying the scale affects the perceived magnitude of variation
Dave says
Sorry if this is a silly question, but how do you get the series labels to appear to the right of only the last point in the series? When I try to associate these labels, I get the name of the series next to every point in the series.
Jon Peltier says
Dave –
Before adding the label, select the series, then click again on the last point, so only that point is selected. Now add the label.
It’ll be easy now that you know how.
Jason says
Great article as always Jon. A minor issue I’m experiencing is with placing the unfilled box in front of the list box. It always goes behind, not in front. I tried the “Send to back” but that doesn’t work.
Are you placing the box in front to hide the black border of the listbox, or are you placing the box behind and making it a bit larger than the list box?
Excel 2007.
Jon Peltier says
Jason –
What do you know. I used 2010 for this tutorial, and I was actually surprised I could put the shape over the control. I had a premonition that it wouldn’t work, but it did.
Now that you mention it, I remembered that 2007 was really stupid about controls in the worksheet. All aspects of any kind of control. So I just fired up 2007 and tried this, and verified that it won’t work in 2007.
Wish I could help.
Jason says
Actually, if you put the rectangle behind the control and size it just a bit larger, it works out ok. I can barely see the black border of the control if I choose a darker color for the rectangle.
Jon Peltier says
Jason –
That just gives you a thicker dark border, doesn’t it? I guess if you’re color-coding it like I did in the last few charts, that’s fine. But if I’m not color coding, I don’t want a black border, I want something less obnoxious, so I use a light gray rectangle as in my first chart with listboxes.
Jason says
What I meant was if I use a darker shade of red and blue, the thin black line isn’t very visible.
Jon Peltier says
Jason –
I knew what you meant, I should have said it gives a thicker, dark colored border. It works to an extent, but it’s an example of something not quite right in 2007 that they'[ve corrected in 2010. Office 2007 has been likened to a beta for 2010, and Windows Vista as a beta for Windows 7. The new versions of Windows and Office are both due out within the next year, and I hope these are better than betas.
MrVines says
This is all well and good for data with like information but I have not been able to apply any of yours or Chandoo’s or a host others to my situation and I don’t think my scenario would be too uncommon. I hope with your experience that the following makes sense:
Series1 Series 2 Series3 Etc
XX 2 aaa 1 123 14 123 ?
YY 4 bbb 0 456 12 XX ?
ZZ 7 ccc 9 ccc ?
ddd 5 ??? ?
This basically says I’m collecting data say by depart (Series) and each depart has their own measuring points and # of occurances that could be the same or different than the other departments. All I want is to be able to do is select the dept in a drop down box on the Chart itself and have the data range populate accordingly. Is this too much to ask without a programatic solution (VBA)?
MrVines says
Wow that post looks horrible! Short of including the actual spreadsheet I’m working on I’m not sure how to make that mess look like I typed it up.
MrVines says
See if this works better…sorry about cluttering things up.
Series1 Series2 Series3 Etc.
XX 2 aaa 1 123 14 123 ?
YY 4 bbb 0 456 12 XX ?
ZZ 7 ccc 9 ZZ ?
ddd 5 ccc ?
??? ?
Jon Peltier says
MrVines –
Don’t worry about the formatting, I know what you mean.
What you ask can be done, but it’s rather complicated for a comment to a different post. Let me neaten up your sample data and work out a new tutorial.
TJ Rucker says
This was the closest Post to my issue so pardon any etiquette fouls.
I am trying to create two Multidimensional arrays.
1.) Store the Cell Addresses (A,1; A,2; ..etc.) of a grid into an array
2.) Store the Values of a Grid.
– also read the RGB Value of a cell and use that to color the cells of the first array that have a matching value in a column on the second.
Sub Yard_Bumps()
Dim VesselA As Variant
Dim BlockA As Range
Range
‘MyCell = ActiveCell.Address
Set BlockA = ActiveCell(2, 16)
‘ranges for two arrays.
BlockA = Range(“D16:Z16”, “D16:D21”)
VesselA = Range(“D28:H28”, “D28:D32”).Value
‘Looping structure to look at array.
For i = 1 To UBound(VesselA)
ActiveCell = BlockA(1, 1)
ActiveCell.Interior.Color = RGB(200, 160, 90)
Next
End Sub
Background: The second array is a list of Discharge and Load instructions for a Container Vessel. The Second Array is the Container Yard Positions.
Those positions being the actual Cells. By painting the cells a color (Vessel) we know where they are being placed or pulled from the Yard Positions.
Jon Peltier says
TJ –
Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.
Joe Kang says
Great idea to use Form Control to facilitate the highlighting!
When learning through, it inspired me to use 5 checkbox which allows to select any combination of Alpha to Epsilon.
(1) Cell link of the 5 checkbox is set to cell H2 to H6, showing True or False
(2) Cell I2 is entered with formula like =IF(H2=TRUE,ROW(),”N”), copy the formula to H6
(3) Cell H10 to H14 is entered with formula like =IFERROR(SMALL($I$2:$I$6,ROW(A1)),””) , copy the formula to H14
(4) Cell A10 is entered with formula like =IF(N($H10)>0,INDEX(A$1:A$6,$H10),””), copy the formula to A10:G14
By checking the checkboxes, the highlight can be done for any combination of Alpha to Epsilon.
In addition, to facilitate the layout of the 5 checkbox, insert a Microsoft Forms 2.0 Image (under ActiveX Controls, other controls), group the 5 checkbox into the image. As such, the checkboxes can be moved together easily on the worksheet or chart.
Harm says
Thanks man! Got it all sorted out without too many problems. Might be good in the tutorial to explain that the control form will change cell value it is linked to based on what is selected using the INDEX() formula.
Jon Peltier says
Harm –
Good point. Those controls work bidirectionally. Change the control, and the cell changes. Change the cell, and the control changes. You can have a setting on a master worksheet, and place a control to change this setting on all the sheets that use the setting, so you don’t need to switch between the sheet you’re analyzing and the master sheet.