Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Filtering for Comments.
by Allen Wyatt
(last updated June 21, 2018)
Robert has a worksheet that has comments included in various places. He wonders if it is possible to filter the rows in a data table so that only those rows that include comments in a particular column will be displayed.
The filtering capabilities of Excel don't provide a way that you can automatically check for the presence of comments, but there are a couple of ways you can approach a solution. One possible solution is to follow these general steps:
If you prefer, you can create a user-defined function that will let you know if a particular cell has a comment associated with it. The following is a simple way to make such a determination:
Function CellHasComment(c As Range) Application.Volatile True CellHasComment = Not c.Comment Is Nothing End Function
Now you can use a formula such as the following within a worksheet:
When the formula is executed, it returns either True or False, depending on whether cell B2 has a comment or not. You can then use Excel's filtering capabilities to display only those rows that have a True returned by the formula.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3508) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Filtering for Comments.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Filter a large worksheet, and Excel will helpfully recalculate every time you apply a different filter. This can get ...Discover More
The filtering capabilities of Excel are indispensable when working with large sets of data. When you create a filtered ...Discover More
Some types of data may have certain fields that contain partially identical information. In such cases you may want to ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.