0

How To Delete All Comments in Excel

Posted by Arch on November 27, 2018

Whether someone is editing your work or you’re leaving yourself important markers, there’s very little chance that you won’t have to get accustomed to comments in Microsoft Excel.

These dialog boxes come in handy during the drafting phase of any project, but you do 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, you can delete all of them at the same time, or you can be selective in your approach.

But not only can you do all that, you can also remove them from three different places in Excel. You can use the Go To function, you can use the Review tab, and you can run a VBA macro.

Knowing all these methods will be helpful if you happen to run across different versions of Excel.

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 wide range selections and rearrange data on various parameters such as formulas, blank cells, objects, column and row differences, constants, and of course comments.

If you want to delete everything that’s no longer necessary, proceed with the following steps.

  1. Select the worksheet where you want to delete comments
  2. Press F5
  3. Click Special
  4. Check Comments
  5. Press OK
  6. Open a context menu with right click
  7. Click Delete Comment

This will remove all comments from your current worksheet regardless of how many comments and how many different authors there are. The method will also work in any version of Excel.

Using the Review Tab

If you already know how to write comments, you should probably also know how to delete them. From the same Review tab from which you can write comments on your worksheets, you can also delete them.

  1. Select desired worksheet
  2. Select Review
  3. Click Delete

Note that you can’t use this to delete all comments from all worksheets. You will have to do this individually for each worksheet.

Using a VBA Macro

The trusty Microsoft Visual Basic for Applications window can help you accomplish a wide variety of tasks in Excel, if you know the right lines of code.

  1. Press Alt and F11 to display the window
  2. Select Insert from the toolbar
  3. Click Module
  4. Copy the following lines of code

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

  1. Press run

This will delete all the comments in all worksheets within your current workbook. But, if you want to delete just the comments in a specific worksheet, there’s a VBA alternative for that too.

Open up a module following the previous steps and copy the following code.

Sub Remove_All_Comments_From_Worksheet()
Cells.ClearComments
End Sub

Make sure you have the desired worksheet selected before you open the VBA interface.

Ultimate Tip on Making Data Range Selections

Unless you’re using a VBA macro to sort through the data in your spreadsheet, you will have to assign a specific data range before taking any action. That’s because Excel needs to know where to look for the parameters you give it.

If you want to modify an entire worksheet, all you have to do is press the triangle in the corner between column A and row 1. If for some reason your keyboard is not working properly, use that.

It has the same effect as using Ctrl + A as it will select every cell branching out from that position.

After that you can proceed to rearrange columns, delete comments, remove empty cells, remove rows, remove columns, or even sort them based on the parameters found in the Go To function window.

A Final Thought

It doesn’t matter if you’ve added some comments yourself to highlight important pieces of information or if you’ve received suggestions from coworkers via comments. When you’re done working in the draft version of your worksheet, the comments need to go. Note that instructions on how to use the spreadsheet should be written on the worksheets rather than in comments.

With Excel, you can dispose of comments easily so that you don’t have to remove them one by one. However, keep in mind that you can’t 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.

Leave a Reply

Your email address will not be published. Required fields are marked *