Software

Use Excel's Data Validation to display comments or data entry tips

If you don't like the way Excel displays comments, try using the Data Validation feature to attach text to a cell. Excel will display the information only when a user selects the cell.

Excel comments let you display information about the values in a cell. The notes might be for you or they can be information for users. Excel displays the comment when you mouse over the cell - you don't have to select the cell, just move the mouse over the cell. Consequently, comments can obscure data that you're trying to view and some users find this behavior annoying. If you're in that group, there's an alternative. You can use Data Validation to attach information to a cell and Excel will display it only when a user selects the cell.

To use Data Validation to attach text to a cell, do the following:

  1. Select the cell.
  2. Click the Data tab and then choose Data Validation from the Data Validation dropdown in the Data Tools group. In Excel 2003, choose Validation from the Data menu.
  3. In the resulting dialog box, click the Input Message tab.
  4. Enter a title and message. The title isn't necessary; you can omit it if you like. Notice that Excel checks the Show Input Message When Cell Is Selection option by default. For this technique to work, you must check this option.
  5. Click OK.

Now, change the selection and then move the mouse over the cell - Excel doesn't display the text you entered. Select the cell and Excel does display the message.

This technique is easy to implement, but it uses Data Validation in an unusual way so you might not think of it. It's a good tip to share with users because they can easily do it themselves.

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.

3 comments
Robiisan
Robiisan

For a continuing series of useful tips and tricks to make life easier in "Office-World!"

Ed19kings
Ed19kings

I thought you might have solved my problem. I use a spreadsheet in which I need to store the serial number for the product issued. The cell shows the product and I then inserted the serial number as a comment. This is fine on the main PC but when I am out on the road I am using Documents To Go to read the spreadsheet on the iPad. Unfortunately neither the Comment or the Data Validation method show up. Do you have any other suggestions that might work?

jbenton
jbenton

put the serial no in a different cell? or use VLOOKUP() in an adjacent cell to find it from a list? or add it to the end of the product name? not sure why you want to add it as a cell comment anyway; if you don't want it to be normally visible then you could use alt-enter to force it onto the next line within the cell and drag the cell height to a single line (and align text to top of cell!)