How to Create Dynamic Tooltip in Excel (with Easy Steps)

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.

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