How to provide different options of customized tooltips on a Microsoft Excel chart and let the user decide which one to display
One of the previous posts described four different techniques how to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel:
Customizable Tooltips on Excel Charts
Two of the approaches used a camera object (aka linked picture) to allow more formatting options of the tooltip.
This idea can be taken one step further: with camera objects, you can easily provide more than one type of tooltip and let your user interactively select the tooltip which is most helpful for his analysis.
Today’s article describes how to enable your user to select from five different tooltips with one single click: dimensions and measures, only a dimension and three additional charts displaying further information on the data point currently hovered over with the mouse. The post describes the idea and the implementation and of course makes the example workbooks available for free download.
The Idea – Let the user decide which tooltip shall be displayed
The basic idea is to predefine a certain number of tooltips showing different views on additional information of the selected data point (i.e. the one hovered over with the mouse) and let the user decide on the fly, which one is most useful for the analysis.
In our example, we define 5 different tooltips and let the user select one with a simple list of radio buttons next to the chart:
Note: Like in the previous post the examples for today’s article are using a data set of fertility rate (children per woman) and life expectancy (in years) of more than 200 countries. This time however, the data includes all years from 1950 to 2013 (data source: Gapminder).
Now, let’s have a look at the results first: which tooltip types are provided?
Tooltip 1: Country, Region and Measure Values
The first option is very close to the tooltip used in the previous post. It displays the name of the country, an icon of its flag, the region, the exact values of the plotted measures and the population as a third measure which is not visualized in the main view:
Tooltip 2: Flag and Country Name
The second tooltip is the minimalistic option. It only shows the flag icon and the name of the country:
Tooltip 3: Trail (XY Scatter over time)
Option #3 shows country, flag icon and the same type of XY scatter chart (fertility rate on the X-axis and life expectancy on the Y-axis) in the tooltip, but for the selected country only and for all years, i.e. the trail or development of this country over time. The red dot highlights the selected year in the main view:
Tooltip 4: Sparkline Fertility Rate over time
Tooltip 4 takes a closer look at the development of the fertility rate in the selected country over time with a sparkline. Again, the red dot highlights the selected year:
Tooltip 5: Sparkline Life Expectancy over time
Last option is a sparkline again, this time showing the development of life expectancy:
Many more or different views on the data could be displayed in the tooltips, no doubt about it. However, with these 5 versions and the possibility to easily switch from one tooltip type to another by simply clicking on the radio buttons, the user of the workbook already has an interesting additional interactive option for exploring and analyzing the data.
The Implementation
The example in this article is based upon the techniques #2 and #4 described in the previous post, i.e. either taking advantage of the chart events (#2) or an invisible ActiveX Label sitting on top of the plot area (#4) plus a camera object to display the tooltip.
You may assume that quite a few changes of the VBA code would have been necessary for implementing the user-defined tooltip options, but this is not the case. All enhancements to make the feature available are done by standard Excel worksheet functionality.
Here is an overview of what has to be done to get from one static tooltip to 5 different options:
Step 1: The Data
The database on worksheet [Data] was enhanced by adding all years from 1950 to 2013. Furthermore, to facilitate data lookups, an ID was added to the data consisting of the country name and the year.
Step 2: The Radio Buttons and the Control worksheet
First step is to add five radio buttons and texts on the dashboard and link them to a new target cell on the worksheet [Control]. Everything else on the [Control] worksheet stays as it has been in the original workbooks of the previous post.
Step 3: The Calculations
On the worksheet [Calculations], the data sources for the charts are prepared. The sheet contains three data tables:
- the first ([tab_xy_scatter_data]) consolidates the data source for the main chart
- the second one ([tab_tooltip_chart_data]) calculates the data sources used in the tooltip charts
- finally [tab_hovered_point] is a one row table for highlighting the data point currently hovered over in the main view.
The highlighting with the red dot in the main chart and in the tooltip charts is done by the usual, well-known charting trick in Excel: the data source of the charts consists of two data series: the first one with all the data for all data points (formatted in grey) and the second one which has the measure value only in the row of the selected country or year and an #N/A in all other rows. The second data series is then formatted with a red fill color and a slightly larger marker size.
The formulas to calculate these series are simple: IF, MATCH, INDEX and some string concatenations. Not much to explain here, I think.
Step 4: The Tooltips
On the worksheet [Tooltips], the views of the different tooltip options are created in 5 different ranges:
Some static texts, some links to the [Control] worksheet, some formulas using INDEX to get the measure values, an embedded XY scatter chart and two embedded line charts, based on the data consolidated on sheet [Calculations] (see above). Nothing new under the sun.
The flags are camera objects linked to a named formula. We will come to this in a minute.
Step 5: The Named Ranges
Next, we define a name for each cell range on the [Tooltips] sheet:
Step 6: The Named Formula for the Flags
The flags inside these ranges are also camera objects. Worksheet [Flags] contains a sorted list of all countries and an icon of their flags:
To display the flag of the currently selected country, the camera objects inside the tooltips are linked to the named formula “myFlag”:
The named formula “myFlag” uses a simple INDEX function, looking up the currently selected country in the [Flags] worksheet and returns what is in the cell (the flag icon).
Step 7: The Named Formula for the Flags
Finally, we use another named formula called myTooltip to switch between the five named ranges defined in step 5 based on the target cell of the radio buttons:
This is done by a CHOOSE formula which selects the tooltip range based on the target cell of the radio buttons.
That’s it.
The Download Link
The download link below contains three workbooks:
- a fully-fledged version based on the ActiveX Label control approach including the flags. This version was used to create the screenshots and the explanations above
- a simplified version of the ActiveX Label control workbook: it provides the same functionality as the first workbook, but does not contain the flag icons
- a workbook using the chart events approach, also in a simplified version, i.e. without the flag icons
Here is the zipped folder for free download:
Disadvantages
As mentioned above, the workbooks of today’s article are built upon the techniques described in the previous post. Thus, they come with the same disadvantages of the techniques #2 and #4 already mentioned there.
Having said that, the fully-fledged workbook with the flags comes with two additional drawbacks:
- keeping the flag icons inside the workbook quadruples the file size (more information here: Excel Oddity: Camera Objects bloat File Size)
- the tooltips are less responsive because of the flags and the additional camera objects, i.e. it takes a bit longer until the tooltip appears when hovering over a data point
Please decide for yourself whether spicing up the tooltip with a flag is worth accepting those two disadvantages.
Important Advice
The workbooks provided above were developed using Microsoft Excel 2016. I recently noticed that with opening the workbooks in an earlier version (e.g. 2010), the size of the inner plot area of the chart and/or the size and position of the Label control may be changed by Excel. I have no clue why.
Hence, if you are using the workbooks with an earlier version than Excel 2016, you may have to adjust and align the plot area and the label control to have the same size and the same position. To do so, go into design mode on the developer tab, select the Label and resize and reposition it until it sits exactly on top of the inner plot area of the chart and finally turn off the design mode again.
I tried to do this automatically via VBA code, but I failed. It is pretty easy to align the sizes of the inner plot area and the label, but I couldn’t manage to position the label automatically. If anyone has an idea how to do this, I’d appreciate if you would let me know.
Acknowledgement
It is certainly no surprise that it was again Leonid Koyfman (Leonid's LinkedIn Profile), who provided me with the idea for this article. Many thanks, Leonid. You are a true inspiration.
Stay tuned.