With Excel comments, you can put a quick note in a cell, to explain a cell’s value. A new type of comment is coming to Excel soon, and here is a warning, before you make the switch.
Insert a Comment
Comments have been available in Excel for many years, and they’re handy for adding extra info to a cell.
For example, a region’s sales might have been low one month because of bad weather. Point to that cell, and the popup comment shows the reason.
For information on the basics of inserting, showing and printing comments, go to the Getting Started with Comments page on my Contextures site.
Comment Tricks
After you insert Excel comments, you can jazz them up a bit, with shapes, colours and pictures.
You wouldn’t want to fill a worksheet with these eye-catching comments, but use them occasionally, to add impact.
See how to change comment shapes, and add comment pictures on my Excel Comment Tips page.
Threaded Comments
Microsoft is introducing Threaded Comments in Office 365, so you might see those soon, if you don’t have them already. It should be available now, if you get the Insider builds.
These new threaded comments look more like blog comments, than the old-style Excel comments.
Your company might want to use threaded comments in some worksheets, to keep track of a discussion, and remember who said what.
Be careful though, if you’re using the extra comment formatting features.
WARNING – Before You Switch
Before you switch to Threaded Comments, instead of old-style (Legacy) comments, be sure to understand which features you will lose.
Legacy comment formatting features, such as changing shapes, inserting pictures or adding colors, will not be available in Threaded comments.
Affect New and Existing Comments
That formatting limitation doesn’t only affect new comments that you insert. Existing comments will be changed too.
Thanks to Bill Jelen (Mr. Excel) for this warning — “If you try to edit an old comment, a long message will tell you to Convert to Threaded Comments and all of your comment formatting will be lost.”
Insert Old-Style Comments
Excel’s old-style (Legacy) comments will still be available, but the icons to create and edit them will be hidden.
To continue working with Legacy comments, add the following icons to the Quick Access Toolbar, or to the Excel Ribbon:
- New/Edit Comment (Legacy)
- Previous Comment (Legacy)
- Next Comment (Legacy)
- Show/Hide Comments (Legacy)
- Show All Comments (Legacy)
Vote for Legacy Comments
Do you use Legacy Comments, and their special formatting features?
If you’d like to keep your Legacy comments safe, after Threaded Comments are introduced, go to UserVoice, and vote for Bill’s suggestion.
Bill suggests that the right-click menu does not offer a “Reply to Comment” command, for Legacy comments. Or, add an Edit Comments (Legacy) command on the right-click menu, to reduce the chance of destroying the formatting.
The more votes there are, the more likely it is that Microsoft will put the suggestion on their To-Do list.
Threaded Comments Video
To learn more about Excel Threaded Comments, and how to work with Legacy Comments, watch this video by Bill Jelen.
Your browser can’t show this frame. Here is a <a href=”https://youtu.be/3Pgf5FVNPWs” rel=”nofollow” target=”_blank”>link</a> to the page
___________________
Tuesday, September 18, 2018
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
As part of Microsoft’s new philosophy of releasing new products when they are ready, rather than waiting for the next big release every three or four years, we now have Threaded Comments in Excel. If you have Office 365, you may already have Threaded Comments, if you are on an accelerated “Insider” release schedule, or you may see it sometime soon. If you have the perpetual license version of Office 2016, you don’t get the same feature enhancements as Office 365 subscribers. And of course, you won’t see them in Excel 2013 and earlier.
What are these new Threaded Comments? Doesn’t Excel already have comments?
Threaded comments allow for discussions within and Excel workbook. They are a lot like comments on a blog or forum, or on a web site like Facebook. You can have multiple comments, and other people with access to the workbook can enter their own comments, or reply to yours. Here we see two comments in the new Comments task pane. The second comment, posted by one of my accounts (with the “JP” icon), shows a reply by another of my accounts (with my picture), and another reply by a third account (with the lone “J” icon).
In this post I will give a summary of Notes in Excel (as the old-style comments have been renamed), then I’ll show a little bit about the new Threaded Comments, and talk about compatibility between them.
Notes (“Legacy” Comments)
Comments were first introduced in Excel 5/95, when they were called Notes, and the interface to work with Notes was rather cumbersome. In Excel 97, Notes were renamed as Comments, with the interface we are now so familiar with. Comments have become indispensable for documenting worksheets and helping users navigate through models and dashboards.
When Threaded Comments first came out, Microsoft referred to the old-style comments as Legacy Comments. I don’t care for that “Legacy” adjective, because it makes me fear that the item will disappear. In later releases, the Legacy Comments are now referred to as Notes.
The best news about Threaded Comments is that they will not replace Notes; Threaded Comments and Notes can coexist side-by-side in a worksheet. Everything in this section that refers to Excel 2013 Comments is valid for Excel 2010 and earlier Comments and for Excel 2016 Notes.
In Excel 2013, you can access Comments through the Review tab of the ribbon (I will use Excel 2013 to show these old-school Comments, since my Excel 2016 has been updated through Office 365). In the Comments group there are buttons to insert a new Comment, navigate among Comments, and show or hide Comments.
You can also create a Comment using the context menu, when you right-click on a cell (below left). If the cell you right-click on already has a Comment, you have some Comment-related items on the context menu (below right).
When you hover over a cell with a Comment, the comment appears, with an arrow pointing back to its parent cell. This is the default size and appearance of a Comment.
You can resize Comments, and format their fonts, borders, and fills like any other shapes. And you can choose to show certain shapes even when the mouse isn’t hovering over their cells.
You can even format their fills to contain pictures. You could set up a catalog or invoice that shows an item’s picture when you mouse over its name or description.
And like other shapes in a worksheet, you can change their shapes, like the star in the screenshot below.
By default, Comments are not visible, but the top right corners of cells containing Comments are marked with small red triangular indicators. When you hover the mouse over one of the indicated cells, its Comment appears. In Excel options, you can change whether to hide the indicators, or whether to show the Comments and indicators all the time.
Because of the flexible formatting capabilities of Comments, they can be helpful in documenting worksheets and guiding users performing their tasks.
Threaded Comments in Excel 2016 (Office 365)
In some ways, the Threaded Comment story is like the familiar Comment/Note story. Here is the Review tab in the Excel 2016 ribbon. Remember that now Comment means Threaded Comment, and the Comments described in the previous section are now Notes.
The familiar Notes commands are available on a menu next to the Comments commands.
The right-click menu for an arbitrary cell offers Comments and Notes, though a given cell can have only one or the other (below left). The context menu of a cell with a Threaded Comment allows you to reply to the cell’s Comment or delete it (below center). The context menu of a cell with a Note lets you edit the cell’s Note, show or hide it, or delete it (below right). I’m sure the “Delete Comment” note will soon be corrected to “Delete Note”. This feature is very new, and in the weeks since it was first rolled out to bleeding edge subscribers, it has already evolved quite a bit.
Click Create Comment from the ribbon or context menu, and an indicator appears with a comment in edit mode. The indicator is a purple polygon, not a red triangle, and the comment shape looks like a discussion element from a web page: the author’s name has an avatar, either initials as shown below or a picture. Click the green button to post (send?) the comment.
This comment has been posted. The time and date are shown, along with its parent cell address, and a link to edit it.
When you click the three dot icon at the top right, a lone menu item give you the option to delete the Comment.
Threaded Comments can be used alongside Notes. This is very important, because their features mean are important for different uses: Notes for documentation, and Comments for discussion. In the first Excel build that included Threaded Comments, it seemed that Microsoft wanted to change all Notes to Threaded Comments, but now it is clear that the features are parallel but not interchangeable.
While Notes can be formatted with different colors, shapes, and even images, the appearance of Threaded Comments cannot be manipulated.
Mouse away from the cell and the Comment hides, leaving behind its strange purple indicator. Here we see another comment which I wrote from one account, and then replied to from two other accounts. Excel isn’t necessarily going to become the chat app of the future, but Threaded Comments are going to be helpful when people are collaborating on a worksheet model and discussing where data comes from and what approaches should be used.
Here we see another Comment being edited.
When we mouse away from the new Comment, which is still in edit mode, it hides, and leaves behind a larger indicator to indicate that it’s awaiting an action.
If you hover over another cell, its Comment reminds you that there is an unposted comment somewhere being edited. While one Comment is being edited, you can view but not edit other Comments.
If you click on the “already in progress” message, the active Comment appears, with a big reminder to post it (or you could click the big X in the top corner to delete it.
Unlike Notes, you can’t show multiple Comments in the worksheet at the same time. Clicking “Show Comments” in the ribbon opens a task pane that contains all of the Comments. Here are the first two Comments above. My mouse cursor is hovering over the first one, and we can see the time it was posted and the Edit link.
My mouse cursor has moved over the second Comment, so we see now when the Comment and its replies were posted as well as the Edit link for the Comment.
Moving the mouse over the first reply to the second Comment shows its Edit link and a Delete link. I showed before that the Delete command for the Comment was found by clicking the three dots; deleting the Comment deletes the entire thread.
When you are editing a Comment or reply, you see the same “already in progress” message as before.
Here is a view of our “in progress” Comment from before.
If I try editing one of the other Comments or replies, I am reminded to post this comment.
If I have an unposted Comment and I try to close its workbook, I get a warning. I can post the Comment, delete it, or cancel closing the workbook.
Viewing Threaded Comments in Excel 2013
Threaded Comments are new to recent builds of Excel 2016 through the Office 365 license. What happens when somebody opens your file on an older version of Excel? Microsoft says that they can be viewed but not edited.
I saved my file with Threaded Comments that I made in Excel 2016, then I opened it on another computer that has Excel 2013. In this section, “Excel 2013” is a stand-in for any “non-Threaded-Comment-enabled version of Excel”.
As you recall, there was one Threaded Comment with a single message and no replies, and another with a message and two replies.
The first Threaded Comment looks like a Note (i.e., a “legacy” Comment) in Excel 2013. It is the same size and formatted with the same colors as the default Notes in 2016 and Comments in 2013.
I can stretch the comment to read what it says. There is a header in square brackets that indicates that it is a Threaded Comment, which can be viewed but not edited in this version of Excel, and there is a link where Microsoft has posted more information. And below all the boilerplate, we see the message that was written in Excel 2016. We only see the text part of the message, not the sender or time it was posted.
Let’s look at the message with replies. Same notice at the top about Threaded Comments, and under that we can read the message and replies. Again, we only see the text, and not the sender or time sent.
For fun, I added a little bit of an edit to the message, highlighted below, to see what would happen.
The little edit I’ve made in the Threaded Comment remains in the old-style Comment, even after I save, close, and re-open the workbook, but only in Excel 2013. But as soon as I open the workbook on my Office 365 computer, the edit is gone, and the Comment has reverted to the Threaded Comment that I’d saved in Excel 2016.
-
#2
try
VBA Code:
Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
'look at each comment in sheet
For Each y In x.Comments
' comment text
b = y.Shape.DrawingObject.Caption
' split comment text by "
c = Split(b, Chr(34))
d = "[Threaded comment]"
nc = ""
For Each i In c
e = Right(Left(i, InStr(i, "]")), Len(d))
If e = d Then
Else
nc = nc + i
End If
Next
y.Shape.DrawingObject.Caption = nc
Next
Next
End Sub
-
#3
try
VBA Code:
Sub RemoveCommentText() Set a = ActiveWorkbook ' look at each sheet in workbook For Each x In a.Sheets 'look at each comment in sheet For Each y In x.Comments ' comment text b = y.Shape.DrawingObject.Caption ' split comment text by " c = Split(b, Chr(34)) d = "[Threaded comment]" nc = "" For Each i In c e = Right(Left(i, InStr(i, "]")), Len(d)) If e = d Then Else nc = nc + i End If Next y.Shape.DrawingObject.Caption = nc Next Next End Sub
I tried it, but all the contents within the comment section is deleted as shown below:
All i need is to get rid of all the text highlighted in yellow instead
-
#4
Ok hope the » where in the comment
Looks like highlighted text is at start of the comment each time
then following will remove nr= number of characters from start of comment string , adjust nr as need
VBA Code:
Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
'look at each comment in sheet
For Each y In x.Comments
' comment text
b = y.Shape.DrawingObject.Caption
' nr of characters from start of comment
' to be remove include for vbLf
nr = 34 ' revise as needed
nc = Right(b, Len(b) - nr)
y.Shape.DrawingObject.Caption = nc
Next
Next
End Sub
-
#5
Previous post #4 wrong use following
VBA Code:
Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
'look at each comment in sheet
For Each y In x.Comments
' read comment text frist
b = y.Shape.DrawingObject.Caption
If Left(b, 1) = "[" Then
'thread comment cell address
c = y.Parent.Cells.Address
' delete new styled thread comment 2018 -----!
' an create a new old styled comment
Range(c).ClearComments
Range(c).AddComment
' nr of characters from start of comment
' to be remove including for vbLf
nr = 237
Range(c).Comment.Text Text:=Right(b, Len(b) - nr)
Else
End If
Next
Next
End Sub
-
#6
Previous post #4 wrong use following
VBA Code:
Sub RemoveCommentText() Set a = ActiveWorkbook ' look at each sheet in workbook For Each x In a.Sheets 'look at each comment in sheet For Each y In x.Comments ' read comment text frist b = y.Shape.DrawingObject.Caption If Left(b, 1) = "[" Then 'thread comment cell address c = y.Parent.Cells.Address ' delete new styled thread comment 2018 -----! ' an create a new old styled comment Range(c).ClearComments Range(c).AddComment ' nr of characters from start of comment ' to be remove including for vbLf nr = 237 Range(c).Comment.Text Text:=Right(b, Len(b) - nr) Else End If Next Next End Sub
I tried this one but my comment turned from this
to this
and when i excuted the vba code,i got that error
-
#7
change
VBA Code:
b = y.Shape.DrawingObject.Captionwith
when you press Debug which line of the code is highlighted
-
#8
when you press Debug which line of the code is highlighted
Range(c).Comment.Text Text:=Right(b, Len(b) — nr)
-
#9
replace
VBA Code:
Range(c).AddComment
' nr of characters from start of comment
' to be remove including for vbLf
nr = 237
Range(c).Comment.Text Text:=Right(b, Len(b) - nr)with
VBA Code:
nr = 237
Range(c).AddComment Right(b, Len(b) - nr)
Asked by: Eldon Rempel
Score: 4.9/5
(69 votes)
To show all the threaded comments on the active sheet, follow these steps:
- On the Excel Ribbon, click the Review tab.
- In the Comments group, click Show Comments.
- A task pane opens, showing all the threaded comments.
Why can’t I see comments in Excel?
1. Click File tab, and click Options. 2. In the Excel Options, click Advanced in the left panel, and then go to the Display section, in the group For cells with comments, show, check the No comments or indicators.
How do you unthread a comment in Excel?
Open the worksheet you want to remove all comments and press Ctrl + A to select the whole sheet.
- Then click Review > Delete, then all the comments in the current worksheet are removed. …
- Click Kutools > More > Reset Comment Position.
How do you remove your version of Excel allows you to read this threaded comment?
As of now, you can’t remove this in previous version of Excel. The work around suggested on that page is to open the file in Excel online, remove all threaded comments and you can put all of them in a single comment box. You can also put a feature development request for this.
Does Excel 2016 support threaded comments?
Here is the Review tab in the Excel 2016 ribbon. … Remember that now Comment means Threaded Comment, and the Comments described in the previous section are now Notes. The familiar Notes commands are available on a menu next to the Comments commands.
27 related questions found
What is a threaded comment on Excel?
*Threaded comments are being made available to Office 365 users on a gradual basis over several weeks. Excel threaded comments allow you to string several comments together, including comments from other users, to form a conversation style thread.
How do you reply to threaded comments in Excel?
If you right-click a cell with a comment, the pop-up menu will include an option to Reply to Comment. If you right-click a cell with a note, you will be offered an Edit Note option. In the original release of threaded comments, editing a note would cause Excel to offer to convert all notes to the new threaded comments.
What are threaded comments?
Threaded comments allow readers to reply to other comments inline/nested which encourages better discussion and responses. This means you can reply to a comment directly regardless of when the original comment was posted, and your comment will appear just below the original comment.
How do I arrange comments in Excel?
Anchoring Comment Boxes in Desired Locations
- Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. …
- At the left side of the dialog box click Advanced.
- Scroll down until you see the Display options. …
- Select the Comments and Indicators radio button.
- Click on OK.
What would be a correct formula for sum in Excel?
The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: =SUM(A2:A10) Adds the values in cells A2:10.
How do you read threaded comments?
To show all comments at once, you click Show Comments on the Review tab. Then a Comments pane will appear on the right with all comments and their replies. To show all notes at once, you click Notes > Show All Notes on the Review tab.
How do I view comments in Excel 2020?
For those times, you need to click the Show All Comments command button on the Ribbon’s Review tab (or press Alt+RA). When you click Show All Comments on the Review tab, Excel displays all the comments in the workbook. Use the Show All Comments button on the Review tab to review the comments added to a worksheet.
How do I delete all comments?
Remove comments from a Word document
- To delete a single comment in the document, right-click the comment, and choose Delete Comment.
- To delete all the comments in the document, go to the Review tab, click the down-arrow on Delete, and choose Delete All Comments in Document.
How do I enable comments in Excel 2016?
Right-click the cell and then click Insert Comment (or press Shift+F2). If you’re using Excel for Office 365, right-click the cell and choose New Note. Type your annotation text. Click outside the cell.
How do I view comments in Excel 2016?
To display an individual comment, click the cell that contains the comment, and then on the Review tab, in the Comments group, click Show/Hide Comment. You can also right-click the cell and then click Show/Hide Comments on the shortcut menu.
Why do my Excel Comments move?
The problem with objects on an Excel spreadsheet (graphs, pictures, comments or anything that hovers over the cells) is that when you make changes to the size of rows or columns, these shapes tend to move and resize with them.
How do you format all comments in Excel at once?
In the Format Comments dialog box, click Format tab, and choose the scope that you want to change the comment format, you can choose Active sheet or All sheets. Then click Specify the comment cell button to select the cell which contains the specific comment format you want to apply.
How we can view a cell Comment *?
To view a cell comment
- A. Click the edit comment command on the insert menu.
- Click the display comment command on the window menu.
- Position the mouse pointer over the cell.
- Click the comment command on the view menu.
How do I put multiple comments in one cell?
Insert a comment to multiple cells with Paste Special feature
- Insert your comment in a cell.
- Select the comment cell and press the Ctrl + C keys to copy it.
- Then select and right click the range that you would like to batch insert comment, select Paste Special > Paste Special from the right-clicking menu.
What is a thread in a text message?
A running commentary of text messages pertaining to one topic or question. Message threads are used in all forms of user discussions on the Internet, including Usenet newsgroups, Web-based forums, blogs, chat rooms, groupware and email. The «thread» refers to the collection of messages organized by the software.
How do I view threads in Excel?
To enable the multi-threading feature, click the FILE tab and select Options to open the Excel Options dialog box, as mentioned earlier. Click Advanced in the menu on the left. Scroll down to the Formulas section and select the Enable multi—threaded calculation check box so there is a check mark in the box.
What are comments in Excel?
Summary. Excel comments are used to add a note or explain a formula in a cell. Excel provides users with the flexibility of editing, deleting, and showing or hiding comments on an Excel worksheet. Users can also resize and move the Excel comment box.