Prevent duplicate records in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to enter. The condition is that there can't be two identical records in the Excel Table.

The image above shows a warning that the Excel user tried to enter a duplicate record which is not valid, the dialog box tells you that "This value doesn't match the data validation restrictions defined for this cell.

There are three buttons available, "Retry", "Cancel" and "Help" on the dialog box. The "Retry" button leaves the value as it is but selected, this allows you to edit the value you just entered. The "Cancel" button removes the value you just entered. The "Help" button opens a web page at Microsoft Support explaining how Data Validation works.

Create an Excel Table

An Excel Table allows you to dynamically apply Data Validation to new data meaning if you enter a new record below the data set it will also have the same data validation rules automatically as the rest of the data.

With this setup there is no need to adjust cell ranges when new data is added or deleted, the Excel Tables does that for you instantly.

1. Select any cell in the data set.
2. Press shortcut keys CTRL + T to open the "Create Table" dialog box, see image above.
3. Enable/disable checkbox "My table has headers" accordingly.
4. Press with left mouse button on "OK" button to apply settings and create an Excel Table.

The data set is now an Excel Table which you can tell by the cell formatting and the arrows next to column headers. You can change the Excel Table style and remove "Filter" arrows next to column headers if you want.

A new tab on the ribbon named "Table Design" appears if you select on any cell in the Excel Table, it allows you to change Table Options and Styles.

Apply Data Validation

Data Validation lets you control what the Excel user can and can't enter using different methods. We are going to use a "Data Validation" formula that will trigger a dialog box warning if conditions are not met.

1. Select data in your Excel Table, I selected cell range B3:D9.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data validation" button.
   
4. Choose Custom, see image above.
5. Type in "Formula:" field:
   =COUNTIFS(INDIRECT("Table1[First name]"), $B3, INDIRECT("Table1[Last name]"), $C3, INDIRECT("Table1[Date]"), $D3)<=1
6. Press with left mouse button on OK button to apply settings and create "Data Validation" to cell range B3:D8

If you enter a duplicate record, the following error message appears.

Explaining the data validation formula in row 9

To learn how formulas work in greater detail I recommend the "Evaluate Formula" tool which is built-in to Excel, you can use this tool for "Data Validation" formulas as well. Copy the "Data Validation" formula and paste to a cell.

I pasted the formula to cell F3 and then pressed Enter. Select cell F3, press with left mouse button on tab "Formulas" on the ribbon. Press with left mouse button on "Evaluate Formulas" button to show the "Evaluate Formula" dialog box, see image above.

The INDIRECT function is a volatile function meaning it recalculates every time Excel recalculates, this may make it more cpu-intensive if used extensively. This function is needed in order to reference values to an Excel Table in a Data Validation formula.

This is why you get the warning text "A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not" in the "Evaluate Formula" dialog box, see image above.

Underlined expressions are what is about to be evaluated in the next step and italic values are the result. Press with left mouse button on the "Evaluate" button to move to the next calculation step in the formula. Press with left mouse button on "Close" button to dismiss the dialog box.

Step 1 - How to reference values in Excel data validation formulas

You can't reference Excel Tables in Data Validation formulas, however, there is a workaround. The INDIRECT function allows you to reference Excel Tables.

References to Excel Tables are called "structured references" and they don't change when values or records are added or deleted in the Excel Table.

Table1[First name]

becomes

INDIRECT("Table1[First name]")

The downside is that you need to change the formulas if you change the Table name or the Table header names accordingly, they do not change automatically in this case.

Step 2 - Count how many times a record exists in a table

The COUNTIFS function lets you count cells based on multiple conditions, we are going to count rows based on the values the Excel user enters in the Excel Table.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

We will use as many criteria pairs (ranges and criteria) as there are columns in the Excel Table. I will use six arguments as there are three columns in my Excel Table. You need to adjust that to your specific Excel Table.

COUNTIFS(INDIRECT("Table1[First name]"), $B3, INDIRECT("Table1[Last name]"), $C3, INDIRECT("Table1[Date]"), $D3)

becomes

COUNTIFS($B$3:$B$9, $B3, $C$3:$C$9, $C3, $C$3:$D$9, $D3)

becomes

COUNTIFS({"Stieg"; "Jonas"; "Camilla"; "Lars"; "Henning"; "Jan"}, $B3, {"Larsson"; "Jonasson"; "Läckberg"; "Kepler"; "Mankell"; "Guillou"}, $C3, {40807; 41324; 41215; 40777; 41081; 40970}, $D3)

becomes

COUNTIFS({"Stieg"; "Jonas"; "Camilla"; "Lars"; "Henning"; "Jan" ; "Stieg"}, "Stieg", {"Larsson"; "Jonasson"; "Läckberg"; "Kepler"; "Mankell"; "Guillou"; "Larsson"}, "Larsson", {40807; 41324; 41215; 40777; 41081; 40970; 40807}, 40807)

and returns 1 in cell F3.

Step 3 - Check if number is smaller than or equal to 1

The less than sign and the equal sign together means that the number must be equal to or less than 1 in order to return True.

COUNTIFS(INDIRECT("Table1[First name]"), $B3, INDIRECT("Table1[Last name]"), $C3, INDIRECT("Table1[Date]"), $D3)<=1

becomes

1<=1

and returns TRU. The "Data validation" error message does not appear.

Get the Excel file

Prevent-duplicate-records-data-validation.xlsx

Recommended articles

• Block duplicate entries in Excel table
• Apply data validation to cells

Data validation category

Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]

Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Excel categories

Comments (2)