Data validation gives the Excel developer a way of dictating the type of information that can be entered in a worksheet. It is a versatile tool that can be used in many situations.

With data validation you can:

  • Provide users a list of options to choose from
  • Dictate the type or size of input
  • Create custom settings

I often use data validation when I create templates. You typically expect a user to enter a specific thing in a cell but sure enough, there will be the user that has different ideas. Those users different ideas are what can cause errors on your worksheet.

To use data validation, select the cell where information will be entered. Choose data from the menu and then validation (data validation in 2007).  In the data validation window, you will see the drop-down “allow”. This “allow” drop-down is where you can select what type of data you would like to see in this cell, such as date. With the “data” drop-down you can choose to limit the data to between specific ranges or greater than a specific number.

Another option from the allow drop-down is list. List will create a drop-down of your choices in the cell. The options that will be placed in the drop-down come from cells on your worksheet. When you select list, a field will appear called “source”. In the source field, you will input where the items to be placed in the list are on your worksheet. If your list items are in cells a1,a2 and a3, you would put =$A$1:$A$3 in the source box. Those items will now be placed in the drop-down for the cell you are creating the validation.

To have your data validation list on a separate sheet, see this:
Data validation list on seperate sheets

Joseph Harris

Related posts:

  1. Data Validation List on Seperate Sheet How to create list for data validation on different sheets...
  2. How to Import XML Data into Excel This article is a quick primer on using XML data...
  3. Referencing Data in Another Excel Workbook Q: Joe, I have data in another workbook that I...
  4. Duplicates – Finding and Removing in Excel There are many occasions when you may need to remove...
  5. #VALUE! Error in Microsoft Excel The #VALUE error is an error that is seen almost...

This entry was posted on Tuesday, November 11th, 2008 at 12:06 pm and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.