Rubayed Razib Suprov
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 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.
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 showing alongside the comment.
- 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.
- 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.
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 Comments in Excel
Similar Readings
- How to Add Comment in Excel Formula
- How to Copy Comments in Excel
- How to Insert Picture in Excel Comment
- Vlookup to Copy Comments in Excel
- How to Filter Cells with Comments in Excel
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.
- 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.
This is how we can find comments in Excel modifying Excel options.
Read More: [Fixed!] Comments in Excel Far Away from Cell
Similar Readings
- How to Extract Comments in Excel
- How to Add Floating Comment in Excel
- Anchoring Comment Boxes 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.
- 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.
Read More: How to Reference Comments in Excel
Similar Readings
- How to Reply to a Comment in Excel
- Difference Between Threaded Comments and Notes in Excel
- [Solved!] Comments Are Not Displaying Properly in Excel
- [Fixed!] Excel Comment Only Showing Arrow
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.
- 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.
- 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.
This is how we can find comments in Excel using the VBA macro.
Read More: How to Make Flashcards in Excel
Similar Readings
- How to Add Comment in Excel
- How to Remove Comments in Excel
- How to Hide Comments in Excel
- How to Edit Comment in Excel
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.
