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

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on February 22, 2023

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

 

datavalidation duplicates intro

 

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.

 

datavalidation copy paste data

 

  1. 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.

 

datavalidation remove duplicates

 

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

 

datavalidation remove headers

 

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…

 

datavalidationduplicates ribbon

 

  1. 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.

 

datavalidation settings

 

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

 

datavalidation duplicates dropdown

 

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.

 

datavalidation duplicates gs copydata

 

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

 

datavalidation duplicates gs menu

 

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

 

datavalidation duplicates gs remove duplicates

 

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

 

datavalidation duplicates gs duplicates removed

 

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.

 

datavalidation duplicates gs select data validation

 

  1. 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.

 

datavalidation duplicates gs datavalidation

 

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

 

datavalidation duplicates gs dropdownlist

AI Formula Generator

Try for Free

See all How-To Articles