Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Editing a Comment Close to Its Cell.
by Allen Wyatt
(last updated August 15, 2015)
Peggy has several cells in a worksheet that have comments associated with them. When she right-clicks on one of these cells, she can choose Edit Comment from the resulting Context menu in order to edit the comment. If the comment was one she created in a previous session with Excel, it is not unusual for the comment to open up elsewhere in the worksheet, sometimes several screens away. Peggy is wondering if there is a way to make the comment appear next to the cell it goes with.
This condition can be caused by several things. For instance, it is possible that while previously editing the comment, you clicked the comment box's border and dragged the comment to a different place on the worksheet. If you did this, then Excel remembers where the comment was moved to and always displays it in the remembered location.
Another common cause is that you do some filtering on your data, which results in some of the rows or columns being hidden while the filter is in place. If you then edit comments in the filtered cells, you have effectively "moved" the comment from the original location to a new location that is associated with the row or column visible on the screen. When you later remove the filter and try to edit the comment, it remembers where it was previously edited, and that is where the new editing opportunity takes place.
In both of these instances, the normal solution is to just grin and bear it—manually move the cells from where they are to where you want them. However, if you have this problem with a lot of cells, all the manual moving can be a real bother. In that case, you may want to use a macro to do the moving for you.
Sub MoveComments1() Dim cmt As Comment For Each cmt In ActiveSheet.Comments With cmt .Shape.Top = .Parent.Top .Shape.Left = .Parent.Offset(0, 1).Left End With Next cmt End Sub
This macro moves all the comments in a worksheet so that their upper-left corner is the same as the upper-right corner of the cell to which they are attached. This puts the comments right next to their cells, which is where you want them.
If you would like to adjust all comments in an entire workbook, as well as "autosize" each of the comment boxes, then you can use this variation on the macro:
Sub MoveComments2() Dim wbk As Workbook Dim wks As Worksheet Dim cmt As Comment Dim rngC As Range Dim lArea As Long Set wbk = ActiveWorkbook On Error Resume Next For Each wks In wbk.Worksheets For Each cmt In wks.Comments With cmt .Shape.TextFrame.AutoSize = True If .Shape.Width > 200 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 .Shape.Height = (lArea / 200) * 1.1 End If .Shape.Top = .Parent.Top .Shape.Left = .Parent.Offset(0, 1).Left End With Next cmt Next wks End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9703) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Editing a Comment Close to Its Cell.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
The default color used to format comments is determined by Windows, not Excel. You can adjust the formatting of individual ...Discover More
If you frequently add comments to cells in a worksheet, Excel provides a variety of tools you can use to manage those ...Discover More
When you protect a worksheet, Excel stops users from editing or otherwise making changes to the data in the worksheet. If you ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.