How to Filter Excel Rows by Cells That Contain Comments
Question details
The user needs to filter their spreadsheet to view only the rows where specific cells contain comments, encompassing both legacy notes and modern threaded comments.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reviewing and auditing large datasets by isolating rows that have user comments attached to them.
- Observed behavior
- Excel's built-in filter and 'Go To Special' features do not directly support filtering rows by modern threaded comments.
Because this solution requires the use of Visual Basic for Applications (VBA), ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to retain the code.
Use a VBA Function to Create a Helper Column
Create a custom VBA user-defined function (UDF) to detect legacy and threaded comments, then filter your data based on the results.
Excel's standard AutoFilter only evaluates cell values and basic formatting, so it cannot natively filter by comment metadata. A custom VBA function is the most reliable workaround to evaluate the Comment and CommentThreaded objects.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor.
Click Insert > Module from the top menu. Write a custom User Defined Function (UDF) that checks if either the legacy 'Comment' object or the modern 'CommentThreaded' object is present in a target cell, returning TRUE or FALSE.
Return to your worksheet and insert a new helper column next to your data. Type your custom formula (for example, =HasComment(A2)) and drag the fill handle down to apply it to all rows.
Select the header of your new helper column, navigate to the Data tab, click Filter, and check only the TRUE option to display rows containing comments.
Experience Seamless Data Management with WPS Office
If you frequently work with complex data and custom macros, WPS Office provides a lightweight, highly compatible, and cost-effective solution for all your spreadsheet needs.
- 1. Download the Software: Visit the official WPS website to download the free installation package for your device.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to complete the setup.
- 3. Open Your Workbooks: Launch WPS Spreadsheet and open your existing Excel files to continue working seamlessly.
Frequently Asked Questions
Why can't I filter rows by comments using the standard Excel filter?
Excel's standard AutoFilter only evaluates cell contents (like text, numbers, and dates) and basic cell formatting (like cell color or font color). It does not have a built-in parameter to detect comment metadata.
What is the difference between legacy comments and threaded comments?
Legacy comments (now often called Notes) are standard static text boxes attached to a cell, usually indicated by a red triangle. Threaded comments are modern features that allow multiple users to have ongoing conversations with direct replies, represented in VBA by the CommentThreaded object.
How do I select all cells with comments without filtering?
You can press F5 or Ctrl+G to open the Go To dialog box, click the Special button, and select Comments (or Notes). This action will highlight all cells containing comments on your active worksheet at once.
