Stop duplicate entries in a SharePoint list

Short answer: Set the column to hold unique values, and SharePoint blocks any new row with the same value in that column. This works on one column at a time. For a rule like "these four columns together must not repeat", SharePoint has no setting, so one way to start is to give each row one key value that holds the whole rule, then make that key unique.

Why duplicates get into a list

Two people add the same customer on the same day. A third person imports a file that already has half the rows. A month later two rows disagree about the same item. Nobody knows which one is right.

A list does not stop this by itself. It accepts whatever is typed. You have to tell it which value must never repeat.

Step 1. Decide what makes a row unique

Pick the column that must never repeat. Examples: an invoice number, an asset tag, a ticket number. This is your key.

If no single column works, you have a "combined" rule. One example from a Microsoft Q&A thread: a person wanted to block a new row when four columns together matched an existing row. Write your rule in one sentence first: "The same customer, product and month must not appear twice." You will use that sentence in Step 4.

Step 2. Check what Microsoft says about unique columns

Microsoft's page on unique columns says:

"When you create a column, you can specify that the column must contain unique values."
"Uniqueness is not case-sensitive."
"A unique column must also have an index."

Microsoft's page lists the column types that can be unique: Single line of text, Choice (single value), Number, Currency, Date and Time, Lookup (single value) and Person (single value). It lists these as not supported: Multiple lines of text, Choice (multi-valued), Calculated, Custom columns, Hyperlink or Picture, Lookup (multi-valued), Person (multi-valued) and Yes/No. Our advice, not a Microsoft rule: use Single line of text or Number for a key.

Step 3. Turn on Enforce unique values

A Microsoft Q&A answer to the four-column question gives this path: open the list settings, select the column, and turn on Enforce unique values. Exact names may differ on your screen.

Open the list. Choose the gear icon, then List settings.
Under Columns, select the column that must be unique.
Find Enforce unique values and choose Yes.
If SharePoint asks you to create an index, agree. Select OK.
Test it: add a row, then try to add a second row with the same value. The second one should not save.

Microsoft's page says a unique column must also have an index. Remember that the Title column is required by default. If Title is your key, that is fine. If not, rename it or turn off Require for it.

Before you switch this on for a list that already has data, find and fix the repeats first. Microsoft's page does not describe what happens when a column already holds duplicates, so do not count on it. Copy the list first. One way to start is to sort by the key column and look for rows that sit next to each other with the same value.

What the rule does and does not catch

Microsoft's page says uniqueness is not case-sensitive, so ORD-231 and ord-231 count as the same value. It also blocks an edit that would create a repeat, and a restore from the recycle bin that would create one. It also says that making a column unique may be blocked on a list that is over the List View Threshold, so set the rule early.

The page does not say that extra spaces or small spelling differences are caught. "Acme Co" and "Acme Co." are different text. Our advice, not a Microsoft rule: use a Choice or Lookup column for names that repeat, so people pick instead of type.

Step 4. A rule that spans several columns

Microsoft's pages describe Enforce unique values per column. They do not describe a setting that checks two or more columns together. So the key has to live in one column.

A calculated column can join text, for example =[Column1]&" "&[Column2] is a pattern Microsoft shows on its page of common formulas. But Microsoft lists Calculated as a type that cannot be unique, so a joined calculated column cannot hold the rule.

Here is one way to start. Our advice, not a Microsoft rule:

Add a Single line of text column named RecordKey. A name with no spaces is easier to use in a flow.
Decide the format, for example CUSTOMER-PRODUCT-2026-10. Write it where people can see it, such as in the column description.
Have each person, or your form, fill in RecordKey in that format.
Turn on Enforce unique values for RecordKey, as in Step 3.

Now two rows with the same customer, product and month share one key, and the second row will not save. The weak point is typing: if one person writes the key differently, it passes. Test with real examples before you tell the team.

A second option is to check with a flow before the row is added. A Microsoft Q&A thread on a related question suggests other ways to build unique text, such as joining in the item ID. That page does not cover multi-column uniqueness, so treat any such route as something to test.

Step 5. Check with Power Automate (optional)

If you want a flow to look for matches, the SharePoint Get items action supports a filter query. Microsoft's page shows the form ColumnName eq 'value', for example Location eq 'Midwest'. Spaces in a column name are written as _x0020_, which is one more reason to keep RecordKey short and free of spaces.

Two settings matter:

The default item limit is 100. Microsoft says that with default options, Power Automate returns 100 items. Set Top Count in Advanced options to a number that covers your list. Microsoft says you can raise it up to the list view threshold of 5,000.
Microsoft says that on lists with more than 5,000 items, a filter query can return no records if no match sits in the first 5,000 items. Its fix is to turn on Pagination in the action settings.

So set Top Count and Pagination on Get items, filter on RecordKey, then add a Condition: if the result has any rows, do not continue. This only checks the rows Get items fetched. Exact names may differ. For more on the 100-row limit, see "Power Automate Get items stops at 100 rows: how to get them all".

A flow runs after a row is saved. It can tell people about a repeat. It does not stop the save the way Enforce unique values does. Use the column setting for the block and the flow for the message.

Rows you bring in from Excel

Imports also have to follow the rule. Clean the file first and check for repeats in the key column. See "Add Excel rows to a SharePoint list you already have". If the repeats are in a shared spreadsheet and not in a list yet, see "Stop duplicate entries in a shared spreadsheet". For a first list, see "Replace a spreadsheet tracker with a SharePoint list".

What to avoid

Do not pick a Calculated column as the key. Microsoft lists it as not supported for unique values.
Do not switch the rule on for a list full of repeats without cleaning it first. Copy the list and fix repeats before you start.
Do not expect the rule to catch a different spelling or an extra space.
Do not rely on a flow alone. It runs after the save.
Do not treat this as security. A unique rule checks data. It does not control who can see or change a row, and views are not security.
Do not forget that Title is required by default. Rename it or turn off Require.

Honest limits

Enforce unique values handles one column at a time. A rule across several columns needs a key you build yourself, and the key is only as good as the way people fill it. The check does not catch spelling differences the way a person would. Menus and names change in Microsoft 365, so check your own screen. If your list has a lot of rows, test the flow settings on a copy.

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 SharePoint block a repeat across two columns?

Microsoft's pages describe the unique setting for one column. For two or more, build one key column of Single line of text, fill it in a fixed format, and make that column unique. A Calculated column cannot be made unique.

Is the check case-sensitive?

No. Microsoft says uniqueness is not case-sensitive, so ORD-231 and ord-231 count as repeats.

Can a unique column hold duplicate values?

No. Microsoft says a unique column means the list can't have any duplicate values in that column, and the column must also have an index.

Which column types can be unique?

Single line of text, Choice (single value), Number, Currency, Date and Time, Lookup (single value) and Person (single value). Calculated, Yes/No and multi-value columns are not supported.

Can I remove the index later?

Microsoft says not until you first change the column to allow duplicate values.

Why can't I make a column unique in a big list?

Microsoft says making a column unique may be blocked on a list that is over the List View Threshold.

Why does my flow find only some of my rows?

Get items returns 100 rows by default. Set Top Count and turn on Pagination. The flow only checks the rows it fetched.