Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Data Validation

| | Information Helpful? Why Not Donate.

Stop Invalid Entries In Excel With Data Validation

Got any Excel 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.

On This Page: Data Validation: How to use it | Using the List Option | Using the List Option to Reference a List on Another Worksheet | Video Tutorials

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

Create a In Cell Validation List Video

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 . See Also Description and examples of data validation in Excel

Create a In Cell Validation List Video

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft