UIS Tip of the Week: Preventing Duplicates in Microsoft Excel While Entering Data

One time working in Microsoft Excel, I mistakenly typed duplicate headings. It was a big deal because I didn't realize I did that until well after I entered a whole mess of data under those headings. Since the headings were off, I had to re-enter all the data. Now I know I could have saved myself this extended agonizing headache.

In Excel, you can set up an alert to appear if you mistakenly enter duplicate data in a selected range of cells.

1. Select a range of cells, such as A2 through A20.

2. From the Data menu, select Validation.

3. Click on the Settings tab.
4. Select Custom from the Allow drop-down list.
5. In the Formula text box, type the formula =COUNTIF($A$2:$A$20,A2)=1.

6. Click on the Error Alert tab.
7. In the Title text area, type Duplicate Entry.
8. In the Error message text area, type The value you entered already appears in the list above.

9. Click OK.

Now if you enter data in those cells, and if you mistakenly enter duplicate data in one of those cells, you'll see the following pop-up message: