Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
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.
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.
Changing Fonts in Data Validation Drop-Down Lists
The data validation capabilities of Excel allow you to easily create drop-down lists showing what data is acceptable for a particular cell. If you want to change which font Excel uses in the drop-down list, you're out of luck.
Don't Allow Empty Cells
Data Validation is a great way to limit what a user can enter into a worksheet cell. It may not stop a cell from being left empty, however. This tip explores why this is and how you can make sure that a cell contains a value.
Drop-Down List of Hyperlinks
Creating a drop-down list with Excel's data validation feature can be a nice touch for a worksheet. What if you want the drop-down list to include active hyperlinks? While Excel doesn't allow you to create this, there are a couple of workarounds you can use.
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 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 a Format
When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain cells. This tip provides a couple of different techniques you can use to impose the limitations.
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.
Triggering a Macro for Drop-Down List Changes
Excel 97 has a bug that causes the Worksheet_Calculate event not to trigger when someone picks a value from a data validation drop-down list. This tip discusses the problem, along with some ways you can compensate for it.
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.