This bumps chart shows you the English Premier League 2007 – 2008. It allows you to highlight and compare two teams by clicking a team in in the table or a line in the bumps charts.
Interactively highlighting a data series in a large data set is very powerful. All lines in the data set are set to light Gray to show you the big picture and the patterns and general tends in the data set. Once you have identified an interesting data series you click it and we highlight it with a bright, saturated color. This has the effect of bringing it into the foreground and allows the user to see the details in the context of the other data series.
As a bumps chart has the lines equally spaced on the value axis you can put an Excel table next to the chart that serves as a legend on one side, and as a detailed ranking table on the other side.
In my last post Hyperlink Legends to Highlight a Series I introduced the main technique for the legend highlighting. You put the legend outside the chart in an Excel table. In the bumps chart, when you click in the table, a ParamLink formula, swaps Team1 and Team2 and assigns the selected team to Team1, if not already assigned, otherwise it assigns it to Team2.
=ParamLink(,AP4,1,”Team2″,Team1,IF(SelectedTeam=Team1,”Team2″,”Team1″),SelectedTeam)
The red and blue lines dynamically refer to the data selected for Team1 and Team2 with these formulas:
=INDEX(TeamList,MATCH(Team1,ChartData,0))
=INDEX(TeamList,MATCH(Team2,ChartData,0))
But what about selecting data series in the chart, just by clicking on a line? Jon presented various ways to select a data series using VBA. I have to admit that I first thought that this VBA method would be problematic, as my experience with chart events was that you have to activate the chart first before it can receive and handle events. However, by playing a little with Jon’s code I discovered that this is not true for all click related events. I used Jon’s code to handle events in embedded charts and added a select handler:
Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim strSelectedTeam As String
If ElementID = xlSeries Then
strSelectedTeam = myChartClass.SeriesCollection(Arg1).Name
Range(“Team2”) = Range(“Team1”)
Range(IIf(strSelectedTeam = Range(“Team1”), “Team2”, “Team1”)) = strSelectedTeam
myChartClass.ChartArea.Select
Else If ElementID = xlPlotArea Then
myChartClass.ChartArea.Select
End If
End Sub
The Select event handler, basically does the same the ParamLink () formula does, swapping and assigning the teams.
Altogether a nice interactive Excel chart, just one click into the ranking table or the chart and you see the performance of your favorite team!
