Data Validation Unique Values (No Duplicates) in Excel & Google Sheets

This tutorial demonstrates how to ensure unique values in a drop-down list with data validation in Excel and Google Sheets.

 

 

To restrict users to entering data from a drop-down list, you can use the data validation feature of Excel to create the list. As you would base the list on a range of values in Excel, it is useful to ensure that there are no duplicate values in the drop-down list.

Remove Duplicate Values from the Data

Before you create the list from the data, make sure there are no duplicates in the data by using the Remove Duplicates feature in Excel.

First, copy the list of data you need for the drop-down list.

1. Highlight the list of values.
2. In the Ribbon, select Home > Clipboard > Copy or press CTRL + C.
3. Select the cell where you wish the list to be placed and, in the Ribbon, select Home > Clipboard > Paste or press CTRL + V.

 

 

Now, remove the duplicate values from the list using the Remove Duplicates feature.

1. Highlight the list, and then, in the Ribbon, select Data > Data Tools > Remove Duplicates.

 

 

2. Remove the check mark from My data has headers, and then click OK.

 

 

You now have a list of unique values.

Note: there is an alternative way of removing duplicate values without using the Remove Duplicates feature. You can learn about this method here.

To create drop-down list from these values, use data validation.

1. Select the cell where you want the drop-down list to go, highlight the list, and then in the Ribbon, select Data > Data Tools > Data Validation > Data Validation.

 

 

2. In the Settings tab, in the Allow drop-down list, select List. Then in the Source box, select the list of unique values you just created.

 

 

3. Fill in the Input Message and Error Alert as needed, and then click OK.

 

 

Unique Drop-Down List Values in Google Sheets

You can remove duplicate values for your drop-down list in Google Sheets in much the same way as it is done in Excel.

1. Select the list of values you wish to use, and copy them to a different location in your worksheet.

 

 

2. In the Menu, select Data > Remove duplicates.

 

 

3. Remove the check from the Data has header row, and then click Remove Duplicates.

 

 

You get a message back letting you know how many duplicates are removed, and how many rows remain. Click OK to dismiss the message.

 

 

To create the drop-down list using data validation:

1. Select the cell where you wish to put your drop-down list, and then, in the Menu, select Data > Data Validation.

 

 

2. In the Criteria drop down, select List from a range, and then select the range for the drop-down list. You can show validation help text if necessary and then click Save.

 

 

3. The drop-down list with unique values is created.