How to Consolidate All Comments in Excel (3 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

In order to add important information to a cell, we often use comments in Microsoft Excel. There might have multiple comments in a worksheet. It can be a lot of comments considering the entire workbook. Nothing to worry …! We can consolidate all comments or notes in a single worksheet. In this article, I will try to explain how to consolidate all comments in Excel in 3 simple ways.

How to Consolidate All Comments in Excel


Download Practice Workbook

You can download the practice workbook from here.


3 Ways to Consolidate All Comments in Excel

The main way to consolidate all comments in Excel is to use VBA. This is because Microsoft Excel doesn’t provide any direct feature for this purpose as of the latest version. In the following sections, we are going to cover 3 different variations of the code that will consolidate all comments from a particular sheet or workbook. The final one will only be available for print preview.


1. Consolidate All Comments of a Worksheet with VBA

A simple VBA code can consolidate all comments of a worksheet quite easily. To insert the VBA code, you have to go to the Developer tab and follow the following steps.

Steps:

  • In order to consolidate all comments on a worksheet, we can use VBA. For this, consider a dataset with comments first.

Dataset with Multiple Comments

  • Then, go to Visual Basic from the Developer tab.

Opening Visual basic Editor

  • Select Module from Insert.

Creating a Module

  • Now, write the following VBA code and press F5 to run the code.
Sub ConsolidatingComments()
Dim WSheet As Worksheet
Dim Rng As Range
Dim cell As Variant
Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
Set WSheet = Sheets.Add
WSheet.Range("B4") = "Cell Address"
WSheet.Range("C4") = "Comment"
i = 5
For Each cell In Rng
    WSheet.Range("B" & i) = cell.Address
    WSheet.Range("C" & i) = cell.Comment.Text
    i = i + 1
Next cell
End Sub

VBA to Consolidate Comments of a Worksheet

  • All the comments will be consolidated in a new sheet according to the given command in the VBA code.

Consolidating Comments in a New Worksheet

  • You can decorate the consolidated comments according to your choice.

Final Otput of Consolidating all Comments of a Worksheet with VBA


2. Consolidate All Comments of Workbook with VBA

We can also consolidate all the comments of the entire workbook. For this, we can follow the following procedures.

Steps:

  • We can consolidate all the comments of an entire workbook along with their worksheet and cell value with VBA.

Dataset with Multiple Worksheets with Comments

  • For this, just apply the following VBA code after inserting a module.
Sub combineAllComments()
Set ConsolidateWS = ActiveWorkbook.Worksheets.Add  
ConsolidateWS.Name = "Comments Consolidation"
ConsolidateWS.Range("B4").Value = "WorksheetName"
ConsolidateWS.Range("C4").Value = "Address"
ConsolidateWS.Range("D4").Value = "Player Name"
ConsolidateWS.Range("E4").Value = "Comment"
i = 4
For Each WS In ActiveWorkbook.Sheets
    If WS.Name <> "Comments Consolidation" Then
        Set SelectedRng = Nothing
        On Error Resume Next
        Set SelectedRng = WS.Cells.SpecialCells(xlCellTypeComments)
           
        If Not SelectedRng Is Nothing Then
            For Each Rng In SelectedRng
                i = i + 1
                ConsolidateWS.Range("B" & i).Value = WS.Name
                ConsolidateWS.Range("C" & i).Value = Rng.Address
                ConsolidateWS.Range("D" & i).Value = Rng.Value
                ConsolidateWS.Range("E" & i).Value = Rng.Comment.Text
            Next Rng
        End If
    End If
Next WS
End Sub

VBA to Consolidate Comments of a WorkBook

  • Thus, we can consolidate all the comments of an entire workbook quite easily.

How to Consolidate All Comments in Excel


3. Consolidate All Comments While Printing

We can have all the comments of a sheet for printing in a separate sheet.

Steps:

  • We can have all the consolidated comments in a sheet while printing. For this, go to the Page Layout tab and click on the extension key of the Page Setup tab.

Having Page Setup Wizard

  • Then go to the Sheet tab and pick At end of sheet from Comments and notes and click on the Print button.

Giving Printing Command

  • Now, click on Ctrl + P and have all the consolidated comments on the last page of Print Preview.

Final Otput of Consolidating all Comments of a Worksheet while Printing


Things to Remember

  • What we called Comments in the previous Excel versions, is now being called Notes in Office365.
  • In the process of using the Page Setup option to consolidate comments, we will only be able to see the consolidated comments in Print Preview.
  • If you use those processes in Office365, it would work perfectly but will consolidate the notes.

Frequently Asked Questions

1. What is the major difference between Notes and Comments?

Notes are mainly used to provide additional information about a cell’s content. On the other hand, Comments are nothing but collaboration and communication within an Excel worksheet.

2. Can we hide all the comments in Excel?

Definitely, we can hide the comments in Excel. For this, we need to go to Notes from the Review tab and click on Show all notes. Click on the same option if we want to unhide them.

3. How to see all the comments in Office365?

Click on Show Comments from the Review tab and you will have all the comments of that worksheet at the right-side bar of that worksheet.


Conclusion

In this article, I have tried to explain 3 simple ways on how to consolidate all comments in Excel. I hope this article will be helpful for you. For any further questions, please comment below. You can also visit our site for more Excel-related articles.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo