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.
How to Filter Cells with Comments in Excel: With Easy Steps
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.
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.
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 the Status Cell. we have to click on the icon.
- Furthermore, select only the True option from the search box so that we can view the cells with comments only.
- Moreover, press OK.
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, the 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.
Download Practice Workbook
You can Download the practice workbook from here.
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.
