data validation list ignore blanks and duplicates

data validation in excel allows users to control the type of data entered into a cell. a common use case is creating a dropdown list from a range of values, allowing users to select from predefined options. this tutorial will cover how to create a data validation list that ignores blanks and duplicates.

step-by-step tutorial

1. **prepare your data**

first, you need a list of values from which to create your dropdown. for this example, let's say you have the following values in cells a1 to a10 (some of which are duplicates and some blanks):

```
a1: apple
a2: banana
a3:
a4: orange
a5: banana
a6: grape
a7:
a8: apple
a9: mango
a10:
```

2. **create a unique list without blanks**

to create a unique list without blanks and duplicates, you can use excel's built-in functions. for excel 365 and excel 2021, you can use the `unique` function directly. however, for earlier versions, a combination of other functions is required.

**using excel 365/2021:**

1. in a new column (let's say starting in d1), enter:
```excel
=unique(filter(a1:a10, a1:a10 ""))
```

**using earlier versions of excel:**

1. in cell d1, enter the following array formula (press ctrl + shift + enter):
```excel
=iferror(index($a$1:$a$10, match(0, countif($d$1:d1, $a$1:$a$10) + if($a$1:$a$10="", 1, 0), 0)), "")
```
drag this formula down until you start seeing blank cells (e.g., down to d10).

3. **set up data validation**

now, we will set up the data validation to use the unique list.

1. select the cell where you want the dropdown (e.g., e1).
2. go to the **data** tab on the ribbon.
3. click on **data validation** in the data tools group.
4. in the data validation dialog box, select **list** from the allow dropdown.
5. in the **source** box, enter the range of your unique list. if you used the unique function, it might look something like:
```excel
=d1 (the "" refers to the spill range of the unique function)
```
if you used the array formula, enter:
```ex ...

#DataValidation #ExcelTips #windows
data validation
ignore blanks
ignore duplicates
Excel data validation
data integrity
list validation
unique values
spreadsheet management
error prevention
data entry
drop-down list
filtering data
clean data
data accuracy
user input validation