Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting Comments in a Worksheet.
Written by Allen Wyatt (last updated September 24, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Sanjib would like to get a count of all the comments in a worksheet. Unfortunately, Excel doesn't include a function that allows you to access this information. You can, however, get the value manually by using this process:
Figure 1. The Go To Special dialog box.
If you want to get the number of comments and place it into a cell, then you need to use a macro to create a user-defined function.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Function CountComments(rCell As Range)
Application.Volatile
CountComments = rCell.Parent.Comments.Count
End Function
This function grabs the value of the Count property for the Comments collection. It is then returned by the function to the worksheet. To use it in your worksheet, enter a formula such as the following:
=CountComments(A1)
The cell address you use in the formula is unimportant; it should simply reference a cell on the worksheet for which you want the count.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12363) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Counting Comments in a Worksheet.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Pasting the contents of a single cell into a comment is rather easy. Pasting the contents of a range of cells is a ...
Discover MoreOne way that you can view comments in a worksheet is to have them appear when you hover the mouse pointer over a cell. If ...
Discover MoreWant to automatically move the contents of a cell into a comment for that cell? It's easy enough to do by using the macro ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-09-24 13:03:11
J. Woolley
The Tip's CountComments function considers legacy unthreaded comments (Notes). For new threaded comments, use this:
CountComments = rCell.Parent.CommentsThreaded.Count
My Excel Toolbox includes the following dynamic array function:
=ListComments([AllSheets],[Threaded],[SkipHeader])
This function returns one row for each comment with the following columns: Worksheet, Cell, Author, Comment (text). It works with threaded and unthreaded comments.
In older versions of Excel you can use ListComments with the SpillArray function described in UseSpillArray.pdf. You can also get count values for the active worksheet with these formulas:
=VBAResult("ActiveSheet.Comments.Count")
=VBAResult("ActiveSheet.CommentsThreaded.Count")
See https://sites.google.com/view/MyExcelToolbox/
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments