How to Find Comments in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

We used comments and notes in Excel for various reasons starting by clarifying complex formulas, reviewing someone’s work, or simply documenting our work. If you stumble upon a worksheet containing formulas and are baffled about how you can show those formulas or even extract them to a new worksheet, this article might be the right solution for you. In this article, we are going to show how we can find comments in Excel with adequate explanations.


Why Comments Are Useful in Excel?

There are various reasons a user should add comments and notes.

  • Sharing comments can help people to understand more clearly without creating too many distractions, cause we can hide comments in Excel and notes easily and they only show when we hover over them.
  • On top of that, these Excel comments have the option to edit and modify whatever the user wants. Users can resize the comment cells as well.
  • Adding comments on cells can enable users to document their work.
  • Best way to review work and send feedback.

For demonstration purposes, we select the below worksheet. In this worksheet, we are going to show students’ mark ranges in the Marks column and the students’ count of who fell into this range in the Number of Students column. Some comments have been made on the ranges. How we are going to visualize those comments is discussed here with elaborate explanations.

Find Comments in Excel


1. Using Show Comments Command from Review Tab to Find Comments in Excel

The Review tab provides different options in the Show, Hide, Add, Edit, and Delete comments and notes in the worksheet. You can also add them to the quick access toolbar for faster operation.

Steps

  • First, there are threaded comments on some of the cells in the given table.
  • The comment is based on the performance or the achieved marks on the exam.
  • The time of the comment is also been shown alongside the comment.

Using Show Comments Command from Comments Group to Find Comments in Excel

  • To view all of the comments at once, we first go to the Review tab.
  • Then in the Review tab, click on the Show Comments command on the Comments group.

Using Show Comments Command from Comments Group to Find Comments in Excel

  • After clicking on the Show Comments icon, you will notice that all the comments in the dataset are showing serially in one place.
  • If you click on any Comments Note tiles, it will instantly take you to the comment’s native location.
  • The tiles also will show you the location of the cells in the worksheet directly in the corner.

Using Show Comments Command from Comments Group to Find Comments in Excel

This is how we can find comments in Excel using the show comments option from the Comments group in the Review tab.


2. Finding Comments in Worksheet by Modifying Excel Options

If the notes and the comments on the Excel worksheets are not showing properly, then the comment tick indicator mark and the comment show while hovering option are probably turned off in the Advanced Excel options. We need to turn it back on in the Display section.

Steps

  • If you notice carefully, normally there should be a tick indicator mark over the comment cells. Highlighting that there is a comment or note that has been made on that cell.
  • But if you notice that there is no indication of the tick on the corner of the cells, then we need to first click on the Files option to go into the Excel options.

Modifying Excel Options to Find Comments in Excel

  • After clicking on the Files command, it will take us to the starting window of Excel.
  • In the starting window, click on the Options at the bottom of the window.

  • There will be a new dialog box named Excel Options.
  • In that box, go to the Advanced Options on the left side.
  • Then look for the Display options.
  • Under the Display options, the No comments, notes, or indicators option may be in select mode. This means no comments, notes, or indicators will be visible in the cell contents.

  • To change that, select the Indicators and notes, and comments on hover shown below. Click OK after this.

  • After clicking OK, you will notice all of the indicator tick marks are back showing which cell has comments. Hovering over those cells will show us the comments made on those cells in detail tiles.

Modifying Excel Options to Find Comments in Excel

This is how we can find comments in Excel modifying Excel options.

Read More: [Solved!] Comments Are Not Displaying Properly in Excel


3. Applying Comments Command from Menu Bar for Finding Comments

There is a default Comments button icon on the Excel worksheet, in the corner of the worksheet.

Steps

  • To view all of the comments in one place, click on the Comments icon in the corner of the worksheet.

Applying Comments Command in Ribbon to Find Comments in Excel

  • After clicking on the Comments command, you will notice that all the comments in the dataset are showing serially in one place.
  • If you click on any Comments Note tiles, it will instantly take you to the comment’s native location.
  • The tiles also will show you the location of the cells in the worksheet directly in the corner.

Find Comments in Excel


4. Using Excel VBA Code to Find Comments

A simple VBA macro may easily show all the comments and notes over an active worksheet in a new worksheet. Furthermore, we will be able to show their location. The user name who made the comments or notes.

Steps

  • From the Developer tab, go to Visual Basic.

Embedding VBA Code to Find Comments in Excel

  • Then click Insert > Module.

  • In the Module window, enter the following code.
Sub Extract_Comments()
Dim com As Comment
Dim x As Integer
Dim wrkt As Worksheet
Dim dt As Worksheet
Set dt = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub

For Each wrkt In Worksheets
  If wrkt.Name = "Comments" Then i = 1
Next wrkt
    
If i = 0 Then
  Set wrkt = Worksheets.Add(After:=ActiveSheet)
  wrkt.Name = "Comments"
Else: Set wrkt = Worksheets("Comments")
End If

For Each com In dt.Comments
  wrkt.Range("B4").Value = "Cell Reference"
  wrkt.Range("C4").Value = "Author"
  wrkt.Range("D4").Value = "Comments"
  With wrkt.Range("B4:D4")
    .Font.Bold = True
    .Interior.Color = RGB(189, 215, 238)
    .Columns.ColumnWidth = 20
  End With
  If wrkt.Range("B5") = "" Then
    wrkt.Range("B5").Value = com.Parent.Address
    wrkt.Range("C5").Value = Left(com.Text, InStr(1, com.Text, ":") - 1)
    wrkt.Range("D5").Value = Right(com.Text, Len(com.Text) - InStr(1, com.Text, ":"))
  Else
    wrkt.Range("B4").End(xlDown).Offset(1, 0) = com.Parent.Address
    wrkt.Range("C4").End(xlDown).Offset(1, 0) = Left(com.Text, InStr(1, com.Text, ":") - 1)
    wrkt.Range("D4").End(xlDown).Offset(1, 0) = Right(com.Text, Len(com.Text) - InStr(1, com.Text, ":"))
  End If
Next com
End Sub
  • Then close the window.
  • After that, go to the View tab > Macros > View Macros.

Embedding VBA Code to Find Comments in Excel

  • After clicking View Macros, select the macros that you created just now. The name here is Extract_Comments. Then click Run.

  • After clicking Run. You will notice all the comments in the active worksheet are now in the new sheet named Comments.
  • Not only that, the location of the comments mentioned in the Comment In the column, which the user made that comment in the Comment By Column, and actual comments in those cells in the Comment column.

Embedding VBA Code to Find Comments in Excel

This is how we can find comments in Excel using the VBA macro.


Download Practice Workbook


Conclusion

To sum it up, the question “how to find comments in Excel” is answered here by using the Review tab commands, the Comments Command, and finally a simple VBA macro. among all of them, the comment method is easier to understand and implement. The VBA method is less time-consuming but requires prior VBA-related knowledge to understand.

For this problem, a macro-enable workbook is attached where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.


Related Articles


<< Go Back to Comments in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo