Threaded comments are like conversations within the Excel worksheet. They allow team members to have a discussion about the data they are working on.
Below is an example of comment in Excel.
At times you may want to print the threaded comments to have a permanent record. In this tutorial, I will show you some easy ways to print comments in Excel.
Note: There is a difference between Comments and Notes in Excel. Comments in previous versions of Excel are now called Notes in newer versions of Excel.
Method #1: Use Excel’s Built-in Way of Printing Comments
Suppose we have the following worksheet with comments in cell C4.
We want to print the comments (where the comment is something as shown in the screenshot above).
Note that Excel does not have a built-in way of printing the comments as they are displayed in the worksheet. Only Notes can be printed as displayed on the worksheet.
However, Excel allows printing comments at the end of the worksheet.
Below are the steps to print comments at the end of the worksheet:
- Select the Page Layout tab, and click the Page Setup dialog box launcher button in the bottom right corner of the Page Setup group.
- In the Page Setup dialog box that appears, select the Sheet tab and select At end of sheet option in the Comments and notes drop-down.
- Click the Print Preview button in the Page Setup dialog box to see a preview of how the comments will appear on the printout.
- In the Print window, use the spin box at the bottom of the window to open the page where the comments appear.
In our example, the preview of the comments is displayed on the second page.
- Click the Print button in the Page Setup dialog box to open the Print window. In the Settings area, enter the pages on which the comments appear in the Pages spin boxes.
- Click the Print button to send the work to the printer.
When you have multiple comments in different cells, Excel would first show you the cell reference and then the entire comment in that cell.
Excel has the limitation of only printing comments at the end of the worksheet.
We can use the following methods to work around this limitation and print at least some comments as displayed in the worksheet.
Also read: Print Preview in Excel [Keyboard Shortcut]
Method #2: Print a Screenshot of the Comments
This method is simple but not the most efficient. However, if you need to print the comments as they are seen in the worksheet, this method can be used.
In this method, we take a screenshot of the comments. We then incorporate the screenshot in other apps and print it.
Suppose we have the following data set with comments in cell C4. Notice that cell C4 has a purple comments indicator in the top right corner.
Below are the steps to print the comments as they are seen in the worksheet:
- Select cell C4 to display the comments.
Note: If you have comments in many cells, you can have them all displayed in the Comments pane.
Select the Review tab, and choose Show Comments in the Comments group.
The comments are displayed in the Comments pane on the right of the Excel window.
- Use an app such as the Windows Snipping Tool to take a screenshot of the comments. The screenshot is sent to the Windows clipboard.
- Open an app such as Word. Press Ctrl + V to paste the screenshot into the app.
- Print the document.
The biggest drawback of this method is that it is manual and very hard to automate. However, if you only have a few comments that you want to print (as they’re seen in the worksheet0, this method gets it done.
Also read: How to Filter Cells with Notes in Excel?
Method #3: Enter the comments manually in a column and print them
This method works best if you have a few comments.
We want to print the comments in cell C4 of the following dataset of a regional sales report.
Below are the steps to add the comment manually to a column and then print it:
- Select cell D3 and type in the word Comments.
- Select the Review tab, and choose Show Comments in the Comments group.
The Comments pane is displayed on the right of the Excel window. It shows the comments in the worksheet alongside the reference to cell C4 which stores the comments.
- We type the comments in cell D4 as we refer to the Comments pane. We can also copy and paste the comments.
Print the worksheet using the following steps:
- Select File on the Ribbon:
- Select Print in the left sidebar.
- In the Settings area of the Print window, select Landscape Orientation. This page orientation allows for the accommodation of more data in a page.
- On the right of the Print window, you can see a preview of how the printout will look like.
- Click the Print button to print the list of comments.
This is again a manual method where you need to copy the content of the comment and put it in a column that would show the comment when printed. This is best suited in situations where you only have a few comments
Also read: How To Make Excel Spreadsheet Bigger When Printing
Method #4: Use Excel VBA to Generate a List of Comments then Print the List
We use the following dataset in our illustration. The dataset has comments in cell C4.
We create a macro that generates a list of the comments in a new worksheet. We then print the list.
Below are the steps to print the comments in Excel by first extracting the comments using VBA macro code:
- Open the Visual Basic Editor by pressing Alt + F11.
- Choose the Module menu item on the Insert menu.
- Copy and paste the following subroutine procedure into the module:
'Code by Steve Scott from https://spreadsheetplanet.com
Sub Print_Comments()
Application.ScreenUpdating = False
Dim nComment As CommentThreaded
Dim activeWs As Worksheet
Dim newWs As Worksheet
Dim i As Long
Dim cmtCount As Long
Set activeWs = ActiveSheet
cmtCount = activeWs.CommentsThreaded.Count
If cmtCount = 0 Then
MsgBox "No Comments."
Exit Sub
End If
Set newWs = Worksheets.Add
newWs.Range("A1:F1").Value = _
Array("Number", "Cell Reference", "Author", _
"Date", "Replies", "Comment")
i = 1
For Each nComment In activeWs.CommentsThreaded
With newWs
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = nComment.Parent.Address
.Cells(i, 3).Value = nComment.Author.Name
.Cells(i, 4).Value = nComment.Date
.Cells(i, 5).Value = nComment.Replies.Count
.Cells(i, 6).Value = nComment.Text
End With
Next nComment
With newWs
.Columns(6).ColumnWidth = 45
.Columns.AutoFit
With .Cells
.EntireRow.AutoFit
.VerticalAlignment = xlTop
.WrapText = True
End With
End With
Application.ScreenUpdating = True
End Sub
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Place the cursor anywhere in the procedure and press F5 to run the code.
- Press Alt + F11 to switch to the active worksheet.
The macro has generated a list of the comments.
- Print the list of comments as explained previously in this tutorial.
The VBA code used above would first add a new worksheet, and then extract all the comments, and put it in separate rows that can easily be printed. in case there are no comments in the active sheet, it would show the message “No Comments”.
Also, know that this VBA code is meant to work only with Comments and not Notes (where notes used to be called comments in the earlier versions of Excel)
In this tutorial, I have covered four methods of printing comments in Excel.
The easiest way is to use the excels inbuilt functionality to print comments at the end of the worksheet. While this is easy, the drawback here is that you do not see the comments as they are seen in the worksheet itself (but rather as a separate list in the end).
I’ve also covered two manual methods where you can either take screenshots of the comment so that you can print exactly what you see in the worksheet, or you can extract the comments and put them in an adjacent column and print it (so that you get the comments in the same row where these have been added).
And finally, I’ve covered a VBA macro code method that would add a new worksheet and extract all the comments in that new worksheet that can easily be printed.
To be honest, I wish there was a better way to print the comments in Excel, but till the time it is improved upon, these methods should serve you well.
Other Excel articles you may also like:
