How to filter cells with comments in Excel?
In daily Excel work, it is quite common to add comments to worksheets as a way to mark important notes, highlight specific details, or provide additional context for certain cells. Comments can significantly enhance collaborative work and data management. However, when dealing with large datasets, you might encounter situations where you need to quickly gather and review only those rows that contain comments, without manually inspecting each cell. Unfortunately, the standard Filter feature in Excel does not include an option to directly filter for cells with comments. To address this limitation, this tutorial explains practical ways to identify and filter cells with comments in Excel, making your data review process more efficient and organized.
Filter cells with comments by creating a helper column
Filter cells with comments by creating a helper column
If you want to filter and display only the rows that contain comments, you can achieve this by using a helper column to first identify which cells have comments. This approach involves using a custom User Defined Function (UDF) created with VBA, and then applying Excel's Filter feature based on the helper column. This method is especially recommended when you frequently need to review comments scattered across large or complex datasets.
1. Open the Microsoft Visual Basic for Applications editor by holding down the Alt + F11 keys in Excel. This window allows you to write and manage VBA code for your workbook. If you have not enabled the Developer tab, you can also access it from the ribbon via Developer > Visual Basic.
2. In the VBA editor, click on Insert > Module. This action will add a new module to your project where you can paste the macro code. Copy and paste the following macro into the new Module window. This function is designed to check whether a specified cell contains a comment and will return TRUE if it does, or FALSE if it does not.
VBA code: Identify the cells that contain comments
Function HasComment(r As Range)
'Update 20140718
Application.Volatile True
HasComment = Not r.Comment Is Nothing
End Function
After entering the code, make sure to save your changes and close the VBA editor by clicking the close (X) button or pressing Alt+Q. It is a good idea to regularly save your workbook in macro-enabled format (.xlsm) to ensure you do not lose any custom functions.
3. Return to your worksheet. In a blank column next to your data (for example, column C), enter the following formula in the cell adjacent to the first cell you want to check. Suppose you are checking cell B2, input this formula in C2: =HasComment(B2). This formula uses your custom function to check for comments. See screenshot:
4. Press Enter to apply the formula. To check other cells in the same column, drag the fill handle (a small square at the lower-right corner of the cell) down the helper column. This quickly applies the function to other rows. The results will appear as either TRUE (cell has a comment) or FALSE (no comment).
Tip: If you are working with large datasets, it is recommended to double-check that the formula has been correctly filled for the whole data range to avoid missing any rows with comments.
5. After identifying which rows contain comments, select the full range of your data—including the helper column you just filled. Next, go to the Data tab in the ribbon and click Filter. This action will add drop-down arrows to each column header, enabling you to filter the results more easily. See screenshot:
6. Click the drop-down arrow in the helper column and select only the TRUE option. This will temporarily hide all rows without comments and display only the data entries with comments. See screenshot:
7. Click OK to apply the filter. All rows that contain at least one comment will now be displayed together, making it convenient for you to review or process them. For clarity, the screenshot below shows the result after filtering:
8. Once your review or further processing is complete, you can choose to delete or clear the helper column (e.g., column C) if it is no longer needed. Remember, deleting the helper column will also remove the ability to re-filter by comments unless you repeat these steps.
Additional Tips and Precautions:
- This approach does not alter your original data and allows you to combine filtering with other standard Excel functions.
- When sharing your workbook with others, remind them that using custom functions requires macro-enabled workbooks, and some security settings may need to be adjusted to run macros.
- If you receive a #NAME? error in the helper column, double-check that the VBA function was named correctly and is present in your project.
- If comments are inserted using the "New Note" or "Threaded Comment" feature in newer versions of Excel, results may differ since VBA detects classic comments (notes) by default.
This method is suitable for anyone comfortable with basic VBA operations and who needs a flexible way to locate all comments within their dataset. However, it does require saving your file as a macro-enabled workbook, and macro settings must be compatible with your organization's policies.
If you encounter issues where the macro does not run as expected, check that macros are enabled in your Excel security settings. Also, confirm that you have saved your workbook as a macro-enabled file (.xlsm). If other errors or inconsistencies arise, verify that your helper column references match the actual positions of your data.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in