Have Fun with Comments

Comments are a useful tool when editing in Excel, especially if more than one user is working on the same worksheet. Comments help communicate ideas, questions, problems, and answers. Comments provide a convenient way to attach information to worksheet cells without getting in the way of your data. In this article you learn how to deal with comments along with some tricks to help you get more from this handy feature.

Bonus: Comments Shortcuts Card

In this article you learn:

  • How to insert a basic text comment.
  • Editing A Comment
  • Advanced Comment Editing
  • Adding a Picture to a Comment
  • Options available on the Comments group
  • Change the shape of the Comment box
  • How to copy comments to other cells in Excel
  • How to Print Comments?
  • Extract Comment Content with a Defined Function
  • New comments functionality in Office 365
  • How do Threaded comments work?
  • Information Comment on Selection.
  • Creating a dynamic Message that grabs value from a cell

Inserting a Comment

  1. Select the destination cell (Say A3)
  2. Go to the Insert Tab of the Ribbon >> Click on New Comment
  3. Alternatively, you can use the Shortcut Shift + F2 C:\Users\nabil\AppData\Local\Temp\SNAGHTML12c8426d.PNG
  4. A Comment Box Appears
  5. Type the wording of your comment “Will Join the Free Dashboard Webinar”
  6. Click outside the Comment Box
  7. A Red triangle appears to the upper right corner of Cell A3 denoting that the cell has a comment.
  8. Hovering over A3 shows the comment text.

Editing A Comment

  1. Select the cell having the comment to edit
  2. On the Insert Tab of the Ribbon >> Click Edit Comment. Alternatively, you can use the shortcut SHIFT + F2. Now the comment is in the Edit mode C:\Users\nabil\AppData\Local\Temp\SNAGHTML12cf10bd.PNG
  3. Sizing handles appear around the comment and a blinking cursor inside the comment box. Start editing your text.
  4. You Can change the Font Type, Size and color using options in the Font group on the Home Tab.

Advanced  Editing for Comments

  1. With the comment in the Edit Mode (SHIFT + F2)
  2. Click on the border of the Comment Box >> Hit CTRL +1
  3. The Format Comment Dialog Box opens (It has 8 Tabs)
  4. On the Font Tab you can do advanced Font editing >> OK
  5. You can resize your comment by dragging the sizing handles.

Adding a Picture to a Comment

Text won’t always be the best way to convey your message. Sometimes, a picture is much more insightful. I use this feature in my personal budgeting to attach a receipt to my expenses!

  1. With the comment in the Edit Mode (SHIFT + F2)
  2. Delete the existing text.
  3. Click on the border of the Comment Box >> Hit CTRL +1
  4. The Format Comment Dialog Box opens (It has 8 Tabs)
  5. Click the Color and Lines Tab C:\Users\nabil\AppData\Local\Temp\SNAGHTML12ea4b32.PNG
  6. Click the down arrow for Color
  7. Towards the bottom of the menu select Fill Effects…
  8. The Fill Effects dialog Box opens
  9. Click on the Picture Tab
  10. Click on Select Picture >> Browse to the location of the picture on your omputer or Online >> Hit Open. C:\Users\nabil\AppData\Local\Temp\SNAGHTML1b8959d9.PNG
  11. You are back to the Fill Effects Dialog Box with a Preview of your picture.
  12. Check the box “Lock Picture Aspect Ratio”.
  13. Hit OK twice
  14. Resize your comment as needed by dragging the sizing handles.

Options available on the Comments group

On the Review Tab of the Ribbon, you have options to insert, delete, navigate, Show/Hide comments. First, select the cell then you Can: C:\Users\nabil\AppData\Local\Temp\SNAGHTML1300c47e.PNG

  1. Edit the Comment text, color, Fill
  2. Delete the comment completely
  3. Navigate to Previous or Next Comment
  4. Keep the Comment of the selected cell displayed
  5. Show All Comments (Toggle Shortcut ALT, R, A, 2)

Comments, by default, stay tucked away from view. You can hover your mouse over an individual comment to read it. Or you can display them all at the same time, by changing central options as follows:

  1. Click on the File Tab of the Ribbon >> Select Options
  2. Click on Advanced (Shortcut ALT, F, T, A)
  3. Under display >> Select: Comments and Indicators

Commenting cells is a favorite feature in Excel. You don’t have to be an expert to need them or use them. As is, comments are a great tool, but you can do more.

Change the Shape of the Comment Box

Most of the time, the shape of comments won’t matter, but you can change it. You might do so to add a bit of interest or even to make a point. In Excel 2007 and later versions, you’ll have to add Change Shape to the Quick Access Toolbar as follows:

  1. Choose More Commands from the QAT drop-down. C:\Users\nabil\AppData\Local\Temp\SNAGHTML13130bb1.PNG
  2. From the Choose Commands From drop-down, choose Drawing Tools | Format Tab.
  3. Highlight Change Shape and click Add.
  4. Click OK.

Once you’ve added the option to the QAT, do the following:

  1. Right-click the commented cell and choose Edit Comment.
  2. Click the comment’s border to select it.
  3. Click the Change Shape drop-down and select a shape. (If the option is grayed out, you didn’t select the comment correctly.)

When you use Cell Comments, you can make notes about the contents of a worksheet cell. You can document where the information came from, how confident you are of its accuracy, whether it might need revision and when. All this information can be important in building a baseline for a forecast.

