Data Validation

Depending on the purpose of a particular worksheet, you may want to limit the parameters for data that users can enter. Fortunately, Excel provides a feature for this known as data validation. Learn how to use this tool to control the accuracy of the values entered into your worksheets with the following articles.

Tips, Tricks, and Answers

The following articles are available for the 'Data Validation' topic. Click the article''s title (shown in bold) to see the associated article.

   Adding Pop-Up Documentation to a Cell
Want to have a small help screen pop up when a user selects a particular cell? This can be done by using data validation, as described in this tip.

   Answering Questions in Order
It is not unusual to use Excel to gather the answers to users' questions. If you want your users to answer your questions in a specific order, you can use both data validation and conditional formatting, as described in this tip.

   Complex Data Input Limitations
Data Validation is a great tool in Excel for making sure that whatever is entered in a cell matches your specific criteria. This tip looks at how you can combine desired limitations into a single formula that can be used with Data Validation.

   Contingent Validation Lists
Data validation can be used to create lists of choices for entry into a particular cell. Using the techniques in this tip you can create multiple lists in which the second list is contingent on the choice in the first list.

   Creating Dependent Drop-Lists
Drop-down lists are handy in an Excel worksheet, and you they can be even more handy if a selection in one drop-down lists controls the options presented in another drop-down list. This tip explains how you can use data validation to create these special dependent lists.

   Ensuring Unique Values in a Column
If you want to make sure that only unique values are entered in a particular column, you can use the data validation capabilities of Excel to your advantage. It's easier than you think, as this tip shows.

   External Data Validation
When using data validation, you may want to reference a list of validation criteria contained on a different worksheet. Here's how you can accomplish that task using named ranges.

   Handling Validation for Proper Latitude
When setting up Excel for data entry, you often have to be concerned with what values are acceptable. For example, if users need to enter degrees and minutes for latitudes, then you need to be concerned with how to specify what the acceptable boundaries are for entries. This tip shows how you can use data validation to specify the proper values for acceptable entries.

   Limiting Choices in a Cell
Want to limit what a person can enter into a particular cell? You can use Excel's data validation feature to help enforce what people enter.

   Limiting Entries to Numeric Values
When creating a worksheet, you may need to limit what can be entered into a particular cell. Using data validation you can easily limit input to numeric values within a certain range.

   Limiting Entry of Names
When inputting information into a worksheet, you may need a way to limit what can be entered. This scenario is a prime candidate for the Data Validation capabilities of Excel, but you aren't limited to them.

   Limiting Entry of Prior Dates
Want to establish a "bottom limit" on what dates can be entered in a cell? This tip presents two different ways you can limit date entries.

   Limiting Input to Two Decimal Places
When entering data in a worksheet, you may want to exercise some degree of control on the values that can be entered. This tip looks at two ways you could limit data input to values that contain no more than two decimal places.

   Making a Drop-Down List Indicator Visible
Data validation allows you to create drop-down lists that aid data entry. If you want the indicator for the drop-down lists to be visible even when the cell containing the list is not selected, you may be out of luck.

   Prohibiting Single-Digit Entries in a Cell
The Data Validation capabilities of Excel can be very helpful when you are trying to control what people can enter into a cell. This includes making sure that someone cannot enter just a single digit into that cell.

   Setting Data Validation Input Messages
When using data validation, you might want to have Excel display a message when someone starts to enter information into a cell. Here's how to set up that message.

   Single-Use Drop-Down List
Want to create an easy drop-down list? You can do so by using the data validation features of Excel.

   Specifying a Data Validation Error Message
Data validation is a great tool for limiting what can be input into a cell. Excel allows you to specify what should appear on the dialog box displayed whenever data validation detects a problem with what is input.

   Stopping Validated Data from being Overwritten
Data Validation is a great tool to make sure that data entered in a cell meets whatever criteria you decide. Its weakness, however, is that people can paste information that overwrites any Data Validation rules. This tip looks at a way you can try to overcome this problem.

   Using Data Validation
Want to control what users put into a cell? It's easy to do using a feature called data validation, as described in this tip.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.