How to Find Comments in Excel (4 Easy Methods)

We used comments and notes in Excel for various reasons starting by clarifying complex formulas, or review someone’s work, or simply just 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.


Download Practice Workbook

Download this practice workbook below.


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 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 way 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.

4 Suitable Ways to Find Comments in Excel

For the demonstration purpose, 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 are discussed here with elaborate explanations.

Find Comments in Excel


1. Using Show Comments Command from Review Tab

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 showing 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 comments native location.
  • And 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.

Read More: Creating and Editing Excel Comments to Cells – [An Ultimate Guide]!


2. 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 names 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, and indicators will be visible in the cell contents.

  • To change that, select the Indicators and notes, and comments on hover shown as 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 details tiles.

Modifying Excel Options to Find Comments in Excel

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

Read More: [Fixed!] Comments in Excel Far Away from Cell (3 Possible Solutions)


Similar Readings


3. Applying Comments Command from Menu Bar

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 comments native location.
  • And the tiles also will show you the location of the cells in the worksheet directly in the corner.

Find Comments in Excel

Read More: How to Reference Comments in Excel (3 Easy Methods)


4. Embedding VBA Code

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 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.


Conclusion

To sum it up, the question “how to find comments in Excel” is answered here by using Review tab commands, the Comments Command, and finally using 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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo