How To Delete All Comments in Excel
Whether someone is editing your work or you are leaving yourself important markers, there is a big chance that you’ll need to get accustomed to comments in Microsoft Excel.
These dialog boxes come in handy during the drafting phase of any project, but you want to remove them before using the worksheets during a presentation.
There are three ways to delete comments from your worksheet. You can do it one by one, delete all of them simultaneously, or proceed with the selective approach.
Not only can you do all the above things, but you can also remove them from three different places within Excel. You can use the “Go To” function, “Review” tab, or run a VBA macro.
Knowing all these methods will be helpful if you happen to run across different versions of Excel.
Option 1: Use the “Go To” Function
The “Go To” function is one of the quickest ways to rearrange or edit your Excel worksheets. From there, you can make range selections and rearrange data on various parameters such as formulas, blank cells, objects, columns, row differences, constants, and comments.
If you want to delete everything that is no longer necessary, proceed with the following steps.
- Select the worksheet where you want to delete comments, then press F5.
- Click Special, select Comments, then click OK.
- Open a context menu with right-click, then click Delete Comment.
Using the “Go To” option removes all comments from your current worksheet regardless of how many authors created them. This method will also work for any version of Excel.
Option 2: Use the “Review” Tab
If you already know how to write comments, you should probably also learn how to delete them. From the same Review tab from which you can write comments on your worksheets, you can also delete them.
- Select the desired worksheet, then select “Review.”
- Select a comment, then click “Delete.”
Note: You cannot use the above process to delete all comments from all worksheets. You will have to do this individually for each worksheet.
Option 3: Use a VBA Macro
The trusty “Microsoft Visual Basic” window can help you accomplish a wide variety of Excel tasks if you know the right lines of code.
- Press “Alt + F11” to display the window.
- Select “Insert” from the toolbar, then click “Module.”
- Copy the following lines of code and paste them into the module.
Sub DeleteAllComments() 'Updateby – insert date using year/month/day format For Each xWs In Application.ActiveWorkbook.Sheets For Each xComment In xWs.Comments xComment.Delete Next Next End Sub
- Press “Run.”
Using a VBA Macro deletes all the comments in all worksheets within your current workbook. If you want to delete the comments in a specific worksheet, there is a VBA alternative.
Open up a module following the previous steps and copy the following code.
Sub Remove_All_Comments_From_Worksheet() Cells.ClearCommentsEnd Sub
Make sure you have the desired worksheet selected before you open the VBA interface.
A Final Thought
It does not matter if you have added some comments yourself to highlight essential pieces of information or if you have received suggestions from coworkers via comments. When you finish working on the draft version of your worksheet, the comments need to go. Note that instructions on using the spreadsheet should be written on the worksheets rather than in the comments.
With Excel, you can dispose of comments easily so that you do not have to remove them one by one. However, keep in mind that you can not create exceptions.
As complex as Excel is as a program, it only lets you delete comments one by one or all of them simultaneously. The only exception is to target specific worksheets instead of the entire workbook.