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

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

 

 

To restrict users to entering data from a list, use the data validation feature in Excel. When you base the list on a range of cells, it is useful to ensure that there are no duplicate values in that list, so that the data validation drop-down list isn’t redundant.

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.

1. First, copy the list of data you need for the drop-down list. Highlight the list of values.
2. In the Ribbon, go to Home > Clipboard > Copy or press CTRL + C.
3. Select the cell where you wish the list to be placed and, in the Ribbon, go to Home > Clipboard > Paste or press CTRL + V.

 

 

4. Now, remove the duplicate values from the list using the Remove Duplicates feature.
   Highlight the list, and then in the Ribbon, go to Data > Data Tools > Remove Duplicates.

 

 

5. Remove the check mark from My data has headers and click OK.

 

 

Now, there are only unique values in the list.

Note: There are alternate ways of removing duplicate values without using the Remove Duplicates feature. You can learn about this method here. Also see How to Prevent Duplicate Entries in Excel and Google Sheets.

Add Drop-Down List

To create a drop-down list from these distinct 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, go to Data > Data Tools > Data Validation > Data Validation…

 

 

2. In the Settings tab, in the Allow drop down, 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

Remove Duplicates From List

You can remove duplicate values for your drop-down list in Google Sheets in much the same way.

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

 

 

2. In the Menu, go to Data > Remove duplicates.

 

 

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

 

 

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

 

 

Add Drop-Down List

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, go to 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.