Note: The other languages of the website are Google-translated. Back to English
English English

Excel comments: Add, Show/hide, modify, delete, and more advanced operations

For many Excel users, comments are often used in their daily work. This tutorial gathers comprehensive knowledge of Excel comments. With the help of this tutorial, creating, modifying, and deleting comments in different circumstances will no longer be a problem for you. In addition, it provides some advanced operations to help you solve complex problems when using Excel comments. Let’s go ahead to read this tutorial to learn more details about Excel comments.

Table of Contents: [ Hide ]

(Click on any heading in the table of contents below or on the right to navigate to the corresponding chapter.)

1. What is the comment in Excel?

Excel comment can be used to add notes about a specific part of the document. For example, you can use a comment to explain a formula in a cell, use a comment to remind yourself or other users about something important, or use the comment for other reasons as you need.

An Excel comment has four components: the indicator, the comment box, the user name, and the remark content.

As shown in the screenshot above:

Comment indicator: The little red triangle on the upper-right corner of the cell to indicate a comment in a cell.
Comment box: The rectangle with an arrow pointing to the indicator.
User name: The bold text in the box is the Microsoft Office user name who created this comment.
Comment content: The texts below the user name.

2. Add a comment to cell(s)

Before using Excel comments, you must know how to add a comment to a cell firstly. This section includes two parts that help you add a comment not only to a cell but also to multiple cells at the same time.

2.1 Add a comment to a cell

Excel provides a built-in function "Insert Comment” to help you easily add a comment to a cell in Excel.

1. Select a cell you want to add a comment to.

2. Click Review > New Comment.

Tips:

1) You can right-click a cell and then click Insert Comment from the context menu to create a comment.

2) Or you can create a comment by a keyboard shortcut: select a cell and then press Shift + F2 keys.

3. Then a comment is added to the selected cell, you just need to type the remarks into the box, and then click on any other cell to complete the comment.

Note: By default, an Excel comment is labelled with the Microsoft Office user name. You can change the user name as you need.

2.2 Add a comment to multiple cells

With the Excel build-in Insert Comment feature, you can only add a comment to a cell per time. This section provides two methods for you to add the same comment to multiple cells at the same time.

2.2.1 Add a comment to multiple cells with the Paste Special feature

You can do as follows to apply the Excel Paste Special feature to add the same comment to multiple cells at the same time.

1. Firstly, you need to add a comment to a cell, click to know how.

2. Select the cell with comment and press the Ctrl + C keys to copy it.

3. Go to select the range of cells that you want to have the same comment.

Tips: Hold down the Ctrl key, you can select multiple noncontiguous cells one by one.

4. Click Home > Paste > Paste Special.

Alternatively, you can right-click on any selected cells, and then select Paste Special > Paste Special from the context menu.

5. In the opening Paste Special dialog box, click the Comments radio button, and then click OK.

Then you can see the same comment has been added to selected cells at once as shown below.

2.2.2 Add a comment to multiple cells with VBA

The below VBA code can also help to easily add the same comment to multiple cells in bulk in Excel.

1. Open the worksheet that you want to add a comment to cells.

2. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

3. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA into the Code window.

VBA code: Add a comment to multiple cells in bulk

Sub InsertCommentsSelection()
'Updated by Extendoffice 20211018
    Dim xRg As Range
    Dim xRgEach As Range
    Dim xAddress As String
    Dim xText As String
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range:", "Kutools For Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Count > 1 Then
        Set xRg = xRg.SpecialCells(xlCellTypeVisible)
    End If
    xRg.Select
    xText = InputBox("Enter Comment to Add" & vbCrLf & "Comment will be added to all cells in Selection: ", "Kutools For Excel")
    If xText = "" Then
        MsgBox "No comment added", vbInformation, "Kutools For Excel"
        Exit Sub
    End If
    For Each xRgEach In xRg
        With xRgEach
        .ClearComments
        .AddComment
        .Comment.Text Text:=xText
        End With
    Next xRgEach
End Sub

4. Press the F5 key to run the code.

5. In the popping up Kutools For Excel dialog box, select the cells that you want to have the same comment and then click OK.

