Sometimes adding a comment to a cell in Excel helps a lot in understanding the dataset. We may wish to filter the cells with comments attached to them, this way we can see all the cells having comments at once. In this article, I will show the procedures to filter cells with comments in Excel.
Download Practice Workbook
You can Download the practice workbook from here.
Step-by-Step Procedures to Filter Cells with Comments in Excel
In Excel 365, we have Notes and Comments. The comment section basically displays threaded comments. In the earlier versions, we used Comments instead of Notes. Here, I will demonstrate the stepwise procedure to filter cells with comments which are also applicable to Notes in Excel 365. I have used a dataset having Salesperson and Actual Sales. Also, a few of the cells contain notes which I will filter.
STEP 1: Add Helper Column in Dataset
- First, we need to include a Helper column Status in the dataset beside the column having comments.
Read More: How to Add Comment in Excel Formula (2 Easy Ways)
STEP 2: Open VBA Window & Run Code
- Now, press Alt + F11 to open the Visual Basics for Applications (VBA) window.
- Then, from the VBA window projects select the active sheet and right–click.
- Consecutively, select Insert > Module.
- Instantly, a code window will appear.
- Write the following code there.
Function HasAnyComment(k As Range)
Application.Volatile True
HasAnyComment = Not k.Comment Is Nothing
End Function
- Afterward, save the module by pressing Ctrl + S and close the window.
In the code,
- We created a custom function named HasAnyComment which will give True if the cell reference in the argument contains comments.
Similar Readings
- How to Populate Comment from Another Cell in Excel (and Vice Versa)
- How to Reference Comments in Excel (3 Easy Methods)
- Anchoring Comment Boxes in Excel (3 Easy Ways)
- How to Extract Comments in Excel (3 Suitable Examples)
- How to Remove Pop-up Comments in Excel (4 Suitable Ways)
STEP 3: Use Custom Function
- Now, go to the worksheet and in Cell D5 write the following formula.
=HasAnyComment(C5)
- Simultaneously, press Enter.
- Furthermore, use the Fill Handle to copy the formula in the cells below.
- As a consequence, we will see True for cells having comments and False for cells without comments.
STEP 4: Sorting Status Column to View Cells with Comments
- First, select the range of cells in the Status column for filtering.
- Then, select Sort & Filter > Filter in the Home ribbon.
- Further, we will see a small filter icon beside status Cell. we have to click on the icon.
- Furthermore, select only True option from the search box so that we want to view the cells with comments only.
- Moreover, press OK.
Read More: [Fixed!] Comments in Excel Far Away from Cell (3 Possible Solutions)
Final Output
Finally, we will see the cells of the dataset containing comments only.
How to Find Comments in Excel
We can find the comments in the dataset very easily following different ways. In this section, I’ll discuss 2 such ways to find comments in Excel.
1. Using Find and Replace Option
Utilizing the Find and Replace option of Excel to find comments is an excellent choice. Let’s walk through the procedures.
- First, go to the active sheet with comments in it.
- Then, select Find & Select > Find in the Home ribbon.
- Afterward, Find and Replace box will appear.
- After that, write the comment text in the Find What section.
- Also, select comments in the Look in section.
- Finally, press OK and we will see the list of comments at the bottom of the box.
2. Using Review Tab
We can view the comments quite simply in Excel. I will show the stepwise procedures below.
- First, open the worksheet with comments.
- Then, select the Review tab and choose Show Comments.
- Finally, we will see a Comments pane on the right side of the worksheet which will show the comments in the worksheet.
How to Find Notes in Excel
Notes are more popular than comments in Excel because they are present in all versions of Excel. Let’s walk through the stepwise procedures to view the notes in Excel.
- First, open the worksheet containing notes.
- After that, press F5 to open the Go to box.
- From there, select Special.
- In the Go to Special box, select Notes then, click OK.
- Finally, we will see the cells containing notes will be selected for easy vision.
Read More: Convert Comments to Notes in Excel (3 Suitable Ways)
Conclusion
Comments are very widely used in Excel. Here, I have shown the procedures to filter cells with comments in Excel. If you have any problem with the procedures, please leave a comment. Please, visit our ExcelDemy Website for more articles regarding Excel.
Related Articles
- How to Extract Comments from Word Document into Excel
- How to Hide All Comments in Excel (3 Simple Methods)
- Reset Comment Position in Excel (With Easy Steps)
- How to Export Instagram Comments to Excel (with Quick Steps)
- How to Read Full Comment in Excel (6 Handy Methods)
- [Fixed!] Cannot Delete Comment in Excel (3 Suitable Solutions)