Power BI lookalike Tooltips in Microsoft Excel

How to create Power BI lookalike Tooltips in Microsoft Excel Charts

1,361 words, ~7 minutes read

Tooltips are an extremely helpful feature to explore and understand data.

When hovering over a data point of a chart, a textbox appears and displays the values and – if applicable – even additional information.

I love working with Microsoft Excel. I truly love the application. But I will make no bones about it: Microsoft Excel’s native chart tooltips are mostly pointless. There is no option to adjust or format them, to link the content to a cell range or the like. They only display default information and this is pretty useless in most cases. All you can do is to turn them off in Excel’s options to get them out of your hair.

I already posted a few articles about this subject, the first one back in 2010, including the same rant as above: Better Chart Tooltips with Microsoft Excel 2010.

Microsoft didn’t do anything about it. Excel’s chart tooltips are still as lame as they have been in the past 20 years. This is remarkable, because Microsoft provides much better tooltips in Power BI. So, they are aware that tooltips are helpful, but apparently they don’t see the necessity to let Excel users take advantage of it, too. So we are still on our own here.

Although I already provided a few options to display better tooltips in Excel (e.g. Customizable Tooltips on Excel Charts), I would like to come back to this topic again.

Power BI does not only automatically insert tooltips on charts, it also shows a vertical line across the entire plot area and displays the tooltip even if the mouse cursor is not above the plotted data series.

That intrigued me. Today’s post will provide 2 options how to mimic Power BI lookalike tooltips on Microsoft Excel charts. As always, the post comes with the Excel workbooks for free download.

The Inspiration and the Objective

Recently I stumbled across the Economic Research website of the Federal Reserve Bank of St.Louis. I found a great chart showing the real gross domestic product per capita including the context of the US recessions as shaded areas in the background.

Professionally done chart, no question about it and kudos to the FRED team. However, the chart itself wasn’t what captured my interest.

The tooltip did. Comparable to Power BI, a tooltip is displayed when hovering over the chart including a vertical line across the entire plot area. That being said, it is not exactly the same as in Power BI, because the tooltip is shown above the data point, while Power BI shows the tooltip at the current mouse position. Furthermore, I like the little halo effect around the data point to highlight the current selection.

Have a look:

On top of that, they also provide a beautiful range slider beneath the chart to define the timeframe to be displayed. Great filtering feature, but I covered range slider filter controls here already: Range Filter Slider Control in Microsoft Excel, so let’s focus on the tooltip implementation.

Today’s objective is to provide the exact same functionality and look-and-feel on a Microsoft Excel chart. Here is a sneak preview of the result:

Previous Posts about Tooltips in Excel

If you are interested in improving tooltips in Excel, you may want to check out my previous posts about this topic:

The journey started 10 years ago with Better Chart Tooltips with Microsoft Excel 2010.

Tooltips on Microsoft Excel Tables explained how to display tooltips not on a chart, but on a cell range.

Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard used my friend Jordan Goldmeier’s stellar idea of a UDF fired in a hyperlink: How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell.

Customizable Tooltips on Excel Charts introduced another technique (ActiveX Label sitting on top of the chart) to avoid the necessity of activating the chart first.

Finally, User-defined Excel Chart Tooltips let the user decide, which kind of tooltip shall be displayed with option buttons close to the chart.

Two different versions

Today’s post provides two alternatives for Power BI lookalike chart tooltips in Excel:

Version 1 uses the Chart Mouse Move Event. The chart has to be activated first (i.e. clicked on) before the tooltips are displayed. Everything else is more or less the same as described in the section “Technique 1” of the post Customizable Tooltips on Excel Charts. Today’s implementation uses a different shape and the position of the tooltip is above the data point instead of bottom right.

Version 2 has an invisible ActiveX label control sitting on top of the plot area of the chart as described in section “Technique 3” here. Again, the positioning of the tooltip is different to make the text appear either above the current mouse position or above the according data point of the line chart. The user can select the option using the radio buttons beneath the chart.

The Implementation

Truth be told, there is not so much of an innovation in today’s post. The workbooks are heavily relying on the code and approach already explained in Customizable Tooltips on Excel Charts.

A few things are different, though:

• The chart is a combination chart with three data series plotted as line charts and a 100% stacked column chart for the shading of the recession areas
• The halo-effect around the selected data point is produced by a dedicated data series, showing only the selected point with a larger marker and a partly transparent fill color
• The tooltip shape is a callout with a down arrow instead of a simple rectangle
• The vertical line is an error bar of a data series (the data point currently selected)., i.e. standard functionality in Excel
• The VBA code is pretty much the same, except for the tooltip now positioned above the data point or the mouse cursor instead of bottom right. Different formulas to calculated the position, but no rocket science
• Finally, in the second version the code checks, which option the user currently selected (at cursor position or above the chart series) and positions the tooltip accordingly

Sounds a lot, but it really isn’t. If you are interested, download the workbook and have a look for yourself.

The Pros and Cons of the 2 Versions

Both versions are workarounds. Necessary only, because Microsoft Excel does not provide useful chart tooltips per default. As workarounds, they both come with advantages and shortcomings.

Version 1 (chart mouse move approach) works for any zoom level and window size. However, the chart has to be activated first, before the tooltip becomes visible, Furthermore, the tooltip only appears when the mouse is directly over the chart series and not if it is above or below the line.

In version 2 (ActiveX label approach) the chart does not have to be activated. The tooltip appears whenever the mouse is over the plot area of the chart right away. And the tooltip is shown even if the mouse is above or below the data series. Furthermore, the user can decide at if the tooltip shall be positioned at the current position of the mouse or at the according data point of the line chart. Sounds great, but it comes with a major downside, too: it only works if the zoom level of the Excel sheet is at 100% and the entire chart is visible in the window.

It’s down to you to decide, which one to prefer.

The Result

Here is what you get:

As an example, this view shows the ActiveX Label version with the option selected to show the tooltip above the data point.

The Download Links

Download Power BI lookalike tooltips – Chart Area (zipped Excel workbook, 61K)

Download Power BI lookalike tooltips – ActiveX Label (zipped Excel workbook, 61K)

Acknowledgements

I have done that before, but I am not getting tired of pointing out that all of this would have not been possible without the brilliant groundwork done and kindly shared by Microsoft Excel MVP Jon Peltier and Microsoft Excel MVP Andy Pope.

Thank you very much, gentlemen.

Stay tuned.