Tips: Hold down the Ctrl key, you can select multiple noncontiguous cells one by one.

6. In the second opening Kutools For Excel dialog box, type in the content of the comment and click OK.

Then the same comment is added to selected cells at the same time. See screenshot:

Note: The hidden cells (auto filter or manually hide) in selections will be ignored while adding comments with this VBA code.


3. Show or hide comments

By default, after commenting on a cell, only the comment indicator displays in the cell. Normally, you need to hover the cursor over the cell to display the comment box. This section is divided into four parts to demonstrate how to show or hide comments in Excel.

3.1 Show or hide a comment in a selected cell only

This part is going to show you how to show or hide a comment in a selected cell.

1. Select a cell you want to show its comment.

2. Click Review > Show/Hide Comment.

Then the comment will be always displayed on this cell.

For hiding a comment, select this cell and click Review > Show/Hide Comment.

3.2 Show or hide all comments in bulk in the current workbook

If you want to show all comments in the current workbook, the build-in Show All Comments feature can do you a favor.

1. Click Review > Show All Comments. See screenshot:

Then all comments in the workbook are displayed at once.

You can click Review > Show All Comments again to hide all comments.

3.3 Hide both the comment and its indicator at the same time

By default, after a comment is added to a cell, the indicator will remain displayed until the comment is deleted. This section is going to show you two methods to hide both the comment and its indicator at the same time in Excel.

3.3.1 Hide both the comment and its indicator with Excel build-in option

Excel provides a built-in option to help you hide both comments and indicators in the whole workbook.

1. In an opened Excel workbook, click File > Options.

2. In the Excel Options window, click Advanced in the left pane, click the No comments or indicators radio button in the Display section, and then click OK to save the changes.

Then you can see all comments and the corresponding indicators are hidden in all sheets of the current workbook.

Note: For displaying the comment indicators, you need to click File > Options to get into the Excel Options window again, and then click the Indicators only, and comment on hover or the Comments and indicators radio button as you need.

3.3.2 Easily hide both the comment and its indicator with an amazing tool

With the above method, you need to get back and forth into the Excel Options window to show or hide the comment indicators. Here recommend the View Options feature of Kutools for Excel to help you easily show or hide both comments and indicators in the Excel workbook.

1. After installing Kutools for Excel, click Kutools > Show & Hide > View Options. See screenshot:

2. In the View Options dialog box, click the None radio button in the Comments section, and then click OK.

Then all comments and their indicators are hidden in all sheets of the current workbook.

Note: For displaying the comment indicator again, you just need to click Kutools > Re-run last utility to open the View Options dialog box, and then choose Comment indicator only or Comment & indicator as you need.

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

3.4 Always show a comment when the cell is selected

By default, you need to hover the cursor over a cell to display its comment. When the cursor moves away from the cell, the comment will disappear. This section is going to show you how to always show a comment when a cell is selected with the Always Show Comment utility of Kutools for Excel.

1. Click Kutools > More > Always Show Comments.

From now on, when selecting a cell, its comment will be displayed all the time until activating another cell.


4. Modify comments in Excel

After adding comments to cells, you may need to change the content for timely updating, change the comments’ appearance to make them look good, or modify the comments for other purposes. In this section, you will find what you need in modifying comments.

4.1 Change the text in the comments

You can change the content text in a comment or multiple comments with the below methods.

4.1.1 Change the text in a comment with the Edit Comment feature

It is very easy to change the text in a comment with the build-in feature “Edit Comment” in Excel

1. Select a cell you want to change its comment text, and then click Review > Edit Comment.

In addition, you can right-click on a commented cell, and then click Edit Comment in the context menu.

2. Then the comment box of the selected cell pops up, please change the texts as you need.

After changing the texts, click on any other cell to complete the comment.

4.1.2 Batch Finding and replacing texts in comments

With the Edit Comment feature, you can only edit one comment per time. If there are a large number of comments that need to be modified, using this feature will waste a lot of time.

In this section, we provide two methods for you to find and replace texts in multiple comments in bulk in Excel.

4.1.2.1 Batch finding and replacing texts in comments in all sheets with VBA

The below VBA code can help to find and replace comment texts in all worksheets of a workbook. You can do as follows to get it done.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Code window.

