Hyperlink Legends to Highlight a Series

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.

image

 

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

clip_image002[9]

Now we have a nice chart legend that highlights the data series when I click on it:

image

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:

clip_image002[11]

image

2 Replies to “Hyperlink Legends to Highlight a Series”

  1. Ha ha, there’s more than one way to skin a cat. Excel’s power means with a little coding (chart events for my approach, the ParamLink add-in for yours) you can do amazing things.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.