How to delete all comments in a worksheet/workbook?
In day-to-day work within Excel, using comments to annotate cells is a common way to communicate feedback or additional information. While removing a single comment is a straightforward process familiar to most users, managing spreadsheets where numerous comments have accumulated—especially across entire worksheets or workbooks—can become inefficient if tackled one-by-one. To streamline this task, there are several practical methods for batch-deleting all comments, each suited to different scenarios and user preferences. This guide introduces a range of quick techniques to efficiently clear all comments from a worksheet or an entire workbook, helping maintain clarity and organization in your Excel files.
Delete all comments in a worksheet with Delete in Excel
Delete all comments in a worksheet with the Go To function in Excel
Delete all comments in a worksheet or workbook with VBA
Reset all comments’ locations with Kutools for Excel
Delete all comments in a worksheet with Delete in Excel
When working with a worksheet filled with comments, you may find it necessary to remove all at once—perhaps for preparing a final report or sharing with colleagues. Excel offers a built-in way to do this efficiently by using the Delete option from the Review tab.
1. Open the worksheet where you want to remove all comments. Press Ctrl + A to select the entire sheet. Make sure you have selected the correct worksheet, and double-check if you need to save any comments before proceeding, as this action cannot be undone.
2. Go to the Review tab, then click Delete in the Comments group. All comments in the current worksheet will be deleted instantly. This method is effective when you want to clear every comment in your sheet without manually locating each one.
If you accidentally remove comments you intended to keep, use Ctrl+Z to undo immediately, or make sure to save a backup before performing this operation for added safety.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Delete all comments in a worksheet with the Go To function in Excel
For situations where you prefer more control over selecting and managing comments, Excel's Go To Special feature offers a flexible way to select all commented cells at once for deletion. This approach is useful if your worksheet has comments scattered throughout and you wish to review or delete them collectively.
1. Press F5 to open the Go To dialog box. Click Special… to access additional selection options. In the Go To Special dialog, select the Comments option and click OK. All cells containing comments will be highlighted at once. This is particularly handy for large sheets or when comments are not easily visible.
2. Right-click one of the selected cells to open the context menu, then choose Delete Comment to remove all comments from each highlighted cell. This removes comments but keeps the cell contents intact, making it ideal for sheets where data integrity must be preserved.
To ensure all comments are deleted, confirm that each cell with a comment was highlighted before using Delete Comment. Accidentally missed comments can remain if not all cells are properly selected.
Delete all comments in a worksheet or workbook with VBA
When you need to remove comments from multiple worksheets or the entire workbook, manual methods can become impractical. VBA (Visual Basic for Applications) provides a robust way to automate this process and handle batch deletions at scale. This technique is best suited for users who are comfortable working with macros and require advanced capabilities not available via standard Excel commands.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Within the VBA interface, click Insert > Module to open a new module window. Paste the appropriate VBA code into the module window, depending on whether you wish to delete comments in just the active worksheet or across the entire workbook. Remember to always save your workbook before applying VBA, as actions performed by macros cannot be undone.
VBA: Delete comments from a worksheet
Sub Remove_All_Comments_From_Worksheet()
Cells.ClearComments
End Sub
VBA: Delete all comments from the whole workbook
Sub DeleteAllComments()
'Updateby20140613
For Each xWs In Application.ActiveWorkbook.Sheets
For Each xComment In xWs.Comments
xComment.Delete
Next
Next
End Sub
3. Once the code is entered, click the Run button (or press F5) to execute. All comments will be deleted from the specified worksheet or the entire workbook as per the macro. If the VBA does not run as expected, check your macro security settings under File > Options > Trust Center > Trust Center Settings > Macro Settings. For large workbooks, consider closing other applications to reduce memory usage and avoid performance issues.
Remember, to avoid unintentional data loss, verify the macro scope—whether operating on a single sheet or all sheets—before execution. It's recommended to back up your file before running VBA operations.
Reset all comments’ locations with Kutools for Excel
As comment boxes can be manually moved around for better visibility, their positions may become scattered, leading to a cluttered appearance or making it difficult to locate them. Kutools for Excel provides a dedicated feature to quickly reset all comment locations, streamlining worksheet presentation and ensuring comments return to their default positions.
After freely installing Kutools for Excel, follow these steps:
Click Kutools > More > Reset Comment Position from the Excel ribbon.
Next, choose Active sheet to reset comment positions in the current sheet, or All sheets to adjust all comments in the workbook.
This tool saves significant time and removes the hassle of manually repositioning each comment box, especially in complex worksheets with many annotations. It is recommended to use this feature after editing or moving comments to maintain a standardized worksheet layout.
To further streamline comment management, Kutools also offers other advanced worksheet tools. Always ensure you’re working on the correct worksheet or workbook before applying mass position resets, and consider reviewing your comment layout after the operation for ideal visual clarity.
Relative Articles:
If you encounter problems such as some comments remaining or error messages when performing batch deletions, try saving your workbook, restarting Excel, and reattempting the operation. When using VBA, double-check for syntax errors or ensure that macros are enabled. For very large files or files with complex formatting, perform these operations with a backup copy to prevent any accidental data loss.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in