User-defined Excel Chart Tooltips

How to provide different options of customized tooltips on a Microsoft Excel chart and let the user decide which one to display

User Defined Tooltips in Microsoft ExcelOne 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:

Selection of Tooltip via Radio Buttons - click to enlarge

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 1: Country, Region and Measure Values - click to enlarge

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 2: Flag and Country Name - click to enlarge

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 3: Trail - click to enlarge

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 4: Sparkline Fertility Rate - click to enlarge

Tooltip 5: Sparkline Life Expectancy over time

Last option is a sparkline again, this time showing the development of life expectancy:

Tooltip 5: Sparkline Life Expectancy - click to enlarge

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:

Tooltips - click to enlarge

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:

Tooltips Named Ranges - click to enlarge

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:

Flag Icons - click to enlarge

To display the flag of the currently selected country, the camera objects inside the tooltips are linked to the named formula “myFlag”:

Name Manager - Named Formula myFlag - click to enlarge

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:

Name Manager - Named Formula myTooltip - click to enlarge

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:

Download user defined chart tooltips (zipped folder with 3 Microsoft Excel 2007-2016 workbooks, 4.8MB)

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.

Add a Comment

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