VBA code: Find and replace comment texts in all sheets

Sub ReplaceComments()
Dim cmt As Comment
Dim wks As Worksheet
Dim sFind As String
Dim sReplace As String
Dim sCmt As String
sFind = "remark"
sReplace = "replace with new"
For Each wks In ActiveWorkbook.Worksheets
For Each cmt In wks.Comments
sCmt = cmt.Text
If InStr(sCmt, sFind) <> 0 Then
sCmt = Application.WorksheetFunction. _
Substitute(sCmt, sFind, sReplace)
cmt.Text Text:=sCmt
End If
Next
Next
Set wks = Nothing
Set cmt = Nothing
End Sub

Notes: Here in this case,

1) The “remark” in the seventh line sFind = "remark" is the original comment text you want to change.
2) The “replace with new” in the eighth line sReplace = "replace with new" are the new texts you want to replace with.

You need to change them based on your needs.

3. Press the F5 key to run the code.

Then the specified comment texts in all worksheets of the current workbook are replaced with new texts in bulk.

4.1.2.2 A handy tool to easily find and replace texts in comments in an active or all sheets

With the Find/Replace Comment Text feature of Kutools for Excel, you can easily find and replace comment texts with specified ones not only in an active sheet but also in all sheets of a workbook.

1. Click Kutools > More > Find/Replace Comment Text.

2. In the Find/Replace Comment Text dialog box, you need to configure it as follows.

2.1) In the Scope drop-down list, select Active sheet or All sheets based on your needs;
2.2) In the Find text in the comments box, enter the original texts you want to change in the comments;
2.3) In the Replace with box, enter the new texts you want to replace with;
2.4) Click the Replace button. See screenshot:

3. Then a Find/Replace Comment Text dialog box pops up to tell you how many comments have been changed, click OK.

4. And then close the Find/Replace Comment Text dialog box.

4.2 Change the comment font

4.2.1 Change the font in a comment with the Format Comment feature

You can easily change the formatting of an individual comment, such as font, font size, font color, font style by following the below steps.

1. Select a commented cell, and then click Review > Edit Comment.

2. Now the comment box is editable. You need to select the remark texts (including the user name as you need), right-click and select Format Comment from the context menu.

3. In the Format Comment dialog box, specify the font style, size, color, or other formats as you need, and then click OK to save the changes.

Then you can see the font in the selected comment is changed as the below screenshot shown.

4.2.2 Change the font in all comments with an amazing tool

After changing the font in a comment, this section is going to show you how to apply this new font format to all other comments in an active sheet or all sheets of a workbook with the Format Comment feature of Kutools for Excel.

1. Click Kutools > More > Format Comment. See screenshot:

2. In the Format Comment dialog box, you need to:

2.1) Choose Active sheet or All sheets as you need in the Scope drop-down list;
2.2) Click the Specify the comment cell button.

Note: It is optional to check the Force non-bold formatting box based on your needs.

2. In the pop-up Format Comment dialog box, select the cell that you want to apply its comment format to other comments, and then click OK.

3. Then a dialog box pops up to remind you of the total number of changed comments, click OK.

4. Then close the Format Comment dialog box.

You can see that the formatting of all other comments in active sheet or all worksheets has changed depending on the format of the selected comment.

4.3 Change the comment shape

By default, the comment shapes are rectangles. If you want to change the comment shapes for making them look good in Excel, two methods in this section can do you a favor.

4.3.1 Change the shape of a comment with the Change Shape command

You can apply the Change Shape feature to change the shape of a comment in Excel.

1. In the Excel ribbon, click Customize Quick Access Toolbar > More Commands. See screenshot:

2. In the Excel Options dialog box, you need to do the following settings.

2.1) In the Choose commands from drop-down list, choose Drawing Tools | Format Tab;
2.2) Select Change Shape in the commands list box;
2.3) Click the Add button to add the Change Shape command to the right box;
2.4) Click OK to save the changes.

The Change Shape command is now added to the ribbon as the below screenshot shown.

3. Select a commented cell and then click Review > Edit Comment.

4. Click on the border of the displayed comment, and then go to the ribbon to click Change Shape.

