Avoiding Duplicate Key Values in Excel

A co-worker was looking to store some records in Excel, but wanted to make sure that there were no duplicate values within the key field. Since this was just a flat record system, converting it to Access of FileMaker Pro seemed like overkill, but we were at a loss as to how to prevent key values in Excel since it is not a database system and does not index. The short answer is, you can't without VBA. However, with a little formula you can help avoid the problem of duplicate entries. This is actually a two step process, though for simplicities sake you only need do the first one.

Conditional Formatting

To avoid duplicates, you can emply conditional formatting. This will enable any duplicate values to show up as a different colour and give you a visual clue that this was a duplicate. The draw back to this step, is it does not prevent duplicates it only warns you that you have made a duplicate but it is still up to you to change the key value so it is no longer a duplicate. Here is how to set up the conditonal formatting:

1. Go to the cell that will contain the key entry for your first record. (In this example we use row 1 for a header, and column A is where we are typing our key values. So, our first record will go on row 2, since 1 is for headers, making the cell we select A2)
2. Click on the Format menu, then click on Conditional Formatting... ( a pop-up box will display)
3. There should be a drop-down box that says "Cell Value Is". Click the down-arrow next to that and change the selection to "Formula Is"
4. To the right of our drop-down box is where we will type our formula. This is the formula you will want to type out:
   
   =COUNTIF(A:A,A2)>1
5. Now that we have told Excel what to look for, we need to tell it how to format the cells. Click on the Format... button in the dialogue box, another pop-up box will display.
6. Choose your format to anything you want, I chose changing the font to white and then clicked on the Patterns tab and chose a red background, so that the cell and text will really stand out.
   
7. Click OK, to close the second dialog box. (You should be able to see a preview of how your new formatting will look, if you don't like it go back and make some changes.) Then, click OK on the first dialog box to return to your spreadsheet.
8. Without moving the Excel cursor, copy the cell by hitting [CTRL]+[C] on your windows keyboard.
9. Now, select the entire column by hitting [CTRL]+[SPACEBAR] on your windows keyboard.
10. We will now paste this formatting to every cell in this column by choosing Edit, from the menu, then clicking on Paste Special...
11. Select the Format option under the paste header, then click OK

Now, any time you enter a duplicate value the duplicate values will show up with your special formatting.

Data Validation

This technique will prevent anyone from typing in any duplicate values at all. While this may seem the preferable way its drawback is it only works for someone typing in the cell, if they cut & paste the data validation will not work. So, this technique must be used in conjunction with conditional formatting. However, if they do a lot of manual typing to enter the values this is an extra layer of security to insure no human error. Here is the procedure to turn on data validation:

1. Go to the cell that will contain the key entry for your first record. (In this example we use row 1 for a header, and column A is where we are typing our key values. So, our first record will go on row 2, since 1 is for headers, making the cell we select A2)
2. Select the Data menu, then Validation...
   
3. On the Settings tab, change the Allow drop-down box to Custom.
   
4. In the Formula box, enter this formula:
   
   =(COUNTIF(A:A,A2)>1=FALSE)
5. Make sure the Ignore Blank check box has a check in it
6. Click on the Error Alert tab
7. Our error will be a Stop error, add an appropriate Title (like, Duplicate Data) and explain the error in the Error Message box (I wrote: "You have already used this value, please select another")
8. Click OK
9. Without moving the Excel cursor, copy the cell by hitting [CTRL]+[C] on your windows keyboard.
10. Now, select the entire column by hitting [CTRL]+[SPACEBAR] on your windows keyboard.
11. We will now paste this formatting to every cell in this column by choosing Edit, from the menu, then clicking on Paste Special...
12. Select the Validation option under the paste header, then click OK

You have now added a validation option to each cell in your primary field, and any hand entry that results in a duplicate value will not be allowed by Excel. Between both of these options you should be able to succesfully avoid duplicate values in an Excel database without the use of VBA.