Data Validation
Stop Invalid Entries In Excel With Data Validation
Got any Excel/VBA Questions? Free Excel Help
Create an In-Cell Validation List - Video
Data Validation Download a Workbook Showing Data Validation In Excel 97 Microsoft introduced a cool new feature called Data Validation. It can be found under Data on the Worksheet Menu Bar. Its purpose is to help stop entries into cells that are not within the criteria we set. However, the one draw-back with Validation is that it does not stop a user pasting invalid data into a cell. In fact, the action of direct pasting will remove the Validation from the cell. However, for the most part it does an excellent job at stopping invalid entries.
Data Validation: How to use it
To use Data Validation it is very simple, simply select the cell(s) you wish to restrict entries to and then go to Data>Validation. This will display the Data Validation dialog box, as shown below;
From this Data Validation dialog box select "Whole Number" from the "Allow:" box then "Between" from the "Data:" box and then enter a "Minimum" of 1 and a "Maximum" of 10. You could also refer the "Minimum" and "Maximum" boxes to appropriate cells housing the numbers to use.
Optional: Click the "Input Message" page tab and enter a message that the users sees each time they select any cell that has this Validation applied. See Below;
Optional: Click the "Error Alert" page tab and set the level according to the list below
1) Stop: No user will be allowed to enter data outside the specified criteria. They will have the choice to Retry or Cancel.
2) Warning: User will be allowed to enter data outside the specified criteria only after confirming the entry by clicking "Yes" on the message box they are shown when entering invalid data. The default button is the "No" button and they also have the choice of clicking "Cancel"
3) Information: User will be allowed to enter data outside the specified criteria only after confirming the entry by clicking "OK" on the message box they are shown when entering invalid data. The default button is the "OK" button and they also have the choice of clicking "Cancel".
Once we have done this, we simply click OK and we are done. Now test it by entering invalid data.
Using the List Option
Perhaps one of the coolest parts of Data Validation is its "List" option under the "Allow" box. The list can reside in a single column of cells, or entered directly into the "Source" box separated by commas. The direct entry method is fine for a small list, but not practical for a large list.
Note also the 2 Checkboxes to the right. These are;
1) Ignore Blanks: Meaning if you referenced, say A1:A10 as your "Source" for the list and only A1:A5 had entries, the blank cells would not be shown until such time as they have data in them. Very handy! In most cases this would be left ticked (default)
2) In-cell Dropdown: Meaning a dropdown arrow will be displayed to the user so they can pick from the list. In most cases this would be left ticked (default).
Be aware that Excel will not allow you to reference a range of cells that reside on a different Worksheet to where the Validation cells are.
Using the List Option to Reference a List on Another Worksheet
As mentioned above, Excel will not allow us to reference a range of cells that reside on a different Worksheet to where the Validation cells are. However, here are 2 simple workarounds to this problem.
1) Name the range of cells (e.g. MyList) then instead of using the cell references in the "Source" box for the list, use =MyList
2) Enclose the reference to the cells on another sheet inside the INDIRECT Function. For example rather than;
=Sheet2!A1:A10
Use
=INDIRECT("'Sheet2'!A1:A10")
With either of the 2 methods shown, you can now reference a range that resides on another Worksheet.
Download a Workbook Showing Data Validation .
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