If several people are entering data and formulas in a workbook, Cell Comments are really helpful. You don’t have to see them unless you want to, but when you want to see them, they can provide great backup

How to copy comments to other cells in Excel

If you want the same comment in multiple cells of your worksheet, you can copy and paste it in other cells without changing their content.

  1. Select the commented cell.
  2. Press Ctrl + C or right-click and choose the Copy option.
  3. Select the cell or the range of cells where you want to have the same comment.
  4. Navigate to the Clipboard group on the HOME tab and open the Paste drop-down list.
  5. Click on the Paste Special option at the bottom of the menu.

You will get the Paste Special dialog box on the screen.

  1. Select the Comments radio button in the Paste section of the dialog box.
  2. Click OK.

Note. You can skip steps 4 – 5 and use the shortcut Ctrl + Alt + V (or ALT, E, S, C) to display the Paste Special dialog box.

As the result, only the comment will be pasted into all the selected cells. If any cell in the destination area already has a comment, it will be replaced with the one you paste.

How to Print Comments?

You can either print all comments at the end of the sheet or as displayed on the sheet. To do that:

  1. Click on the Page Layout Tab of the Ribbon.
  2. Click on the small square in the lower right corner of the Page Setup group. Alternatively, you can use the shortcut ALT, P, S, P. The Page Setup dialog box opens.
  3. In the Page Setup dialog box, click on the Sheet Tab (Rightmost Tab).
  4. Go to the Comment Drop List and click.
  5. Select whether you want to print your comment at the end of sheet or as displayed.
  6. You can either Preview, Print or save these settings by hitting OK.

Extract Comment Content with a Defined Function

In Excel, there is no formula to extract comment contents but, we can create a Defined Function as follows:

  1. Press Alt + F11 keys to enable the Microsoft Visual Basic for applications window.
  2. Click Insert >> Module (or use the shortcut ALT, I, M)
  3. Copy and paste the code here under to the blank new module.

Here is the code:
Function GetComment(FromCell As Range) As String

On Error Resume Next

GetComment = FromCell.Comment.Text

End Function C:\Users\nabil\AppData\Local\Temp\SNAGHTML1bd86051.PNG

  1. Save the code and go back to the sheet (ALT + F11) where you want to extract comment contents.
  2. Select the destination cell (where the text will be extracted). Say C2.
  3. Type =GetComment(B2) the function appears in the intellisense list of excel, and press Enter key.
  4. You can drag fill handle down to extract comments as you need. If there is a comment in column B it will be displayed in the adjacent cell in column C.

    C:\Users\nabil\AppData\Local\Temp\SNAGHTML1bdd5cbf.PNG

C:\Users\nabil\AppData\Local\Temp\SNAGHTML1bdf2133.PNG

New Name and New Functionality in Office 365

Back in Excel 97 what we used to call “Comments” was called “Notes”. C:\Users\nabil\AppData\Local\Temp\SNAGHTML1d3cb6ea.PNG

Now in Office 365 the name is Back. All what I explained above is recognized as “Notes” on the Review Tab. What about the name “Comments” now? It corresponds to a new type of functionality called “Threaded Comments” and it has a purple triangle.

How do Threaded comments work?

  1. Select a Cell >. Click on the Review Tab >> Click New Comment.
  2. Start Typing your Comment then click on the right pointing triangle icon (post).
  3. Comment appears with the cell reference, Date and Time stamp.
  4. If the file is Shared with anyone else then a reply can be typed in the thread.
  5. Clicking on Show Comments >> Opens a pane on the right side where you can read the conversation thread, edit or delete a comment.

Information Comment on Selection. No Hovering, No Red Triangle.

This is not exactly a comment it’s a work around the functionality of data validation. It creates a comment that pops up only when a cell is selected to provide extra information about the cell contents. C:\Users\nabil\AppData\Local\Temp\SNAGHTML1d544119.PNG

  1. Select the cell where you want the comment.
  2. Click on the Data Tab >> Select Data Validation (or use the shortcut ALT, D, L)
  3. Click on the “Input Message” Tab
  4. Make sure the box “Show input message when cell is selected” is Checked.
  5. Type a Title in the Title box
  6. Type your message
  7. Hit Ok.
  8. To check the message, click on the cell. If you change selection the message disappears.

Creating a dynamic Message that grabs value from a cell

Say I want to create an information message in Cell A12 that grabs the result of a payment function in cell B7. If the Price (in cell B2) or the down payment (in cell B3) change the PMT function updates and my information message in cell A12 updates as well.

Part A: Where to write the code?

  1. Right Click on the Sheet Tab >> Select “View Code’
  2. At the top of the visual Basic Editor there are 2 Drop Lists; From The left one >> Select Worksheet, from the Right one >> select Change.
  3. Remove the Selection Change Sub and keep the Worksheet_Change Sub

Part B: Copy and Paste the code between the 2 lines: Private Sub & End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range(“B2”).Address Or Target.Address = Range(“B3”).Address Then

On Error Resume Next

With Range(“A12”).Validation

.Add Type:=xlValidateInputOnly

.InputMessage = Range(“B7”).Value

End With

End If

End Sub

C:\Users\nabil\AppData\Local\Temp\SNAGHTML220723f0.PNG Whenever there is change in B2 or B3 the information message which appears upon selecting A12 will change automatically.

Have Fun with Comments

Comments Shortcuts Card

 

Share This Post
Have your say!
1 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>