How to count number of cells with comments in Excel?
When working with large Excel worksheets, it’s common to use cell comments to annotate data, provide feedback, or highlight important notes for yourself and collaborators. However, there are times when you may need to quickly find out how many comments exist in your worksheet—whether for data auditing, quality assurance, or simply to review workbook documentation. Accurately counting commented cells can help you ensure important notes are not missed or deleted inadvertently. This tutorial will introduce several practical methods to count the number of cells that contain comments in Excel, covering both built-in features and flexible code-based approaches. Each solution can be chosen based on your familiarity with Excel and your workflow needs.
Count number of cells containing comments with Go To Special function
Count number of cells containing comments with User Defined Function
Count number of cells containing comments with VBA code
Count number of cells containing comments with Go To Special function
Excel’s Go To Special feature provides a straightforward way to visually select and count all cells within your worksheet that contain comments (also referred to as “Notes” in modern Excel versions). This method is especially quick and effective if you need a simple count and are dealing with worksheets where comments are visible and accessible.
1. On the ribbon, click Home > Find & Select > Go To Special. This will open the Go To Special dialog. See screenshot:
2. In the Go To Special dialog box, under the Select section, select the Comments option. This option will highlight all cells that contain traditional comments in the worksheet. See screenshot:
3. Click OK to apply your selection. All cells containing comments within the active worksheet will now be highlighted. You can view the count of selected cells at the status bar at the bottom of the Excel window—they will be displayed as the number of cells selected. See screenshot:
Applicable Scenarios: This method is ideal for quick checks or when you want to visually inspect where comments are located. It does not require coding knowledge and is accessible in all standard versions of Excel.
Limitations: Please note that with this method, any blank cells that have comments attached may not be counted in some Excel versions, because the selection may skip non-value cells depending on prior settings or Excel build.
Tip: You can also use the Ctrl + G shortcut to open the Go To dialog, then click the Special button to access Go To Special quickly.
Practical Advice: If your worksheet contains a mix of traditional comments and new threaded notes (introduced in recent Excel versions), the Go To Special function counts only the legacy comment type. For threaded “Notes” or “Comments,” you may need to use VBA-based methods shown below for more accurate results.
Troubleshooting: If the status bar does not display the “Count,” right-click the status bar and ensure the “Count” option is checked in the status bar menu. If nothing is selected, make sure you are in the correct worksheet and that comments are indeed present.
Count number of cells containing comments with User Defined Function
While the Go To Special function is quick, it may not always provide a comprehensive or dynamic count, especially if you need to regularly monitor the number of commented cells, including those that are blank or containing formulas. For more flexibility, you can use a User Defined Function (UDF) through Excel's VBA editor. This approach enables you to count all cell comments—including blank cells—directly within worksheet formulas.
1. Press ALT + F11 simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the VBA editor, click Insert > Module, and paste the following code into the blank module window that appears.
VBA code: Count number of cells with comment
Function CountComments(xCell As Range)
'Update by extendoffice
Application.Volatile
CountComments = xCell.Parent.Comments.Count
End Function
3. After you have pasted and confirmed the code, save and close the VBA editor. Go back to your Excel worksheet. In any blank cell where you want to display the count, enter the following formula (replace A1:A100 with the actual range you need):
=countcomments(A1:A100)
4. Press Enter to execute the function. The cell will now show the total number of cells with comments within your specified range.
Parameter Explanation: The range within the formula (e.g., A1:A100) determines which cells will be scanned for comments. Adjust this to match the part of the worksheet you are interested in.
Advantages: This method counts comments even in empty cells, works well in dynamic data scenarios, and updates automatically when new comments are added or removed.
Precautions: Macros must be enabled for the function to run. This method won't count modern threaded comments (called “Notes” in some Excel versions) unless the VBA code is specifically updated for those.
Practical Tip: This UDF approach is very helpful if you need the comment count to refresh automatically as you update your worksheet. If you copy this formula to other cells, remember to adjust the cell references to match the desired ranges.
Count number of cells containing comments with VBA code
If you prefer a one-time operation to count all comments—including those in empty cells—or want to quickly process large ranges, you can use a ready-to-run VBA macro. This approach is efficient for non-repetitive counting or gathering statistics about comment usage in your workbooks.
1. Press ALT + F11 to open the Microsoft Visual Basic for Applications window in Excel.
2. In the VBA editor, click Insert > Module to add a new module. Paste the following code into the module window:
VBA code: Count number of cells with comment
Sub CountComments()
'Update by extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeComments)
If VBA.Err > 0 Then
MsgBox 0
Else
MsgBox WorkRng.Count
End If
End Sub
3. After you have pasted the code, press F5 (or click the Run button 
) to execute the macro. A dialog will appear asking you to select the range where you want to count comments. See screenshot:
4. After selecting the desired range, click OK. A message box will display the total number of comments found within your selection.
Advantages: Macros are suitable for large, complex data ranges and can handle both blank and filled cell comments efficiently.
Precautions: This macro requires macros to be enabled in your workbook and may prompt a security warning upon opening. Save your work before running macros, as unintended changes cannot be undone using the standard Undo command.
Troubleshooting: If the macro does not prompt you to select a range, ensure the module was properly inserted and that no syntax errors are present in the code. Save your workbook as a macro-enabled file to avoid losing your code.
Applicability: Good for occasional audits or reporting on workbook annotation usage, particularly when you need a summarized result rather than a formula-driven cell.
Additional Suggestion: For environments with strict macro/security restrictions, or for collaborating with users unfamiliar with VBA, try the Go To Special method or seek help from add-ins such as Kutools for more advanced comment management features.
Related articles:
How to list all comments to a new worksheet or workbook in Excel?
How to find and replace text within comments in Excel?
How to quickly insert a comment to multiple cells in Excel?
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