Excel be able to give message when you enter a douplicate number

New Contributor

How can i make my column tell me if i enter the same invoice twice?

2 Replies
best response confirmed by luciemathieu (New Contributor)


One option is to use conditional formatting.

Select the range.

On the Home tab of the ribbon, select Conditional Formatting > Highlight Cells Rules > Duplicate Values...

Click OK.


Alternatively, if you want to avoid entering duplicates:

Let's say you want to apply this to B2:B100.

Select this range. B2 should be the active cell in the selection.

On the Data tab of the ribbon, click Data Validation.

Select Custom from the Allow drop down.

Enter the formula




Activate the Error Alert tab.

Enter an appropriate error message, for example "You have already used this invoice number!"

Click OK.