How to Autofit Comment Box in Excel?

This post describes how to autofit comment box in Excel. While adding a large comment in a cell in Microsoft Excel, you may have noticed that only a part of it is visible to you by default. You need to manually resize the comment box to see the rest of the comment data. While this is okay for one or two comments, resizing each comment box when there are a lot of comments in an Excel sheet can be tiresome and time-consuming. This post explains how you can use some Excel settings to automatically resize the comment box to fit the entire comment data.

How to Autofit Comment Box in Excel?

There are 2 ways in which you can autofit the comment box in Excel:

Using the Format Comment option
Using VBA Code

Let us see these in detail.

How to Auto Size Excel Comment Box to fit its content

1] Autofit Comment Box in Excel using the Format Comment option

Right-click on the cell which has the comment and select the Show/Hide Comments option. This will make the comment box visible (without mouse hover).
Take your mouse cursor anywhere on the outer edge of the comment box and right-click when the mouse pointer turns into a ‘4 Way Arrow’ symbol.
Select the Format Comment… option.
In the Format Comment window, switch to the Alignment tab.
Check the Automatic size checkbox.
Click on the OK button.

You will notice that the comment box has been resized to show the entire comment content.

Notes:

The above method will auto-size the selected comment box only. To auto-size all comment boxes in the active Excel sheet, use the next method.
The main difference between using the ‘Automatic size’ option and manually resizing the comment box is that the ‘Automatic size’ option dynamically changes the size of the comment box. So if you’ve enabled this option, the comment box will automatically adjust to the new size if you update the comment. Whereas if you’ve not enabled it, you will have to manually resize the comment box every time you update the comment.

2] Autofit Comment Box in Excel using VBA Code

Click on the Developer tab.
If you can’t find the Developer tab, you can enable it in Excel. Go to File > More… > Options. Select Customize Ribbon on the left panel. Select Main Tabs in the dropdown on the left-hand side. Then select Developer and add it to the column on the right. Select OK. You will now see the Develop tab in your Excel sheet.
From the options available in the Developer Tab, click on Visual Basic on the extreme left.
Then click on Insert > Module.

Type the following code in the code editor window that appears:

Sub FitComments()
    Dim c  As Comment
    For Each c In ActiveSheet.Comments
        c.Shape.TextFrame.AutoSize = True
    Next c
End Sub

Run the code. This code will automatically resize all comment boxes in your Excel sheet to fit their respective comment data.

The above methods will increase the width of the comment box to fit the entire comment data in a single line if the comment doesn’t have any line breaks. So if your comment data is longer than the width of your screen, you will have to use the horizontal scroll bar at the bottom of the screen to see the entire comment. To resolve this issue, you can set the height and width of the comment box to make the entire content visible on your PC screen.

Here’s the VBA code to fix the height and width of the comment box in Excel:

Sub ResizeALLcomments()
    Dim c  As Object
    For Each c In ActiveSheet.Comments
        c.Shape.Width = <width>
        c.Shape.Height = <height>
    Next c
End Sub

The above code will fix the width and height of the comment box to the user-specified values (<width> and <height>). You can set these values according to the length of your comment data. For example, you can set the width as 200 and the height as 100. Do not use any quotes or unit (such as cm) with the value of width and height in this code.

This sums up how to autofit comment box in Excel. Hope you find this useful.

How do I expand all comments in Excel?

There are 2 ways to expand comments in Excel. You can expand comments one by one or expand all the comments in one go. To expand individual comments, right-click on the cell that has the comment and select the Show/Hide Comments option. To expand all the comments, go to the Review tab on top of the Excel sheet and select the Show/Hide Comment option.

How do you align a comment box in Excel?

Excel allows you to change the align comment box or format of a comment in Windows 11/10. To format the comment text, select part of the text or the entire comment text and then right-click and select the Format Comment option. Choose the desired Font, Font Style, Size, and Effects, and then click on OK. The comment will be formatted.