Share via

Preventing duplicates in Excel

Anonymous
2022-07-12T09:27:23+00:00

I have a column of invoice numbers/letters that is constantly added to. How do a prevent a duplicate entry? Highlight the whole column then click on Data then Data Validation? What is the correct custom formula?

Microsoft 365 and Office | Excel | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-07-12T10:01:41+00:00

    I would use conditional formatting to identify when there are duplicate values. Then you/the user can decide which one to keep. Presumably the first, but I rather let a person decide.

    • Select the column
    • Home tab > Style group >Conditional formatting drop down > Highlight cells rule option > Duplicates Values
    • Pick a color you want for highlight
    6 people found this answer helpful.
    0 comments
  2. Anonymous
    2022-07-12T10:02:05+00:00

    Hello Michelle S,

    Thank you for contacting the Microsoft Community.

    If I am starting my invoice numbering from column A, from row 2

    =COUNTIF($A$2:$A$20,A2)=1

    Below are two links that can help in guiding you to achieve what you desire with the excel file

    https://www.excel-easy.com/examples/prevent-duplicate-entries.html

    https://www.howtoexcel.org/how-to-prevent-duplicate-data-entries/

    Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

    Best Regards
    Fuad

    2 people found this answer helpful.
    0 comments