Note: The Change Shape command is grayed out and not clickable until you click on the border of a comment.

5. Then click a shape that will serve as the new shape for the selected comment. In this case, I click the Hexagon.

Now the comment shape of the selected cell has been changed to a specified one as shown below.

4.3.2 Easily change comment shapes with an amazing tool

Here recommend the Change Comment Shape feature of Kutools for Excel to help you easily change comment shapes in Excel.

1. Click Kutools > More > Change Comment Shape.

2. In the Change Comment Shape dialog box, all comments in the current workbook are listed in the Comments list box, and you need to do as follows.

2.1) In the Comment list, select a comment that you want to change its shape;
2.2) Choose a new shape in the Select shape box;

Tips: You can repeat the above two steps to apply different shapes to existing comments. However, if you want to apply the new shape to all comments in bulk, go ahead to do the below settings.

2.3) Click the Apply All button.
2.4) Close the Change Comment Shape dialog box.

Notes:

1) You can view the changes instantly in the dialog box.
2) Checking the Show comments box will display a comment in the worksheet when it is selected in the Comments list.

Then comment shapes are changed to the shapes you specified.

4.4 Resize comment to fit the content

As the screenshot below shows, some comment boxes are too small to display all the remarks, while some remarks are too short to fill the comment boxes. In this case, you may need to resize the comment box to fit its content. The methods in this section will help you solve this problem.

4.4.1 Manually adjust the comment border to fit the content

The most common way to resize the comment box is to manually drag the border of the comment box until it reaches the size you need.

1. Select a commented cell, click Review > Edit Comment to make the comment editable.

2. Click on the border of the comment box, then move the cursor over any border of the comment box, and when it turns to , drag the border until it reaches your size. See below demo.

Repeat the above step 1-2 to resize other needed comments.

4.4.2 Auto resize comments to fit their contents in bulk with an amazing tool

If lots of comments need to resize, applying the above method will be time-consuming. Here highly recommend the AutoFit Comment feature of Kutools for Excel. With this feature, you can easily resize comments in an active sheet or all sheets in bulk to fit their content with only several clicks.

1. Click Kutools > More > AutoFit Comment > Active sheet or All sheets as you need.

Then comments in the active sheet or all sheets are resized automatically to fit their content.

4.5 Remove user names from comments

When creating a comment, the Microsoft user name will be added automatically, and you can remove the user name with the below methods.

4.5.1 Manually remove the user name from a specified comment

You can manually remove the user name from a specified comment.

1. Select a commented cell that you want to remove its user name.

2. Click Review > Edit Comment.

3. Select the user name in the comment box, and then press the Backspace key to remove it. See screenshot:

4.5.2 Easily remove user names from comments in bulk with an amazing tool

If you want to remove user names from a large number of comments in an active sheet or all sheets, the above traditional method is time-consuming and annoying. Here introduce the Remove/Add User Name in Comment feature of Kutools for Excel to help you easily remove user names from comments in bulk.

1. Click Kutools > More > Remove/Add User Name in Comment.

2. In the Remove/Add User Name in the Comment dialog box, you need to configure as follows.

2.1 In the Scope drop-down list, there are two options: Active sheet and All sheets, choose the one you need;
2.2 By default, the default Microsoft Office user name displays in the User Name box. You can change it if you need.
2.3 Click the Remove user name from comments radio button;
2.4 Click Apply. See screenshot:

3. Then a dialog box pops up to tell you how many comments are changed, click OK, and then close the main Remove/Add User Name in Comment dialog box.

Now user names are removed from comments in the active sheet or all sheets as you specified.

4.6 Change user name for comments

The above section demonstrates the methods to remove user names from comments, this section is going to show you how to change the comments' user names in Excel.

4.6.1 Change the default user name in Excel

When you insert a comment, the user name is generated by default. Since the user name is the default Microsoft Office user name for the Excel application utilized to insert it, you can change it by following the below steps.

1. Click File > Options.

2. In the Excel Options dialog box, stay in the General pane, change the user name in the User name text box under the Personalize your copy of Microsoft Office section, and then click OK to save the changes.

From now on, when creating a new comment in Excel, the new user name will be generated automatically.

