Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)
Question from PowerPivotPro School!
Got a great question the other day from Oscar, a student in PowerPivotPro School…
[OSCAR]: “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”
My first thought was “no, not possible.” Then ten seconds later, a guerilla-style hack came to mind. And then, my reply:
[ROB]: “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”
The Trick: Hyperlinks to Nowhere!
Select a cell and choose Insert Hyperlink…
Your Hyperlink in Cell D5 Goes to Cell D5, Making the Hyperlink a “Do Nothing” Link
Now click on ScreenTip…
ScreenTip Button Lets You Enter a Tooltip
And that’s it!
You now have an “on hover” tooltip for cell D5!
End of blog post, right?
“Rob you SUCK! That’s incredibly tedious!”
Yeah, who wants to go through that process for every cell. Blech.
So, we create a “mirror” sheet, that has the same shape as our dashboard:
“Mirror” Sheet Has Same Shape and Location as Our Dashboard.
You Enter Desired Tooltips Here.
Then you MACRO That Sucker!
Go back to your dashboard sheet, make sure your mirror sheet is named “ToolTIps,” then run this macro called RunTheToolTipHack. It calls two other macros, also included below.
This macro “grabs” the tooltips from your mirror sheet and assigns them all to the cells on your main sheet. It’s magic.
(Anyone who makes fun of my macro code will be beaten with a nerf bat. Or worse, asked to improve it.)
CubeFormulasToolTips ActiveSheet.Name, “ToolTips”
Sub CubeFormulasToolTips(sSheet As String, sToolTipsSheet As String)
Dim oMainSheet As Worksheet
Dim oTipsSheet As Worksheet
Dim oRange As Range
Dim c As Range
Dim sFormula As String
Dim sAddress As String
Dim sToolTip As String
Set oMainSheet = Sheets(sSheet)
Set oTipsSheet = Sheets(sToolTipsSheet)
Set oRange = oMainSheet.UsedRange
For Each c In oRange.Cells
sFormula = c.FormulaR1C1
sAddress = c.Address
sToolTip = oTipsSheet.Range(sAddress).Value
If Left(sFormula, 5) = “=CUBE” Then
SetHyperlink sSheet, sAddress, “‘” & sSheet & “‘!” & sAddress, sToolTip, True, False
Sub SetHyperlink(sSheet As String, sCell As String, sDestAddress As String, sToolTip As String, bFormula As Boolean, bLookLikeLink As Boolean)
Dim oSheet As Worksheet
Dim sFormat As String
Dim c As Range
Dim iColor As Integer
Set oSheet = ActiveWorkbook.Sheets(sSheet)
Set c = oSheet.Range(sCell)
sFormat = c.NumberFormat
iColor = c.Font.ColorIndex
If bFormula = True Then
oSheet.Hyperlinks.Add Anchor:=c, Address:=”", SubAddress:=sDestAddress, ScreenTip:=sToolTip
oSheet.Hyperlinks.Add Anchor:=c, Address:=”", SubAddress:=sDestAddress, TextToDisplay:=c.Value, ScreenTip:=sToolTip
c.NumberFormat = sFormat
If bLookLikeLink = False Then
c.Font.Underline = xlUnderlineStyleNone
c.Font.Color = iColor
- Does this work with pivots? No, sadly, pivots don’t let you set hyperlinks on the cells. In Power Pivot land, this only works with cube formula reports.
- Can the mirror sheet be constructed using formulas? Yes, absolutely. No need to manually enter tooltips. In fact you could use cube formulas and text measures to FETCH COMMENTS FROM THE POWER PIVOT MODEL ITSELF! Cue maniacal laughter!!!!
- Will these tooltips work in the web version of Excel? Yes, they will. I have tested it.
- Wait, I didn’t think macros ran on the web version! That’s right, they don’t. But your macros don’t need to RUN on the server. You are merely using the macros to SET the tooltips. The macros don’t even need to live in your report workbook, they can live in your Personal Macro workbook.
- What happens when I want to change the comments? Well, you need to run the macros again.
- Wait, even if I am using formulas to fetch the comments??? Yeah. You refresh your Power Pivot model, refresh the dashboards, and even refresh the mirror sheet. Everything is updated. But your tooltips will still be the “stale” tips until you run the macro again.
- So this won’t work with Power Pivot autorefresh? Bingo. It won’t. If you have a Power Pivot server and have scheduled the reports to autorefresh, the tooltips will still be stale. You will need to download the file and re-run the macros.
- That’s a tragedy. Can you fix that with the HYPERLINK function? These are good questions, it’s like you were watching me work on this over my shoulder or something. No, the HYPERLINK function lacks an input for tooltip, otherwise we COULD do this all automatically.
- Shouldn’t we point this out to Microsoft and ask them to address this tragic shortfall? Yup, damn straight. On it.
- Why not just have a TOOLTIP() function and dispense with all of this fake hyperlink crap? Doubly damn straight, and doubly on it.
You Have to be a Real Nerd to Be Disappointed About This. Which, I am.
Am I Missing Something?
I have this spider sense feeling that a longtime Excel pro is going to now say “but Rob why didn’t you do X instead?” I am actually hoping that there IS a better approach here, and this post will bring that to the surface. So bring it on.