Suppose you face a situation where you are not being able to see the full content in a cell but you don’t want to increase the column width either. Then, showing full cell contents on hover is the way to go. The main focus of this article is to explain how to show full cell contents on hover in Excel.
How to Show Full Cell Contents on Hover in Excel: 5 Quick Ways
To explain this article, I have taken the following dataset. It contains the grades of 5 students in 4 subjects on different tests. Here, you can not see the full cell contents for the column named Tests. I will explain 5 quick ways to show full cell contents on hover in Excel.
1. Using Link Command to Show Full Cell Contents on Hover in Excel
In this method, I will show you how you can use the Link command to show full cell contents on hover in Excel.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to show the contents on hover. Here, I selected cell C5.
- Secondly, go to the Insert tab.
- Thirdly, select Link.
After that, a dialog box named Insert Hyperlink will appear.
- Firstly, write the cell address you selected in the Type the cell reference section.
- Secondly, select the sheet you are in.
- Thirdly, select ScreenTip.
Now, the Set Hyperlink ScreenTip dialog box will appear.
- After that, write the full contents of the cell as ScreenTip text.
- Next, select OK.
- Then, select OK.
Finally, you will see a link has been inserted and if you hover your mouse cursor over the cell the full contents of the cell are showing.
- Next, insert hyperlinks to the other cells in the same way.
Here, I am not using the links as they lead to the same cell. I only need the hover texts. So, I will remove the link format from these cells and change them to normal font.
- Firstly, select the cells where you want to change the format. Here, I selected cell range C5:C14.
- Secondly, Right-click on the selected cell range.
- Thirdly, select Format Cells.
After that, the Format Cells dialog box will appear.
- Next, go to the Font tab.
- Then, check the Normal Font.
- After that, select OK.
Finally, you will see that the font of the hyperlinks has changed to the normal font. Now, you will not see the hyperlinks but the links are still there. If you hover over the cell then you will be able to see the full cell contents.
2. Applying Notes Feature to Show Full Cell Contents on Hover in Excel
Here, I will show you how you can apply the Notes feature to show full cell contents on hover in Excel. Let’s see how it is done.
Steps:
To begin with, I will show you how to turn on the display on hover.
- Firstly, go to the File tab.
- Secondly, select the Options.
After that, a dialog box named Excel Options will appear.
- Firstly, select the Advanced tab.
- Secondly, select the Indicators only, and comments and notes on hover option.
- Thirdly, select OK.
Now, I will add the hover text.
- Firstly select the cell where you want to show the full cell contents. Here, I selected cell C5.
- Secondly, go to the Review tab.
- Thirdly, select Notes.
After that, a drop-down menu will appear.
- Then, select New Note from the drop-down menu.
Now, you will see a note will be added to the selected cell.
- After that, write the full cell contents of the selected cell as a note.
- Then, you can change the size of the note by selecting and dragging the border of the note box.
In the following picture, you can see my final note.
- Now, select any cell to hide the note. You can see that there is a note by the red triangle in the corner of the cell.
- After that, add notes to all the other cells.
Finally, you can hover over any cell to see the full contents of the cell. In the following picture, you can see that I have hovered on cell C8 and full cell contents are showing on a note.
3. Employing Comment Feature in Excel
In this method, I will explain how you can employ the Comment feature to show full cell contents on hover. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to show full cell contents on hover.
- Secondly, go to the Insert tab.
- Thirdly, select Comment.
After that, a comment box will appear.
- Then, write the full cell contents in the comment box.
- Next, select the marked button to add the comment.
Now, your comment will be added to the cell. You can see that by the purple mark on the corner of the cell.
- After that, add comments to all the other cells.
Finally, you will be able to see the full cell contents in the comment box on hover in Excel.
4. Use of Data Validation in Excel
In this method, I will show you how you can use the Data Validation feature to show full cell contents on hover in Excel. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to show the full cell contents on hover.
- Secondly, go to the Data tab.
- Thirdly, select Data Validation.
After that, a dialog box named Data Validation will appear.
- Firstly, select the Input Message tab.
- Secondly, check the Show input message when cell is selected option.
- Thirdly, in the Input message write the full cell contents.
- Next, select OK.
Now, you will be able to see full cell contents on a message box when the cell is selected.
- After that, add message boxes to all the other cells. Now, you will be able to see the full cell contents in a message box when a cell is selected.
5. Using Macros to Show Full Cell Contents on Hover
Here, I will show you how you can show full cell contents on hover in Excel by using Macros. Let’s see the steps.
Steps:
After that, the Visual Basic window will open.
- Firstly, select the Insert tab.
- Secondly, select Module.
Here, a module will open.
- Then, write the following code in the module.
Sub Hover_Text()
   Dim row_no As Integer
   For row_no = 5 To 14
        With Cells(row_no, 3)
               If ActiveSheet.Cells(row_no, 3).Value <> "" Then
                   .ClearComments
                   .AddComment
                   .Comment.Visible = False
                   .Comment.Text Text:=Cells(row_no, 3).Value
                   .Comment.Shape.ScaleWidth 1, msoFalse, msoScaleFromTopLeft
                   .Comment.Shape.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
               End If
           End With
       Next