Note: This change will take effect in all Microsoft Office components.

4.6.2 Change the user name for existing comments in the current workbook with VBA

The above method changes the default user name in Microsoft Office. For some Excel users, they just need to change the user name for existing comments only. The VBA code in this section will help you solve the problem.

1. Open the workbook you want to change the user name for existing comments, and then press the Alt + F11 keys.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA into the Code window.

VBA code: Change the user name for existing comments in current workbook

Sub ChangeCommentName()
'Updateby20211008
Dim xWs As Worksheet
Dim xComment As Comment
Dim oldName As String
Dim newName As String
xTitleId = "KutoolsforExcel"
oldName = InputBox("Old Name", xTitleId, Application.UserName)
newName = InputBox("New Name", xTitleId, "")
For Each xWs In Application.ActiveWorkbook.Worksheets
    For Each xComment In xWs.Comments
        xComment.Text (Replace(xComment.Text, oldName, newName))
    Next
Next
End Sub

3. Press the F5 key to run the code.

4. Then a KutoolsforExcel dialog box pops up with the default user name of current comments listing in the text box, click OK to go ahead.

5. Then another KutoolsforExcel dialog box pops up, you need to enter the new user name into the text box, and then click OK.

Then, the user names in all comments of the current workbook are replaced with the new user names.

4.6.3 A handy tool to change the user name for existing comments

In the steps above, you have learned to use the Remove user name from comments option in the Remove/Add User Name in Comment feature to remove the user names from comments in an active sheet or all sheets in a workbook. Here you are recommended to combine another option (Add user name to comments) in the same feature to easily change the user names for existing comments. Please do as follows.

1. Click Kutools > More > Remove/Add User Name in Comments.

2. In the Remove/Add User Name in the Comment dialog box, you need to make the following settings.

With this feature, firstly you need to remove all the user names from comments.

2.1) Choose Active sheet or All sheets in the Scope drop-down list as needed;
2.2) In the User Name text box, the default Microsoft Office user name is displayed in it, you can change it according to your needs;
2.3) Select the Remove user name from comments option;
2.4) Click Apply. Then a dialog box will pop up to tell you how many comments have been changed, close this dialog box;

Now you need to add the new user name to existing comments.

2.5) Enter the new user name into the User Name text box;
2.6) Select the Add user name to comments option;
2.7) Click Apply. (Then a dialog box will pop up to tell you how many comments have been changed, close this dialog box.)
2.8) Close the Remove/Add User Name in the Comment dialog box.

Now the user names in all comments are changed.


5. Copy comments to other cells in Excel

5.1 Copy comments to other cells with the Paste Special feature

You can apply the Excel build-in feature “Paste Special” to copy a comment from one cell to another.

1. Select a commented cell, press Ctrl+ C keys to copy it.

2. Select a cell where you want to have the same comment, right-click it, and select Paste Special from the context menu. See screenshot:

Tips: After selecting the destination cell, you can press Ctrl + Alt + V shortcut to open the Paste Special dialog box.

3. In the opening Paste Special dialog box, click the Comments radio button in the Paste group, and then click OK.

Then the copied comment is pasted to the selected cell.

Note: If you want to copy multiple comments from a range to a new range, you need to first select that range and then follow the steps above.

5.2 Easily copy comments to other cells with an amazing tool

The above method can only copy comments in a range at a time. If comments in multiple non-contiguous ranges need to be copied, how can you do? Here we recommend Kutools for Excel’s Copy Ranges feature to help you handle this problem easily.

1. Hold down the Ctrl key to select the non-contiguous ranges one by one.

2. Click Kutools > Copy Ranges.

3. In the opening Copy Multiple Ranges dialog box, select the Comments radio button, and then click OK.

Note: It is optional to enable the Including row height, Including column width and Rows and columns that are already hidden are not copied options according to your needs.

4. Then another Copy Multiple Ranges dialog box pops up, please select where (just a single cell is needed) to paste the copied comments, and then click OK.

Then only comments in selections are pasted to destination cells.


6. Insert a picture in a comment in Excel

