Comments are quite useful when you want to add some information about a cell/data point without changing the content of the cell.
I often use these when I am using formulas and I need to specify what a formula does and what a cell value means. If you’re collaborating with others, you can also add comments for other people’s reference.
In this tutorial, I will show you all you need to know about comments – including how to insert comments in Excel, how to edit/delete comments, how to delete comments from the entire sheet or workbook, etc.
But before I go ahead and show you how to insert comments in Excel (or delete/edit comments), there is something important you need to know.
In Office-365, Microsoft has renamed the Comment functionality as Notes. So the traditional comments that you know (one that shows a yellow box and small red triangle in the cell at top-right) are now called Notes. And comments now refer to a new feature that allows you to create threaded conversations. Here is an article about the difference between notes and comments
Bottom line – If you’re NOT using Office 365, you don’t need to worry about anything and a comment remains unchanged. If you’re using Office 365, comments are now referred to as Notes
This Tutorial Covers:
How to Insert Comments in Excel (Shortcut)
If you want to insert a comment in a cell in Excel, you can use the below keyboard shortcut:
SHIFT + F2 in Windows
Command + F2 in Mac
Here is how to use this shortcut to insert a comment:
- Select a cell where you want to insert a comment
- Hold the Shift Key (or Command key if using Mac)
- Press the F2 key
The above steps would instantly add a comment to the selected cell.
Now, you can enter any text that you want in the comment and then hit the Escape key or click anywhere in the worksheet to make that comment save in the cell.
In case you select a range of cells and use the above shortcut, it would insert a comment in the active cell only.
In case you don’t prefer the shortcut method, below is another method to add a comment:
- Right-click on the cell in which you want to add the comment
- Click on New Comment (or New Note in case you’re using Office 365)
Show All Comments/Notes
If you want all the comments to expand and show the text in it, you can do that easily with a single click:
- Go to the Review tab
- In the Comments group, click on Show All comments
This will instantly show all the comments on the worksheet.
This works like a toggle button. So if you click on it again, it will hide all the comments in the worksheet.
In case you’re using Office 365, you need to click on the Notes option and then click on Show All Notes
Navigate Through Comments (Previous/Next)
If you want to go through each comment one by one, you can do that as well. This can be useful when you have a lot of comments and you want to make sure you don’t miss any while going through these comments.
Below are the steps to go through comments one by one:
- Click the Review tab
- In the Comments group, click on the Next option to go to the Next comment (and Previous to go to the previous comment).
When you are navigating through these comments, only the comment that is active will be shown. The rest will be hidden. When you click on Next, it takes you to the next comment and hides all the other comments.
If you’re using Office 365 (where comments are now called Notes), you need to click on the Review tab, then click on the Notes option and then click on the Next/Previous Note options. This has been made a bit difficult if you have to use this option a lot (as it now takes more clicks). One way to deal this could be to add the Next/Previous notes option to the QAT (this option comes when you right-click on the Next/Previous Note options).
When you do a simple copy-paste, it will copy everything including the values, formulas, formatting and comments. So if you want to copy-paste everything, all you need is the regular Control-C and Control-V
But what if you only need to copy the comments (and nothing else).
Excel allows you to do this using the Paste Special options.
Below are the steps to copy and paste comments only in Excel:
- Select all the cells that have the comments that you want to copy
- Right-click on any cell in the selection and then click on Copy (or use Control + C keyboard shortcut)
- Go to the cell where you want to paste the comments.
- Right-click and select Paste Special.
- In the Paste Special dialog box, click on Comments (it will be ‘Comments and Notes’ if you’re using Office 365)
- Click OK
The above steps would only paste the comments and not the values/formatting/formulas.
This can be a useful thing when you have two versions of the same file with different comments. You can copy and paste all the comments in the latest version to make sure you’ve everything in one place.
There are different methods you can use to Delete comments in Excel. The method you choose will depend on what comments you want to delete.
For example, if you only want to delete comments in a selection or from the entire worksheet, you can use the easy way – shortcut or option in the ribbon.
But if you want to delete comments from all the worksheets at one go, you need to use VBA.
Let’s see both of these scenarios.
Delete Comments from a Selected Range or the Entire Worksheet
This one is simple.
To delete all comments in a selection, follow the below steps:
- Right-click anywhere in the selection
- Click on Delete Comment (or ‘Delete Note’ if you’re using Office 365)
In case you want to remove comments from the entire worksheet, follow the below steps:
- Click on the gray triangle at the top-left of the Excel application. This will select the entire worksheet (or you can use Control + A + A)
- Click the Home tab
- In the Editing group, click on Clear
- Click on Clear Comments
The above steps would remove all the comments in the worksheet.
Note that when you select the entire worksheet and then right-click on any cell, you won’t get the option to delete all the comments (or notes)
Delete Comments from All the Worksheets at One Go
Deleting comments from the entire workbook is a bit tricky. You can go to each sheet and delete all the comments in each sheet, but this can become tiresome if you have a lot of sheets in the workbook.
A simple VBA code, in this case, can do all the heavy lifting.
Below is the code that will go to each sheet in the workbook and delete comments from the entire sheet:
For each ws in Worksheets: ws.cells.ClearComments: Next ws
The above code uses the For Each Next loop to go through each worksheet in the workbook. And for each sheet, it uses the ClearComments method to remove all the comments at one go.
Since Microsoft has renamed comments to notes in Office 365, in future, the above code may delete comments (new feature in O365) and you may have to use the below code to delete notes (the comments in prior versions)
For each ws in Worksheets: ws.cells.ClearNotes: Next ws
As of now, both of these codes work and delete comments from the specified cells.
Now, let me show you how to use this code.
- Right-click on any sheet tab
- Click on View code. This will open the VB Editor (you can also open the keyboard shortcut ALT + F11)
- In the VB Editor, if you don’t see the immediate window, click on the View option in the menu and then click on Immediate window
- Copy and Paste the above VBA macro code into the immediate window.
- Place the cursor at the end of the line in the immediate window
- Hit the Enter key
The above steps will delete all the comments in the entire workbook.
Copy All Comments In a Separate Worksheet
It would be great to have a list of all the comments and all the text within each comment as a list in the worksheet. This will make it easy to go through the comments one by one (instead of navigating and jumping across cells in the sheet).
While this can not be done with an in-built feature, you can get this done using VBA.
Here is a detailed tutorial that shows how to get a list of all the comments in a separate worksheet.
Convert Notes to Comments (Office 365 Only)
This feature is only available to the users of Office 365.
Since there are two types of comments now – comments and notes, it allows you to change notes to comments (not vice-versa).
So if you have traditional comments (now called notes in O365), you can easily convert these to comments using the below steps:
- Click the Review tab
- Click on the Notes option
- Click on Convert to Comments
- In the dialog box that opens, click on ‘Convert All Notes’
The above steps would convert all the comments into Notes.
Note that if you have any formatting or images in the comments, it would be removed automatically.
Also, you can not choose to convert some of the notes to comments and leave others as is. The above steps would change all the notes to comments in the entire worksheet.
So this is all that there is to know about inserting and deleting comments in Excel.
I hope you found this tutorial useful!
You may also like the following Excel tutorials: