In the end, I moved beyond just firing an event when your mouse hovers over a data point; instead, I created the functionality to select a rectangle of data points to display information about them. See:
Because I’m now writing a book, I don’t really have time to go through what I did at length. But here’s a short summary.
1. I first made every cell into a square of the same size, which you can learn how to do by reading my “Most Squares Method.”
2. With each cell the same size, I now had a grid that I could turn into”hot spots” for the mouse rollover. I laid out my chart to use 36 (0 – 35) squares horizontally and 17 (0 – 16) vertically. There was really no good reason for why I picked these numbers; my choice was pretty arbitrary. However, for this method, the more squares you use, the more hotspots you create; thus, more squares means more precision.
3. I then created a mouse rollover technique to capture the row and column numbers set above. Using these numbers, I mapped them on to the charts grid (so for row two, I would do 2/17 * y-axis value to get an approximate mapping). With these mapping I could approximate where the hotspots would light up certain points that were within them
4. I didn’t want the user to be able to click on the graph and change its values. So I took a shape and placed it on top of the graph and assigned it to fire a macro on click. You can’t see the shape because the fill color is fully transparent. Reread that last sentence and note that I did not say I used “no fill color.” When you assign a macro to a shape, if you select “no fill,” Excel lets you select anything that is contained by the shape as if it’s not there. That would mean the user would be able to select the graph, which is exactly what I didn’t want. For the shape above, I simply set its transparency to 100%.
5. Finally, to make a long story short, the shape when clicked fires the macro that allows the user to draw the rectangle.
I know right now my work isn’t perfect (some data points aren’t selectable for example depending upon where you start the rectangle), but I like what I have now and don’t have much more time to work on it. I’ll leave it to you to put it to good use.
Let me know if you make something cool.
Download File
Details on Demand Rollover.xlsm
Jordan,great post and a very interesting approach.With regards to your initial idea: "to see if I could have a label appear – or just some event fire – upon the mouse entering a chart's datapoint's hotspot."I thought you might be interested in the following 2 articles I published some time ago on my blog:Better Chart Tooltips with Microsoft Excel 2010Bluffing Tableau Actions with Microsoft ExcelKind regardsRobert
Hey Robert,Thanks! I'm very familiar with your work – and it's quite an honor to have you post to this blog. Actually, I'm happy you posted your "Better Chart Tooltips with Microsoft Excel 2010" because I was considering linking to it (and a few others with similar approaches) at the end of the blog post.An advantage to what I've created above for presenting tooltips when putting your mouse over a point is that it does not require first selecting the chart. On the other hand, it's definitely not as easy – or as intuitive – to build as your method. Rollovers are still a bit unpredictable and the reason I moved beyond just displaying tool tips above is because I didn't think that results matched the effort involved. Indeed, I have used your tooltips method – and will probably still prefer it – for displaying tool tips for data points on charts. But if you do something really cool with this method – please do share! The best stuff will not come from me but from the real excel geniuses.
Jordan,thank you. I just recently found your blog via a link from Roberto Mensa on a LinkedIn Excel group (thanks Roberto!) and I did not yet read all of your articles, but what I have seen so far is really impressive. I will dig deeper into your work and if a good idea comes to my mind, I may "borrow" one of your techniques and use it for a post on my blog. With regards to my tooltips method: agreed, the fact that you have to select the chart first is a bit annoying and I was hoping I could get around this, but I failed. On the other hand Excel's insufficient default chart tooltips require the selection of the chart too, so most Excel users are familiar with this.I am looking forward to your next posts. Keep them coming!