How to Extract All Comments in A Worksheet in Excel?

Sharing is caring!

Microsoft Excel allows users to add comments and notes to cells, helping teams collaborate, leave feedback, or annotate data. However, reviewing these comments manually can be time-consuming, especially when working with large worksheets. Thankfully, Excel provides options to extract all comments in a worksheet using VBA (Visual Basic for Applications).

In this guide, we will explain step-by-step how to use VBA code to pull all comments from your worksheet. You’ll also learn how to create a custom function to extract comments from individual cells. Whether you’re analyzing feedback or auditing spreadsheets, these methods will save you time.

Types of Comments in Excel

Before we proceed, it’s important to understand the two types of comments in Excel:

Both comment types can be extracted using VBA macros, but you need to handle each appropriately.

Method 1: Using VBA to Extract All Comments Into a New Worksheet

This method uses a macro to collect all traditional comments from a worksheet and display them in a new sheet called “Comments”.

Step 1: Open the VBA Editor

• Press Alt + F11 to open the VBA editor.

Step 2: Insert a New Module

• In the Project Explorer, right-click your workbook name.
• Choose Insert → Module.

Step 3: Paste the VBA Code

Copy and paste the following VBA code:

Sub ExtractComments()
    Dim ExComment As Comment
    Dim ws As Worksheet
    Dim CS As Worksheet
    Dim i As Integer
    
    Set CS = ActiveSheet
    
    If CS.Comments.Count = 0 Then Exit Sub
    
    For Each ws In Worksheets
        If ws.Name = "Comments" Then i = 1
    Next ws
    
    If i = 0 Then
        Set ws = Worksheets.Add(After:=ActiveSheet)
        ws.Name = "Comments"
    Else: Set ws = Worksheets("Comments")
    End If
    
    ws.Range("A1").Value = "Comment In"
    ws.Range("B1").Value = "Comment By"
    ws.Range("C1").Value = "Comment"
    
    Dim rowNum As Integer
    rowNum = 2
    
    For Each ExComment In CS.Comments
        ws.Cells(rowNum, 1).Value = ExComment.Parent.Address
        ws.Cells(rowNum, 2).Value = ExComment.Author
        ws.Cells(rowNum, 3).Value = ExComment.Text
        rowNum = rowNum + 1
    Next ExComment
End Sub

Step 4: Run the Macro

• Press Alt + Q to close the editor.
• Go to the Developer tab, click Macros.
• Select ExtractComments and click Run.

Step 5: View the Results

A new worksheet named Comments will appear with the extracted data:

This provides a clear overview of all feedback across your sheet.

Method 2: Extract Comments from Individual Cells Using a Custom VBA Function

If you want to check comments for specific cells, use this method to create a custom function in Excel that can retrieve comments like a formula.

Step 1: Open the VBA Editor

• Press Alt + F11 to launch the editor.

Step 2: Insert a New Module

• Right-click on your workbook in the Project Explorer.
• Choose Insert → Module.

Step 3: Paste the Custom Function Code

Paste the following code into the module:

Function GetCommentText(rng As Range) As String
    Dim cmt As String
    On Error Resume Next
    
    ' Check for threaded comments (modern comments)
    Dim threadedComment As CommentThreaded
    Set threadedComment = rng.CommentThreaded
    If Not threadedComment Is Nothing Then
        cmt = threadedComment.Text
    End If
    
    ' Check for traditional comments (notes)
    If cmt = "" Then
        cmt = rng.NoteText
    End If
    
    On Error GoTo 0
    
    ' Return "No comment" if no comment exists
    If cmt = "" Then
        GetCommentText = "No comment"
    Else
        GetCommentText = cmt
    End If
End Function

Step 4: Save the Workbook

• Save the file as a macro-enabled workbook with the .xlsm extension.

Step 5: Use the Custom Function

Now go back to your worksheet and use the formula:

=GetCommentText(A1)

Replace A1 with any cell address. Excel will return the comment (or note) from that cell. You can also drag the formula to apply it across a range.

Choosing the Right Method to Extract All Comments in Excel

Use this table to decide which method works best for your needs:

Important Tips Before You Begin

Here are a few important things to keep in mind before using VBA:

• Enable Macros: Macros must be enabled for the code to run.
• Save Your Workbook: Always save your Excel file before running VBA to avoid unexpected changes or errors.
• Check for Threaded Comments: If you’re using Excel 365, some comments may be threaded, requiring the updated handling shown in the custom function.
• Use .xlsm Format: If you save your workbook in .xlsx, the macro and custom functions will not be retained.

Final Thoughts

Extracting all comments in an Excel worksheet may seem tricky, but using VBA macros makes it quick and efficient. Whether you want a full summary or cell-by-cell feedback, these methods help you manage and organize comments for better decision-making.

If you’re working with multiple contributors or managing spreadsheets with ongoing reviews, extracting comments is a must-have technique to streamline your workflow.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.