In a recent post Jon presented a way to dynamically hover over the chart legend to highlight a data series. Jon’s method is very smart, as it really shows the capabilities of the event rich Excel chart programing model. The downside of this approach is that it requires you do code some VBA and only works with chart sheets. The chart events also work in theory for embedded charts, but you have to activate the chart, in order to make Excel handle your events, which is one click you want to avoid.
So I picked up Jon’s idea and tried to combine it with ParamLink. In The Missing Link I introduced the (free) ParamLink Add-In. It implements a new hyperlink formula ParamLink(). When the hyperlink is followed the formula can set cell values or define names.
To highlight a data series you have to click on the legend (hyperlink).
These are the steps to create a hyperlink in a chart:
Insert a couple of ParamLink formulas next to your chart that serve as a legend.
=ParamLink(,”Series Name 1″,1,”HighligtedDataSeries”,”=”&”Series Name 1″)
=ParamLink(,”Series Name 2″,1,”HighligtedDataSeries”,”=”&”Series Name 2″)
ParamLink inserts a hyperlink, that sets the name HighligtedDataSeries when it is clicked. When you click on
=ParamLink(,”Jon’s Excel and Chart Pages”,1,”HighligtedDataSeries”,”=”&”Jon’s Excel and Chart Pages”)
the name HighligtedDataSeries is defined as =”Jon’s Excel and Chart Pages”.
Now we define a name that dynamically selects the data series based on the selection in HighligtedDataSeries.
ChrtData := INDEX(Data!$B$2:$F$65,,MATCH(HighligtedDataSeries,Data!$B$1:$F$1,0))
Now insert a new data series that refers to =<SheetName>! ChrtData
Now we have a nice chart legend that highlights the data series when I click on it:
To give the user the visual feedback about which data series is selected we can add some conditional formatting to the legend that highlights the selected legend entry: