Create Tableau lookalike Chart Tooltips on your Microsoft Excel 2007/2010 Charts
Let’s call a spade a spade: Microsoft Excel’s chart tooltips are lame.
When talking about tooltips I refer to textboxes that appear when hovering over a data point of a chart with the mouse.
Excel’s chart tooltips show by default the name of the data series, the point (e.g. the category) and the values. There is no built-in feature to change anything about them except for turning the tooltips off in Excel’s options.
However, chart tooltips are a great interactive feature. They give the user the opportunity to easily explore the data and get additional information about selected data points on the chart.
Have a look at Tableau as a benchmark. Tableau allows you to display any information in the tooltips (i.e. any given dimension or measure), to format them and to replace the field names by whatever you choose. There is even much more. For instance: my highly esteemed Tableau blogging colleague Andy Cotgreave showed on the outstanding blog of the data studio how to add conditional formatting to tooltips and even how to implement pseudo bar charts inside of a tooltip. Fantastic work, Andy.
Back to Microsoft Excel. Can we do at least something similar in Excel? Let’s stay humble. I am not dreaming of great formatting features or even the fabulous things Andy did with Tableau. I am talking about just some nice and meaningful tooltips displaying more information than the Excel default does. Is this possible?
Yes, it is.
Today’s post shows how to improve Microsoft Excel’s chart tooltips using a textbox and some VBA. As always, providing the Excel 2007/2010 workbook for free download.
The Challenge
A picture (two pictures in this case) says a thousand words:
Microsoft Excel’s chart tooltips are not sufficient, not helpful, sometimes even confusing. In this specific case, I do not care about latitude and longitude. I am much more interested in the name of the mountain, the elevation, the prominence and the first climbers:
Have a look at the Tableau equivalent:
No further comment necessary, I guess.
Here is today’s challenge: provide tooltips in Microsoft Excel as close as possible to what Tableau does by default.
The Idea
The basic idea is inserting a shape object (textbox) to the worksheet and some VBA code to fill it with data and make it visible only if the user hovers over a data point with the mouse.
The Implementation
You may have already noticed that I am referring back to a workbook I already published here on Clearly and Simply: the 50 most prominent summits on earth, a workbook I provided to show how to bluff Tableau Actions with Microsoft Excel.
I decided to enhance this model with better tooltips. The following explanations will refer to the workbook provided back then.
Before starting with adapting your workbook, please be advised that this approach only works properly with Microsoft Excel 2007 or 2010. It will not work with Microsoft Excel 2003 and earlier.
Here is a how-to tutorial to make better chart tooltips with Microsoft Excel 2007 / 2010:
- Create the texts to be displayed in the tooltips
Insert an additional column on the worksheet [Calculations]. Concatenate names of the data fields (strings), values of different columns and a carriage return (CHAR(10)) to one string. The formula looks like this:
=Data!B5&", ("&Data!C5&")"&CHAR(10)&
"Elevation: "&TEXT(Data!F5;"#.###")&" m"&CHAR(10)&
"Prominence: "&TEXT(Data!G5;"#.###")&" m"&CHAR(10)&
"First ascent by: "&Data!H5" ("&Data!I5&")"Simple string concatenation using the ampersand and CHAR. I bet you have done this before.
- Assign a name to the header of this new column to be referred to by the VBA code (“myTooltipBubbleStart” in my example workbook).
- Add a textbox from the shapes menu to the dashboard, format it the way you want to have your tooltips look like and assign a name to the textbox (“myshpTooltip”).
- The VBA – Part I
We enhance the Workbook_Open Sub to make sure the tooltip looks the way we want to. If you are interested, have a look at the workbook posted for download. Nothing special. Just changing color, size and name of the shape.
- The VBA – Part II
We need an additional sub to calculate the points per pixel. I “stole” this piece of code from the fabulous book “Professional Excel Development” by Stephen Bullen, Rob Bovey and John Green. One of the best publications on Excel VBA Development I know. If you are interested in VBA, I highly recommend to buy this book.
- The VBA – Part III
Finally we have to set up a routine to handle the mouse moves. It is a sub called myEmbeddedChart_MouseMove in the already existing class module clsChartEvent. This sub detects the position of the mouse and – if the mouse hovers over a chart point – assigns the defined tooltip text to the textbox and makes it visible. Otherwise the textbox is set back to invisible. If you are interested in the details, have a look at the VBA of the workbook provided for download below.
That’s it. Simple text formulas, a named range, a text box and some lines of VBA code.
The Result
Here is a screenshot of the result. Agreed, not as nice as the tooltips in the Tableau workbook, but – in my humble opinion – better than Excel’s default:
Our VBA code ensures to position the tooltip properly: it usually displays the tooltip to the bottom right of the data point. If there isn’t enough space for the tooltip on the chart, the tooltip is displayed on the bottom left or (like in the following screenshot) on the top left of the data point. This makes sure to always display the tooltip within the chart area.
The Disadvantages
It is a workaround. Workarounds always come with their own limitations and disadvantages. This one is no exception:
- Unlike the clicking on a data point to update the Google view and the bar chart, the tooltips are not working seamlessly. You have to activate the chart first to make the tooltips working. This is not intuitive and the user has to know this.
- As mentioned above, the approach works only with Microsoft Excel 2007 and 2010. The workbook doesn’t crash if you open it with Excel 2003. You just don’t see the tooltips.
- The approach is based on creating the tooltips in a cell and assigning these strings to a textbox. Formatting options are next to nothing.
A lot of disadvantages, no doubt about it. But it works and it provides better tooltips than Excel’s default without too much effort needed for implementation. Everything else than perfect, but one step up from my point of view.
Let me know what you think.
The Download Link
Here is the workbook for free download:
Download Bluffing Tableau Actions and Tooltips (Microsoft Excel 2007, 468.6K)
More things to come on Excel and Tableau soon.
Stay tuned.
Leave a Reply