Microsoft

An alternative to Excel comments

Use Excel's data validation feature to share meaningful information about specific cells with users.
You probably know that you can display information about a cell or its contents by inserting a comment. Simply select the cell and choose Comment from the Insert menu or click Insert Comment on the Reviewing toolbar. Excel will display the comment when you move the mouse over the cell. However, the hovering issue annoys some users because it blocks adjacent data. If you find this display behavior annoying, you might want to look at data validation. This feature lets you display a meaningful message to the user when you select a cell. To enter a data validation text message, do the following:

  1. Select the cell.
  2. Choose Validation from the Data menu.
  3. Click the Input Message tab
  4. Enter your title and message text and click OK.

Now, you can hover over the cell all you want but you won't see the message text. Displaying the message will block adjacent data, but only after you select the cell. Keep in mind that you're usurping a property of the data validation feature and doing so might have consequences:
  • If you decide you need to use validation to limit data entry, you'll probably want to replace the Input Message text with more appropriate validation text.
  • Using this feature in this way can be a bit confusing to others updating your workbook. You'll just have to decide whether the potential for confusion is worth the trade-off or document your reasons clearly.
  • There's no built-in method for printing Input Message text, whereas it's easy to print Comment text.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
Gordon Or-8
Gordon Or-8

You can also add annotation directly to the cell contents using the =N() function. e.g. =sum(A5:a40)x1.2 in cell A41 =sum(A5:a40)x1.2+N("Total Sales + 20% projected sales") in cell A41 explains why you have multiplied by 1.2 Don't forget the quotation marks....

MicrosoftTraining
MicrosoftTraining

Excellent tip, having to hover over the relevant cell to get the comment has always frustrated myself and my colleagues, but no longer, nice one

USBPort1
USBPort1

You could always highlight the cell using a color, cell border, etc. to make it stand out. Just a thought.

tearher33
tearher33

^_^,,tnx,,it helps,,it adds new knowledge,,,thank you,,

jshaw4343
jshaw4343

The nice thing about comments is the marking to let you know a comment even exists. Otherwise, the user may not know the validation comment even exists.

Glenn from Iowa
Glenn from Iowa

I've always wondered how I could add in-line comments to a formula! Thanks.

johndoe4024
johndoe4024

Learn something new every day. Sweet.