When you have a database set up in Excel, it is important to maintain its integrity. You can help ensure that only the right data goes into your database by setting up data validation rules.

To set up data validation for a cell, activate the cell of your choice and choose Data->Validation from the menu Bar. This will display the Data Validation dialogue box.

Under the settings tab, you can use the Allow drop lists to select the type of data value that will be permitted in the cell.

You choices are any value, whole number, decimal, list, date, time, text length, and custom. If you choose any option other than any value, you will be provided with further options to set criteria and or limits on the data values.

This is an example where Decimal has been chosen as the allowed data type.

Notice the other drop lists with options to set conditions and parameter values.

You can set such conditions as: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to. 

With whatever condition you choose, you can specify the associated numerical parameters.

If there are other cells with the same validation settings, you can fill the bottom check box to apply the changes to all of the cells.

Under the input message tab, you will find options to create a message that will appear when the validated cell is selected.

Here you can enter a title and a brief message with instructions or information about the type of data that should go in this cell.

If you place a check in the checkbox at the left, the message will appear when the validated cell is selected.

Under the Error Alert tab, you will find options to specify an error message if the wrong type of data is entered into the cell.

You can use these options to enter a title and message for a user that enters invalid data. You can also use the Style option list to specify what type of alert will appear. A stop alert will not permit you to enter invalid data.

A warning alert can be used to warn of invalid data entry, but permit it if the user is sure.

An information alert is the most gentle, and can provide information to the user before he or she commits to entering the data.

If you select the Settings tab and choose list from the Allow drop menu, you will see a data box where you can enter permissible source data for the cell.

You can type the values that are permissible for the cell directly, or specify a range that contains the values.

If you specify a cell range, make sure there is an equals sign = in front of it.

If you select custom from the Allow drop menu, you can enter a formula to validate your data.

You can base the formula on data from another cell if you wish.

Here, cell range B2:B12 is specified as the source of the data for a validation list in cell E7.

This is the validation list for cell E7; any of the options on the list constitute valid data. The list gets its data from cells B2:B12.

If you enter something that is not on the list, what happens next depends on the type of warning or alert you set up.

Here the custom type has been chosen from the Allow drop list and a formula containing an IF function has been entered.

The formula reads, IF(Not(E5=100),TRUE,FALSE).

This means that if the value entered into cell E5 (the cell that is being validated) is not 100, the function will return TRUE. If it is 100 the function will return FALSE. If the validation formula returns true, the data is permitted. If FALSE, the data is invalid. Basically, this formula allows any number other than 100 to be entered.

This image shows what happens if number 100 is entered into the cell. The type of alert and the message content can be specified when you are setting up the validation rules.

You can select a range of cells, click within the range, and choose Data->Validation to set up validation rules for the entire range. You can also AutoFill a cell with validation rules into other cells in a row or column. These cells will all have the same validation properties.