Build Dynamic Tooltips in Excel: Step‑by‑Step Tutorial

Step 1 – Use Formula to Fetch Data from Another Sheet

• In a new worksheet, create a data table with columns L No and Name. In the first column, input the serial number like 1,2,3, etc. And in the second column, enter names in a random serial from the names of the Dataset worksheet.
• Create a Tooltip column under Column D. Enter the following formula in cell D5.

=IFERROR(VLOOKUP(C5,Dataset!$B$4:$D$10,{2,3},FALSE),"")

The VLOOKUP function returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the lookup_value.

• Use the Fill Handle tool to AutoComplete the output in the remaining cells.

• Select columns D and E and right-click on the mouse. From the context menu, click on the Hide option to hide these two columns.

Step 2 – Assign VBA Code

• Right-click on the sheet name tab and click on View Code on the context menu.

• Enter the following VBA code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
' Clear existing comments
For Each rng In Me.UsedRange
If Not rng.Comment Is Nothing Then
rng.Comment.Delete
End If
Next rng
If Target.Count > 1 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
If Target.Row < 5 Then Exit Sub
Dim tooltipText As String
tooltipText = "Birth Date: " & Target.Offset(0, 1).Value & _
vbCrLf & "Age: " & Target.Offset(0, 2).Value
Target.AddComment tooltipText ' Add comment with tooltip text
Target.Comment.Shape.TextFrame.AutoSize = True ' Adjust the _
comment box size automatically
End Sub

• Return to the worksheet. Click on any cell of the Name column and it will show the tooltip with the person’s Birth Date and Age. It is dynamic.

3 Methods to Create Static Tooltip in Excel

Method 1 – Utilize Data Validation Feature to Generate Static Tooltip

Steps:

• Navigate to the Data tab.
• Choose Data Tools from the Data Tools group.
• Click the Data Validation icon.

• The Data Validation window will open.
• Go to Input Message.
• Enter the tooltip title in the Title section.
• Enter a message in the Input Message box.
• Press OK.

• Select cell D4.
• It will produce the output.

Method 2 – Create Tooltip Using Excel VBA

Steps:

• Choose the working sheet as the active sheet.
• Go to Developer.
• From the Code group, select Visual Basic.

• Click Insert followed by Module to get a Module Box.

• Enter the formula below.

Sub AddToolTip()
Dim target As Range
Set target = Range("D4")
With target.Validation
      .Delete
      .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = "Formula Column"
      .InputMessage = "Please, do not modify the Age column. All of the following cells in this field contain a formula."
      .ShowError = True
  End With
End Sub

• Press F5 or click Run.

• Select cell D4.
• We will get the output as shown below.

Read More: How to Display Tooltip on Mouseover Using VBA in Excel

Method 3 – Apply Link Option to Create Static Tooltip

Steps:

• Select the cell D4..
• Right-click in D4.
• The Context bar will pop up.

• Choose the Link
• The Insert Hyperlink window will open.
• Click the Place in This Document icon from the Link To section.
• Enter D4 in the Type The Cell Reference box.
• Go to ScreenTip.

• The Set Hyperlink ScreenTip window will open.
• Enter a message in the ScreenTip Text box.
• Press OK.

• Go to the Insert Hyperlink window and click OK.

• Use the Mouse cursor to hover over cell D4.
• We will get the output as shown below.

Download Practice Workbook

Related Articles

• How to Edit Tooltip in Excel
• How to Create Tooltip in Excel Chart
• How to Remove Tooltip in Excel
• How to Show Full Cell Contents on Hover in Excel
• How to Insert Excel Tooltip on Hover
• Excel Button Tooltip

<< Go Back to Excel Tooltip | Learn Excel

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio