Curious to master the art of deleting comments in Excel? Dive into this comprehensive guide on managing Excel comments.
In the realm of Excel, comments serve as handy instructions or explanations for intricate formulas within your worksheets. Microsoft Excel provides seamless methods for handling comments, making deletion effortless and convenient.
Stick with me through this quick Excel tutorial to uncover some tried-and-true techniques for removing comments from your Excel worksheets.
What Is a Comment In Excel?
In Excel, a comment is a way for collaborators to have a threaded conversation within the workbook. If you see a dark magenta arrow in the top right corner of a cell, that indicates there is a comment.
This particular styling is specific to the latest version of Excel for Microsoft 365 desktop app. In older versions, comments are denoted by small red arrows in the top right corner. However, in the Excel 365 app, these red arrows signify notes.
Simply hover your cursor over the cell to view the threaded conversation. Typically, you will see your name displayed, which is synced from your Microsoft 365 subscriptions. You have the ability to reply to, resolve, and delete a comment. On the other hand, notes do not allow for replies; you can only edit, hide, or delete them.
Now that you have a grasp of the basics of Excel 365 comments and how they differ from notes, you can explore the various methods available to delete notes. These methods are applicable even if you are using an older version of Excel.
Using the Comments Button
If your Excel worksheet contains, you can get an itemized list by clicking on the Comments button in the top right corner of the Excel app. It’s near the Share button.
Click on that to open a right-side navigation panel listing all the comments in chronological order.
Now, you can click the three dots or More Actions icon to expand a context menu containing two comment management options.
Click on the Delete thread option to remove the comment.
You can now follow the same steps for the rest of the comments in the right-side panel to delete all comments.
Using the Right-Click Method
Suppose, there are three comments in column D. You want to delete the one in cell D6.
You can do that quickly by simply right-clicking on the cell and clicking on the Delete Comment option in the context menu.
This method is only useful if the worksheet you’re working on is quite small and you can see all the datasets, tables, shapes, etc., on one screen.
Using a Keyboard Shortcut
If you like to use keyboard shortcuts to pace up your productivity on Excel, I’ve also got a secret hotkey that you can use to delete a comment.
Select the cell that contains a comment.
Now, press Alt + R + D to remove the comment instantly.
Using the Editing Tool in the Home Tab
Navigate to the cell or cell ranges containing a comment.
Go to the Home tab and click on the Clear drop-down menu in the Editing commands block.
Choose the Clear Comments and Notes option from the context menu.
Using the Comment Box
You can also delete a comment directly from the comment box.
Go to the cell from which you’d like to delete a comment.
Hover the cursor over the cell or the comment icon to open the comment box.
Now, drag your cursor on the comment and click on the More Options or the three dots icon.
Select Delete thread from the context menu.
If a comment has been previously resolved by you or someone else, you won’t see the three-dot menu.
In that scenario, when you hover the mouse cursor over the comment, the comment box with a delete or trash bin icon pops up.
Click on the trash bin to remove the comment.
When you delete or edit a comment on Excel, you can revert to the previous state or bring back the comment by using the Excel Undo feature.
This method is suitable when you’re carefully auditing an Excel worksheet to remove comments.
Deleting Multiple Comments
You can use any of the methods explained already to remove multiple comments instantly. You just need to select all the cells containing comments.
For instance, you can press Ctrl + A to select the whole worksheet.
Now, press Alt + H + ZE + E + M to delete all the selected comments.
This hotkey simply uses the Clear Comments and Notes command to delete comments.
Deleting Comments From Data Validation
Sometimes, you might find that the Delete Comment or the Clear Comments and Notes tools aren’t working when you select a comment.
These comments show up on a yellow dialog box and don’t seem related to the latest threaded conversation style.
These are actually notifications from the Input Message feature of the Data Validation tool.
Therefore, none of the methods you’ve learned so far won’t work on such comments. These notifications also clutter your worksheet similar to redundant comments.
If you don’t require the Data Validation comments, highlight the cell or cell range where you get the Input Message box.
Click on the Data Validation drop-down arrow in the Data Tools commands block of the Data tab in the Excel ribbon menu.
You should again see the Data Validation option in the context menu. Click on that.
The Data Validation dialog box will pop up. Navigate to the Input Message tab.
Remove the contents of the Title and Input Message fields.
Click OK to save and apply the changes you’ve just made.
Delete Specific Comments Using Find & Replace
Sometimes, you might want to delete all comments in a worksheet containing a specific name, value, or keyword. You can utilize the Find and Replace tool to achieve this task.
Firstly, navigate to the worksheet from which you’d like to remove certain comments.
Click on any blank cell and press Ctrl + F to bring up the Find and Replace dialog box.
Type the target keyword, like Priority for the sample exercise in the Find what field.
Click on the Look in drop-down menu and choose Comments from the list.
Click the Find All button to list all the comments in the worksheet that contains the keyword Priority.
The list also shows you the worksheet name and the cell where the comment is located.
Click on an item in the Find and Replace search results list to select the cell in the worksheet.
Now, navigate to the selected cell, right-click, and choose Delete Comment from the context menu.
You can also use this method to delete comments in bulk that contain a specific value or word.
For that, select an item in the Find and Replace results list and press Ctrl + A to highlight all items.
You’ll see that Excel has highlighted those cells in the worksheet.
Right-click on any of the highlighted cells and click on the Delete Comment option.
Disable Comments From Excel Options
Do you want to temporarily remove all the comments from your worksheet for public presentation purposes? You can try this secret hack to hide all comments within a workbook. These comments will stay hidden until you turn them on again.
Open the workbook that contains comments distributed in several worksheets.
Press Alt + F + T to bring up the Excel Options dialog.
Click on the Advanced menu located on the left-side navigation panel.
Scroll down the options on the right side menu of the Excel Options to find the Display section.
Once you locate the Display section, click on the No comments… selection.
Click OK to save and apply the changes you’ve just made.
You’ll now find that there are no comments in the workbook. To get back the comments, repeat the above steps, and choose the Indicators only… selection.
Deleting Comments in Excel Using VBA
Do you know how to automate tasks on Excel using VBA? Are you willing to learn Excel VBA by using pre-written VBA scripts?
If yes, I’ve created this special segment just for you. Here, I’ve demonstrated how to delete a comment in Excel programmatically using VBA.
Before you take a look at the simple VBA scripts for different ways to delete comments, learn below the technique to set up a macro using a VBA script:
Now that you’ve learned the simple method to create a VBA macro, let’s look at the automatic methods and scripts below:
VBA Script To Delete All Comments in All Worksheets
Use this VBA script to create a VBA macro that’ll delete all the threaded conversations or comments in all worksheets of the active workbook.
Sub RemoveComments()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.ClearComments
Next ws
End Sub
You don’t need to customize any part of the above code.
Once you’ve created a VBA macro, press Alt + F8 to bring up the Macro dialog.
There, select the RemoveComments macro and hit the Run button.
Microsoft Excel will immediately remove all the comments.
⚠️ Warning: Any changes you make using a VBA macro are irreversible. You can undo the change automatically. So, create a backup copy of your workbook before trying the VBA-best methods.
VBA Macro To Delete All Comments in a Specific Worksheet
Let’s say there are more than 100 worksheets in your workbook. It’s an unproductive approach to manually navigate to Sheet73 and delete all of its comments manually.
Instead, create a VBA macro using the following script:
Sub DeleteThreadedComments()
Dim ws As Worksheet
Dim targetSheetName As String
Dim c As CommentThreaded
' Show an input box to enter the target worksheet name
targetSheetName = InputBox("Enter the name of the target worksheet:")
On Error Resume Next
Set ws = Worksheets(targetSheetName)
On Error GoTo 0
If Not ws Is Nothing Then
For Each c In ws.CommentsThreaded
c.Delete
Next c
MsgBox "Threaded comments deleted successfully.", vbInformation
Else
MsgBox "Worksheet '" & targetSheetName & "' not found.", vbExclamation
End If
End Sub
When you execute the above macro, you’ll see a dialog box like the one shown in the above screenshot. There, you can enter the name of the worksheet.
Once the process is complete, you’ll see a notification as displayed above.
For dated Excel desktop apps, use the following VBA code instead:
Sub DeleteCommentsInSpecificWorksheet()
Dim ws As Worksheet
Dim targetSheetName As String
Dim comment As comment
' Prompt user for the target worksheet name
targetSheetName = InputBox("Enter the name of the target worksheet:")
' Check if the worksheet exists
On Error Resume Next
Set ws = Worksheets(targetSheetName)
On Error GoTo 0
If Not ws Is Nothing Then
' Loop through all comments in the worksheet and delete them
For Each comment In ws.Comments
comment.Delete
Next comment
MsgBox "All comments in '" & targetSheetName & "' have been deleted.", vbInformation
Else
MsgBox "Worksheet '" & targetSheetName & "' not found.", vbExclamation
End If
End Sub
The above script has been tailored to remove old Excel comments, presently known as Notes in the Excel 265 app.
VBA Script To Delete Comments in a Specific Range
Do you know in which cell range comments are available? You can use the following VBA script to develop a macro that’ll remove comments from the defined cell range:
Sub DeleteThreadedCommentsInRange()
Dim ws As Worksheet
Dim rng As Range
Dim sheetName As String
Dim commentRange As String
' Prompt for sheet name
sheetName = InputBox("Enter the sheet name:")
' Prompt for cell range
commentRange = InputBox("Enter the cell range for comments (e.g., A1:B10):")
On Error Resume Next
Set ws = ThisWorkbook.Sheets(sheetName)
On Error GoTo 0
If Not ws Is Nothing Then
Set rng = ws.Range(commentRange)
If Not rng Is Nothing Then
rng.ClearComments
MsgBox "Threaded comments deleted successfully!", vbInformation
Else
MsgBox "Invalid cell range. Please check your input.", vbExclamation
End If
Else
MsgBox "Sheet not found. Please check the sheet name.", vbExclamation
End If
End SubUpon running the above script, you’ll first see the input box to choose a worksheet.
Then, Excel will ask you for the cell range to look for comments.
The Excel VBA will show a notification once the task is complete.
VBA Macro to Delete Comments Based on Conditions
The following VBA script allows you to delete comments based on conditions, like authors, comment dates, and keywords in comments.
Sub DeleteThreadedComments()
Dim ws As Worksheet
Dim conditionType As String
Dim conditionValue As String
Dim comment As CommentThreaded
' Prompt for sheet name
On Error Resume Next
Set ws = Worksheets(InputBox("Enter the sheet name:"))
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Sheet not found. Please enter a valid sheet name.", vbExclamation
Exit Sub
End If
' Prompt for condition type
conditionType = UCase(InputBox("Enter the condition type (Author/Date/Keyword):"))
Select Case conditionType
Case "AUTHOR"
' Prompt for author name
conditionValue = InputBox("Enter the author name:")
Case "DATE"
' Prompt for date
conditionValue = InputBox("Enter the date (format: MM/DD/YYYY):")
Case "KEYWORD"
' Prompt for keyword
conditionValue = InputBox("Enter the keyword:")
Case Else
MsgBox "Invalid condition type. Please choose Author, Date, or Keyword.", vbExclamation
Exit Sub
End Select
' Delete matching comments
For Each comment In ws.CommentsThreaded
Select Case conditionType
Case "AUTHOR"
If comment.Author.Name = conditionValue Then
comment.Delete
End If
Case "DATE"
If comment.Date = CDate(conditionValue) Then
comment.Delete
End If
Case "KEYWORD"
If InStr(1, comment.Text, conditionValue, vbTextCompare) > 0 Then
comment.Delete
End If
End Select
Next comment
MsgBox "Threaded comments deleted based on the specified condition.", vbInformation
End Sub
Don’t make any changes to the script. You should use it as is.
It’ll show the following input boxes to guide you through the process:
- Enter sheet name
- Enter the condition type from Author, Date, and Keyword
- Enter the data for the chosen condition
- Work completed notification
Troubleshoot Comment Deletion in Excel
Often you might find blank comments. You won’t be able to delete those using the Delete Comment command from the right-click context menu.
In that case, hover over the cell containing the blank comment.
On the comment box, click on the Cancel or cross button.
Conclusions
To improve the readability and confidentiality of your worksheet, consider removing comments that may contain sensitive information.
If your workbook is large and you are trying to reduce the file size, deleting comments can be a helpful step.
Feel free to try out the suggestions provided in this article that best suit your situation. If you found this information useful or have a different approach to removing comments in Excel, please share it below.