Supposing you plan to send a product quotation scheme to your customers. To make the scheme more vivid, you decide to insert the product images in the comment box of each product cell. When a customer hovers the cursor over a cell with a comment, the corresponding image will be displayed in the comment box. This section is going to show you how to insert a picture in Excel comments.

In Excel, you can apply the Format Comment feature to insert a picture into a comment. Please do as follows.

1. Firstly, you need to insert a comment to a cell: select the cell and then click Review > New Comment.

2. Now a comment is inserted in the selected cell, remove the user name from the comment box.

3. Click on the border of the displayed comment to make the comment box selected.

4. Right-click on the comment box and select Format Comment in the context menu.

5. In the Format Comment dialog box, you need to:

5.1) Click the Colors and Lines tab;
5.2) Click to expand the Color drop-down list, and then choose Fill Effects.

6. In the opening Fill Effects dialog box, go to the Picture tab, and then click the Select Picture button.

7. Then find and select a picture that you want it to display in the comment box.v

8. After opening the picture, it is displayed in the Fill Effects dialog box, click OK to close this dialog box.

9. Click OK when it returns to the Format Comment dialog box.

Now the specified picture is added to the comment box of the selected cell.

10. Repeat the above steps to insert pictures to other comments.


7. Convert cell values to comments and vice versa

To convert cell values to comments or vice versa, you could manually do this by copy-pasting. This section provides effective methods to help you get rid of time-consuming operations.

7.1 Convert cell values to comments

You can apply one of the below methods to convert cell values to comments in Excel.

7.1.1 Convert cell values to comments with VBA

Running the below VBA code can help to automatically convert cell values to comments in a worksheet.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA into the Code window.

VBA code: Convert cell values to comments

Sub CellToComment()
'Updateby20211011
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.NoteText Text:=Rng.Value
Next
End Sub

3. Press the F5 key to run the code.

4. In the popping up Kutools for Excel dialog box, you need to select the cells you want to convert the values to comments, and then click OK.

Then cell values are converted to comments as shown in the below screenshot.

7.1.2 Convert cell values to comments with an amazing tool

If you are an Excel newbie who is not good at handling VBA code, we recommend the Convert Comment and Cell feature of Kutools for Excel to help you easily converting cell values to comments in just a few clicks.

1. Click Kutools > More > Convert Comment and Cell. See screenshot:

2. In the opening Convert Comment and Cell dialog box, you need to configure as follows.

2.1) In the Source range section, click the button to select the cells you want to convert the cell values to comments;
2.2) Click the Convert content of cells into comments radio button;

Tips: If there are already comments in the selected cells, to add the cell contents to the existing comments, you need to choose the Insert content of cell into comment radio button.

2.3) Click OK.

Then cell values are converted to comments immediately as shown in the below screenshot.

7.2 Convert comments to cell values

In addition to converting cell contents to comments, you may also need to convert comments to cell contents. This section is going to show you two methods to deal with this problem.

7.2.1 Convert comments to cell values with VBA

Apply the below VBA code can help to automatically convert comments to cell contents. Please do as follows.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy the below VBA into the Code window.

VBA code: Convert comments into cell contents in Excel

Sub CommentToCell()
'Updated by Extendoffice 20211011
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Rng.NoteText
Next
End Sub

3. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, select the cells that contain the comments you want to convert to cell contents and then click OK.

Now comments are converted to cell contents immediately as the below screenshot shown.

7.2.2 Convert comments to cell values with an amazing tool

Applying Kutools for Excel’s Convert Comment and Cell feature will be the most efficient way to convert comments to cell contents in Excel.

1. After having Kutools for Excel installed, click Kutools > More > Convert Comment and Cell.

2. In the opening Convert Comment and Cell dialog box, you need to:

2.1) In the Source range section, click the button to select the cells contain the comments you want to convert to cell values;
2.2) Click the Convert comments into content of cells radio button;

Tips: If there are already contents in the selected cells, to add the comment contents before or after the existing cell contents, you need to choose the Insert content of comment into cell radio button.

2.3) Click OK.

Now comments are converted to cell contents immediately.


8. Extract comment texts from cells

Supposing you have a large workbook with a lot of cell comments scattered all over the worksheets, now you want to bring these comments together in a single list for some purpose, the methods in this section can help you.

