Leading the way in Microsoft Office Development

Step 1 - Highlight the cells

Data Validation can be used to restrict the entry of data within a column.

This example shows you how to prevent duplicate entries (both text and numbers) from being entered into a column.

Note that this technique is not restricted to just columns but can also be used with rows and cell ranges (see step 6).

Step 2 - Find the correct formula

You can use worksheet functions in the formulas given for the data validation.

In this example we will use the COUNTIF() function.

This function returns the number of non blank cells with a value that satisfies a condition.

We can use this function to tell us if an entry already exists in a particular range.

=COUNTIF($B:$B,B1)=1

Step 3 - Activate the Data Validation dialog box

Highlight the cells you want to apply the data validation to. In this case the whole of column B.

Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.

In the "Allow" drop-down box, select "Custom" (or List).

Step 4 - Enter a duplicate value

Type the following data onto a worksheet and try and type a duplicate entry into column B.

In this example we have tried to enter the text "France" which already exists.

The following error message will appear and the text will be removed from the cell.

Step 5 - Input Message and Error Alert tabs

It is always worth entering a helpful message into both of these tabs.

Input Message - This message will be displayed when the cell is selected (before the users enters any data). This message should be a polite reminder that the data is restricted and should give the user an indication as to what data is allowed.

Error Message - This message will be displayed if the user enters data that does not conform to the validation rules that have been set up.

Step 6 - Using Rows and Cell Ranges

When using functions and especially cell references it is important to understand the significance between using absolute and relative references.

If you wanted to use this technique for rows and cell ranges then the formulas would be similar to the following:

=COUNTIF($A$1:$D$20,A1)=1

=COUNTIF($4:$5,A1)=1