Data Validation is a very common feature in Excel. In this article, we will discuss about 2 methods to add hints for cells with data validation.
Suppose you have set data validation in certain cells. And now you will send the file to other people because you need to collect their information. If you only set data validation without any prompt, other people will probably input invalid value into the target cell. As a result, they will need to spend more time than expected on the file. Therefore, to make things smoother, you can add hints to inform users before sending the file. Here we have 2 ways to achieve this task.
Method 1: Show Prompt Message in the Cell
When you set data validation for certain cell, you will find that those cells have no difference with others from appearance. Now you may input the prompt message before setting data validation. Thus, when users see this message, they will know what to input.
- Input the content into target cell.
- And then set the data validation for the target cell.
This order can make sure that the message can remain in the cell.
Thus, the interface will be like this:
Of course you can set for the whole column. Thus, user will know what to input as soon as they see this information.
On the other hand, if you have already set the data validation for cells, you will see the error alert once you input the invalid content. If you still want to input the information, you may disable the error alert.
In addition, you can take an easier way.
- Input the information into another blank cell which doesn’t have data validation.
- Copy or cut the cell.
- Now right click the target cell.
- And then in the menu, choose the “Value” of the paste option. Thus, the data validation and error alert will remain in the cell. And the message can also be input into it.
Method 2: Show Messages when Select the Cell
This is the second method. And when you choose the cell, a message will appear and remind users to input certain contents. Besides, even if the cell has no data validation, you can also set the message.
- Click the target cell.
- And then click the tab “Data” in the ribbon.
- Next click the button “Data Validation”.
- And in the “Data Validation” window, choose the tab “Input Message”.
- Then input the texts for the title and the message.
- After all the setting, click “OK”.
Thus, you have finished all the settings. And when you click the cell, you will see the message near the text.
A Comparison of the 2 Methods
In this table below, we categorized the comparison of the 2 methods that can add hints to cells. We have list all the advantages and disadvantaged of them. And you can refer to it as a reference.
|Information in Cell||Show Message when Select cell|
|Input Message into cell.||Set in the “Input Message” of data validation.|
|1. Users will know what to do immediately after seeing this information.
2. When users input corresponding values, the message will disappear.
|1. When user selects the cell, the message will appear and remind user.
2. It will not mess up the interface of the worksheet.
3. You can enter detailed messages with many words.
|1. The interface of the worksheet will be not so clear.
2. You can only input few words into the cell.
|1. When the message appears, it will block other cells.
2. Whenever you click this cell, this message will appear even if sometimes you don’t need it.
Data Validation can be Destroyed by Various Reasons
Sometimes, your Excel will corrupt due to many different reasons. As a result, the data validation that you have set in Excel will probably be invalid. This is also a result that may happen in a data disaster. When such accident happens, an Excel recovery tool is a must for you. This tool is easy to manipulate and you are sure to get back all your files.
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com