End Sub
Code Breakdown
- Here, I created a Sub Procedure named Hover_text.
- Then, I declared a variable named row_no as Integer.
- After that, I used a For Next Loop to run the Sub Procedure through the cells I want to.
- Next, I used the With Statement to skip the requalifying the name of the object.
- Then, I used an IF Statement to check if the cell is non-empty. If the cell is non-empty then the code will clear the existing comments at first by using the ClearComments method.
- After that, I use the AddComment Method to add comments to the cells. Then, the code will show the value in the selected cell as a Comment.
- Next, I declared the Height and Width of the comment box.
- Then, I ended the IF Statement.
- After that, I ended the With Statement.
- Finally, I ended the Sub Procedure.
Now, Save the code and go back to your worksheet.
- Firstly, go to the Developer tab.
- Secondly, select Macros.
Here, the Macro dialog box will appear.
- After that, select Hover_Text as the Macro name.
- Then, select Run.
Now, you will see that notes are added to the cells you selected in your code. Finally, you will be able to see the full cell contents if you hover on a cell in Excel.
How to Remove Hover Text in Excel
In this section, I will show you how you can remove the hover text you added in Excel. Let’s see how you can do it.
Steps:
To begin with, I will show you how you can remove the hover texts you added by using the Link command.
- Firstly, select the cells where you added the hyperlinks to show the full cell contents on hover.
- Secondly, Right-click on the selected cells.
- Thirdly, select Remove Hyperlinks.
Now, if you hover on any cell, you will see that the full cell contents are not showing anymore.
Here, I will show you how you can remove the hover texts you added by applying the Notes feature.
- Firstly, select the cells that contain the hover text.
- Secondly, Right-click on the selected cells.
- Thirdly, select Delete Note.
Now, you will see that if you hover on a cell you won’t be able to see the full cell contents on hover. In the following picture, you can see that I hovered on cell C9 and no hover texts are showing.
Here, I will show you how you can remove the hover text from the cells that you have added by employing the Comment feature.
- Firstly, select the cells where you added the comments.
- Secondly, Right-click on the selected cells.
- Thirdly, select Delete Comment.
Now, the comments will be deleted and if you hover on a cell you will not be able to see the full cell contents.
Here, I will explain how you can remove hover text from the cells you added by using the Data Validation feature.
- Firstly, select the cell where you want to remove the hover text.
- Secondly, go to the Data tab.
- Thirdly, select Data Validation.
After that, the Data Validation dialog box will appear.
- Firstly, select the Input Message tab.
- Secondly, uncheck the Show input message when cell is selected option.
- Thirdly, select OK.
Now, you will not be able to see the full cell contents in a message box when a cell is selected.
You can also use a VBA code to remove Hover text.
- To begin with, follow Method-5 to bring the VBA Editor.
After that, I will write another code for removing the hover texts.
- Firstly, select the Insert tab.
- Secondly, select Module.
Now, a module will open.
- After that, write the following code in the module.
Sub Clear_Hover_Text()
   Dim row_no As Integer
   For row_no = 5 To 14
       ActiveSheet.Cells(row_no, 3).ClearComments
   Next
End Sub
Code Breakdown
- Here, I created a Sub Procedure named Clear_Hover_Text.
- After that, I declared a variable named row_no as Integer.
- Then, I used a For Next Loop to run the Sub Procedure through the column I want.
- Next, I use the ActiveSheet property to select the cells from the current sheet. And then I used the ClearComments method to remove the comments from the cells.
- Finally, I ended the Sub Procedure.
After that, Save the code and go back to the worksheet.
- Firstly, go to the Developer Tab.
- Secondly, select Macros.
Here, the Macros dialog box will appear.
- After that, select Clear_Hover_Text as the Macro name.
- Then, select Run.
After that, you will see that the notes are removed from the sheet.
Things to Remember
- If you are working with Macros then you must save the Excel file as Excel Macro-Enabled Workbook. Otherwise, the macros won’t work.
Practice Section
Here, I have provided a practice sheet for you to practice how to show full cell contents on hover in Excel.
Download Practice Workbook
Conclusion
In this article, I tried to cover how to show full cell contents on hover in Excel. Here, I explained 5 different methods of doing it. I hope this article was helpful for you. If you have any questions feel free to let me know in the comment section below.