8.1 Extract comment texts with a user-defined function

You can apply the following user-defined function to extract comment texts within a certain range of cells.

1. Press the Alt + F11 keys.

2. Then the Microsoft Visual Basic for Applications window pops up, click Insert > Module. And then copy the below VBA code into the Code window.

VBA code: Extract comment texts

Function getComment(xCell As Range) As String
'UpdatebyExtendoffice20211011
On Error Resume Next
getComment = xCell.Comment.Text
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell, enter the below formula into it and press the Enter key to get the comment content.

Tips: If multiple comments in the same column need to be extracted, you need to select the first result cell, then drag its AutoFill Handle down to get the other results. See screenshot:

=getComment(B3)

Note: For extracting comments in the same column or the same row, this user-defined function can help getting done. However, if you want to extract comments scattered all over the worksheets, we suggest applying the below more convenient method.

8.2 Get a list of all the comments with an amazing tool

The Create Comment List feature of Kutools for Excel helps you easily get a list of all the comments in a worksheet or all worksheets according to your needs.

1. Click Kutools > More > Create Comment List.

2. In the Create Comment List dialog box, you need to configure it as follows.

2.1) In the Scope drop-down list, choose Active sheet or All sheets according to your needs;
2.2) In the Comment listing group, there are two options:

In a new workbook: choose this option, all the comments will be listed in a new workbook;

In a new sheet: choose this option, all the comments will be listed in a new worksheet of the current workbook.

2.3) Click the Create button. See screenshot:

Then you can see all comments are extracted as shown below.


9. Reset all comment positions in Excel

Sometimes, you may drag the comment boxes far away from cells and end up finding that it is not so simple to restore them to their original position. This section provides two methods to help you reset all comment positions in Excel.

9.1 Reset all comment positions with VBA

You can apply the below VBA code to rest all comment positions in an active worksheet.

1. Activate the worksheet where you want to reset all comment positions, press the Alt + F11 keys.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA into the Code window.

VBA code: Reset all comment positions in an active sheet

Sub ResetComments()
'Update 20211012
Dim pComment As Comment
For Each pComment In Application.ActiveSheet.Comments
   pComment.Shape.Top = pComment.Parent.Top + 5
   pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 5
Next
End Sub

3. Press the F5 key to run the code, then all comment positions are restored to their original position. See screenshot:

9.2 Easily reset all comment positions with a handy tool

Here recommend Kutools for Excel’s Reset Comment Position feature to help you reset all comment positions in an active sheet or all sheets by several clicks only.

1. Click Kutools > More > Reset Comment Position > Active sheet or All sheets according to your needs.

Then all comment positions are restored to their original position at once.


10. Delete comments in Excel

This section describes how to delete comments in Excel.

10.1 Delete a comment in a cell

It is very easy to delete a comment in a cell with the build-in Delete Comment feature.

1. Select a cell that you want to remove its comment.

2. Click Review > Delete.

Tips: You can right-click the cell and select Delete Comment in the context menu.

Then the comment in the selected cell is deleted.

10.2 Delete all comments in selections or the entire sheet

To delete all comments in selections or the entire sheet, you can do as follows.

1. Select a range or multiple ranges where you want to delete comments.

Note: Press the Ctrl+ A keys or click the upper left corner button of the worksheet operation area to select the whole worksheet.

2. And then, click Review > Delete, or right-click any cell and select Delete Comment in the context menu.

Then comments are deleted in selections or the entire worksheet.

10.3 Delete all comments from the entire workbook

To delete all comments from the entire workbook, usually, you can use the above method to process worksheets one by one. Here is a handy code to help you quickly delete all comments in bulk from the entire workbook.

1. Open the workbook where you want to delete all comments, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click View > Immediate Window, and then copy the below VBA into the Immediate window.

VBA code: Delete all comments from the entire workbook

For each ws in Worksheets: ws.cells.ClearComments: Next ws

3. Ensure that the cursor is displayed in the Immediate window, then press Enter.

Now all the comments in the current workbook are deleted at the same time.

We hope this tutorial will help you easily solve any comment problems you encounter and improve your work efficiency.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations