How to Prevent Duplicate Entries in Excel

Save

Microsoft Excel lets you store thousands of data values in cells. Sometimes, you may want to prevent any duplicate entries from being entered into a specified range of cells. Use the "COUNTIF" statistical function to prevent duplicate entries in a spreadsheet. You provide two parameters to the function. The first is a range of cells, the second is some criteria. This works with both text and numbers. Enter the function in the "Data Validation" section, which also lets you program an error message to appear when duplicate entries are made.

  • Open an Excel spreadsheet. Select a range of cells. For example, click on column "A" to select all of the cells in that column.

  • Click the "Data" tab on the ribbon then click the "Data Validation" button to open the dialog box.

  • Click the "Allow" drop-down menu and select the "Custom" option. Type "=COUNTIF($A$1:$A$500,A1)=1" in the formula text box. This applies the duplicate entry check to cells in the range A1 to A500.

  • Click on the "Error Alert" tab. Type "Duplicate Entry" into the title text box and type "You have already used this entry" into the error message text area.

  • Click the "OK" button to save the data validation settings. Type something into cell A1 then type the same thing into cell A2. The error message pops up to inform you about the duplicate entry.

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!