Data Validation - Points to Remember

Hi Guys!! Nice to see you again.

We have discussed Data Validation concept and few examples on how to set up the validation criteria in our earlier posts. Now lets continue with few points we have to keep in mind while using this feature.

1. While creating a list, you can refer the list items from a range with in the same sheet. If your list is in a different sheet, you cannot select that range using the Data Validation window. In such case, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. Then type the name in Data Validation window.
2. Input Message will be shown till you deselect the cell. If users do not want it to be there while cell is selected, they can just press Esc. 
3. You can change the place where ‘Input Message’ appears. Once the message is displayed, click on it and drag with your mouse to any place on the sheet. Next time onwards that message will be displayed in its new place. 
4. You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically. Click on 'Circle Invalid Data' under 'Data Validation' in the menu to access this feature.
5. 
6. To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear All. 
7. To find the cells on the worksheet that has data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. Same can be achieved by pressing F5 ('Go To' dialogue box) > Special > Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
8. It is not possible to change the font or font size for items in a list. 
9. The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list. 
10. While applying data validation, you can press F3 while placing cursor in Source field to show all the available Named Ranges. This will be handy to see names available to use in the workbook.

Hope we are all clear on Data Validation. Let’s continue discussion about Data Validation in our next post by discussing few techniques using it. Happy learning!!