Stop duplicate entries in a shared spreadsheet

In Excel, select the column, open Data Validation, set Allow to Custom and enter =COUNTIF($A$2:$A$10,A2)=1 to block repeats as people type. Microsoft says validation does not check copied or filled data, and you cannot change validation settings if the workbook is shared or the sheet is protected. For a file with several editors, consider a SharePoint list with Enforce unique values on the key column.

Why duplicates keep appearing

Two people add the same customer, invoice number or request on the same day. Neither sees the other row. A third person pastes in an export that already has half the rows. A month later two rows say different things about the same item, and nobody knows which one is right.

Common causes:

No rule says what makes a row unique. Is it the invoice number, the email, or the name plus the date?
Many people type free text. "Acme Co" and "Acme Co." look different to a person and to Excel.
Nobody checks. Duplicates are found when a total looks wrong.

Step 1. Decide what makes a row unique

Pick one column that must never repeat. This is your key. Examples: invoice number, asset tag, ticket number. If no single column works, add one. A simple approach is a running number, such as INV-0001.

Write the key rule in one sentence at the top of the sheet: "Invoice number must not repeat." People follow rules they can see.

Step 2. Block repeats with data validation

Microsoft's "More on data validation" page says: "Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear." So a pasted repeat can get past the rule. Highlighting in Step 3 is your second check.

Microsoft's data validation page documents a custom rule that accepts only unique values in a range. One way to start:

Select the cells to check, for example A2:A10.
Go to Data, then Data Validation.
On the Settings tab, set Allow to Custom.
In the formula box, enter =COUNTIF($A$2:$A$10,A2)=1
Select OK.

Microsoft notes that you enter the formula for the first cell and then copy it down, so the second part of the formula matches each row. Also test the rule with a repeated value and a new value.

On the Error Alert tab you pick a style. Stop makes the person fix the entry. Warning asks Yes or No before it lets the entry stand. Use Stop for a key column.

Step 3. Highlight repeats that still get in

Validation covers the cells you selected. Highlighting helps you see problems anywhere. Microsoft describes this path:

Select the cells to check.
On the Home tab, open Conditional Formatting.
Point to Highlight Cells Rules, then choose Duplicate Values.
Pick a format and select OK.

Every repeated value now shows in a color. Add a line to the sheet: "Red means a repeat. Fix it before you leave."

@@KEEP@@

Step 4. Remove repeats on a copy, not the original

Excel has a Remove Duplicates command on the Data tab, in the Data Tools group. You choose which columns to compare. Rows that match on those columns are treated as repeats.

Microsoft is direct about the risk: removing duplicate values permanently deletes them, and it advises copying the original data elsewhere first. So:

Save a copy of the file with today's date in the name.
Select the range, then choose Remove Duplicates.
Choose only the key column, or the set of columns that together define a repeat.
Check the result against the copy before you share it again.

To match on two columns, such as customer and invoice date, tick both. Rows count as repeats only if all chosen columns match.

Limits you should know

Each tool has a gap, and a shared file makes the gaps larger.

Validation rules cannot be changed in an old-style shared workbook. Microsoft says you cannot change data validation settings if your workbook is shared or your sheet is protected. This means the old Shared Workbook feature, not a file people co-author in OneDrive or SharePoint. Set the rule first, and unprotect the sheet to change it.
A rule covers only the range you selected. A row added below that range is outside the rule. Select a range that is longer than your data, or use a table, then test by adding a row.
Matching goes by what you see. Microsoft says duplicates are decided by the value displayed in the cell, so the same date in two formats counts as two different values. Text that differs by a stray space or a period is also not the same text. Keep the key column plain and consistent.
Highlighting does not work everywhere. Microsoft says Excel cannot highlight duplicates in the Values area of a PivotTable report.
Remove Duplicates does not work on outlined data. Microsoft says you cannot remove duplicate values from data that is outlined or has subtotals. Remove the outline or subtotals first.
Highlighting shows the problem after it happens. It does not stop it.

A stronger option: a SharePoint list with unique values

If the same file is edited by many people every day, one fix is to move the data into a SharePoint list. For the full move, see "Replace a spreadsheet tracker with a SharePoint list". For the version problem that often goes with duplicates, see "Spreadsheet versions out of sync: why it happens and how to fix it".

Microsoft's page on unique and lookup columns says: "When you create a column, you can specify that the column must contain unique values." In list settings the option is named Enforce unique values; exact names may differ on your screen. When it is on, the list refuses a new item whose value in that column already exists. Microsoft also says "Uniqueness is not case-sensitive," so INV-0001 and inv-0001 count as the same value.

Our advice, not a Microsoft rule: test the setting on a small copy of your list first, and make sure the key column has no repeats before you turn it on.

One way to start:

Create the list and import your rows. Clean the key column first, so no repeats are in it.
Pick the column that holds your key, such as Invoice number.
Open the list settings, select the key column, and set Enforce unique values to Yes. Microsoft says "A unique column must also have an index," so accept if SharePoint offers to index it. Microsoft also warns you may be blocked if the list already has more items than the List View Threshold, so turn it on early.
Try to add an item with a key that already exists. The list should refuse it. Check that it does.

If the list grows large, indexed columns also help with the view limit. See "SharePoint list over 5,000 items: what breaks and the fix".

What to avoid

Do not rely on highlighting alone. It tells you after the fact.
Do not run Remove Duplicates on the only copy of the file. The deletion is permanent.
Do not choose too many columns in Remove Duplicates. Rows only count as repeats if all chosen columns match, so a loose choice keeps repeats and a wide choice may delete real rows. Pick the key.
Do not let the key column be free text with mixed formats. Use a code, a number or a choice list.
Do not turn on Enforce unique values for a column that holds names or other values that can really repeat. Two different people can share a name.
Do not treat the unique setting as a cleanup tool. It stops new repeats only after the old ones are gone.
Do not change a shared workbook's rules without a backup. Save a dated copy first.

Honest limits

No setting fixes habits. If the key is not clear, people will still create two records for one thing. A unique column also stops a repeat only in that one column. Two rows can differ in the key but describe the same customer. Pick a key that people already use, and review the list once a month.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

Can Excel stop a duplicate completely?

Not in every case. A rule covers only the cells you selected, and Microsoft says you cannot change it in an old-style shared workbook. Test the rule, then use highlighting as a second check.

What counts as a duplicate in Excel?

Excel goes by the value shown in the cell. The same date in two formats counts as two different values, so keep the key column in one format.

Will Remove Duplicates ask before it deletes?

Do not count on it. Microsoft says the deletion is permanent, so save a dated copy of the file first.

How do I remove duplicates in Excel but keep one?

Use Remove Duplicates on the Data tab. Choose the key column, or both columns for a two-column match, and run it on a copy.

Does validation stop pasted duplicates?

No. Microsoft says validation shows messages and blocks entries only when people type directly in a cell, and the messages do not appear when data is copied or filled. Use highlighting as a second check.