---
title: "Data validation formula practice in Excel | ExcelSavvy"
description: "Practice the formulas behind Excel drop-down lists and custom data validation rules, from a sorted source list to dependent lists and duplicate checks."
canonical_url: "https://excelsavvy.com/practice/data-validation"
meta:
  "og:description": "Practice the formulas behind Excel drop-down lists and custom data validation rules, from a sorted source list to dependent lists and duplicate checks."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_Data%2Bvalidation%2Bformula%2Bpractice%2Bin%2BExcel%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBFeGNlbCBkcm9wLWRvd24gbGlzdHMgYW5kIGN1c3RvbSBkYXRhIHZhbGlkYXRpb24gcnVsZXMsIGZyb20gYSBzb3J0ZWQgc291cmNlIGxpc3QgdG8gZGVwZW5kZW50IGxpc3RzIGFuZCBkdXBsaWNhdGUgY2hlY2tzLg%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIk9yZGVyIiwiUHJvZHVjdCIsIlByb2R1Y3RzIl19LHsibiI6MiwiY2VsbHMiOlsiNTAwMSIsIlN0YXBsZXIiLCIiXX0seyJuIjozLCJjZWxscyI6WyI1MDAyIiwiQmluZGVyIiwiIl19LHsibiI6NCwiY2VsbHMiOlsiNTAwMyIsIlN0YXBsZXIiLCIiXX0seyJuIjo1LCJjZWxscyI6WyI1MDA0IiwiUGFwZXIiLCIiXX0seyJuIjo2LCJjZWxscyI6WyI1MDA1IiwiQmluZGVyIiwiIl19XSwidGFyZ2V0IjpbMSwyXX19%2Cp_Ii9wcmFjdGljZS9kYXRhLXZhbGlkYXRpb24i%2Cs_cYvwA0dRR9NXxkhp.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "Data validation formula practice in Excel | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_Data%2Bvalidation%2Bformula%2Bpractice%2Bin%2BExcel%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBFeGNlbCBkcm9wLWRvd24gbGlzdHMgYW5kIGN1c3RvbSBkYXRhIHZhbGlkYXRpb24gcnVsZXMsIGZyb20gYSBzb3J0ZWQgc291cmNlIGxpc3QgdG8gZGVwZW5kZW50IGxpc3RzIGFuZCBkdXBsaWNhdGUgY2hlY2tzLg%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIk9yZGVyIiwiUHJvZHVjdCIsIlByb2R1Y3RzIl19LHsibiI6MiwiY2VsbHMiOlsiNTAwMSIsIlN0YXBsZXIiLCIiXX0seyJuIjozLCJjZWxscyI6WyI1MDAyIiwiQmluZGVyIiwiIl19LHsibiI6NCwiY2VsbHMiOlsiNTAwMyIsIlN0YXBsZXIiLCIiXX0seyJuIjo1LCJjZWxscyI6WyI1MDA0IiwiUGFwZXIiLCIiXX0seyJuIjo2LCJjZWxscyI6WyI1MDA1IiwiQmluZGVyIiwiIl19XSwidGFyZ2V0IjpbMSwyXX19%2Cp_Ii9wcmFjdGljZS9kYXRhLXZhbGlkYXRpb24i%2Cs_cYvwA0dRR9NXxkhp.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **Drop-down lists and data validation practice exercises**

Write the formulas behind drop-down lists and data validation rules: a source list without repeats, a list that depends on another choice, weekdays only, no duplicates and codes in capitals.

[**Start the first exercise**](https://excelsavvy.com/practice/data-validation/drop-down-source-without-repeats)

FILTER(SORT(UNIQUE(

## **How data validation formulas work**

Data validation controls what can be typed into a cell. A list rule gives you a drop-down, and a Custom rule uses a formula that must return TRUE before Excel accepts the entry. Both can use formulas. A drop-down can pull its choices from a list you build with SORT and UNIQUE in a helper cell, and you point to it with the spill reference, such as `**$D$2#**` in the Source box. A custom rule is written for the first cell of the range.

`**=SORT(UNIQUE(B2:B8))**` builds a list where each product appears once, from A to Z. `**=WEEKDAY(B2,2)<6**` as a custom rule only accepts weekdays, because with 2 as the second argument Monday is 1 and Friday is 5.

Write a custom rule as if it applied to the first cell, and lock what must stay put with $, as in `**=COUNTIF($A$2:$A$6,A2)=1**`, which blocks duplicates. Validation only checks what you type, not what you paste, so a pasted duplicate can still slip through. The exercises below cover a source list without repeats, a dependent list with FILTER, weekdays only, no duplicates and codes in capitals.

1. [**1****A drop-down source without repeats**The drop-down on the order form should offer each product once, from A to Z. In D2, build that list from B2:B8 with SORT and UNIQUE.](https://excelsavvy.com/practice/data-validation/drop-down-source-without-repeats)
2. [**2****A list that depends on the first choice****Premium**The second drop-down should only offer items in the category picked in E1. In E2, list the items in B2:B8 whose category in A2:A8 matches E1, with FILTER.](https://excelsavvy.com/practice/data-validation/dependent-drop-down-list)
3. [**3****Weekdays only****Premium**Deliveries only go out Monday to Friday. Write the custom validation rule for B2 in C2. With 2 as the second argument, WEEKDAY counts Monday as 1, so a weekday is less than 6.](https://excelsavvy.com/practice/data-validation/weekdays-only)
4. [**4****Block duplicates****Premium**Each customer ID may only be used once. The rule is set on A2:A6 and written for A2. In C2, count how many times A2 appears in A2:A6 with COUNTIF and test that it is exactly 1. Lock the list with $.](https://excelsavvy.com/practice/data-validation/block-duplicates)
5. [**5****Codes in capitals only****Premium**Product codes must be typed in capital letters. Write the rule for A2 in C2. Compare A2 with UPPER(A2) using EXACT, since a plain = ignores case.](https://excelsavvy.com/practice/data-validation/capitals-only)

## **Functions in this topic**

- [**FILTER**](https://excelsavvy.com/functions/filter)
- [**SORT**](https://excelsavvy.com/functions/sort)
- [**UNIQUE**](https://excelsavvy.com/functions/unique)
- [**UPPER**](https://excelsavvy.com/functions/upper)
- [**WEEKDAY**](https://excelsavvy.com/functions/weekday)

## **Read the guide**

- [**How to create a drop-down list in Excel, and make it update itself**](https://excelsavvy.com/learn/drop-down-list-excel)

## **Questions**

<details>

<summary>

### How do I make a drop-down list that depends on another cell?

</summary>

Build the second list with FILTER in a helper cell, for example =FILTER(B2:B8,A2:A8=E1), which returns the items for the category picked in E1. Then set the second drop-down's source to that cell with the spill reference, such as =$E$2#. The list updates whenever the first choice changes. This needs Excel 365 or Excel 2021.

</details>

<details>

<summary>

### How do I stop duplicate entries in Excel?

</summary>

Select the range, open Data Validation, choose Custom and enter =COUNTIF($A$2:$A$6,A2)=1 for a rule on A2:A6. Excel then rejects any entry that already appears in the list. The $ signs keep the range fixed, while A2 moves with each cell, so every cell is counted against the whole list.

</details>

<details>

<summary>

### How do I only allow capital letters in Excel data validation?

</summary>

Use a Custom rule with EXACT: =EXACT(A2,UPPER(A2)). EXACT compares the entry with its capital-letter version and cares about case, while a plain = does not, so only entries typed in capitals pass. For a code of exactly five characters, use =LEN(A2)=5 instead.

</details>

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/practice/data-validation#webpage","description":"Practice the formulas behind Excel drop-down lists and custom data validation rules, from a sorted source list to dependent lists and duplicate checks.","name":"Data validation formula practice in Excel","url":"https://excelsavvy.com/practice/data-validation","@type":["WebPage","CollectionPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/practice/data-validation#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/practice/data-validation#/schema/question/1"},{"@id":"https://excelsavvy.com/practice/data-validation#/schema/question/2"},{"@id":"https://excelsavvy.com/practice/data-validation#/schema/question/3"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/practice/data-validation"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/practice/data-validation#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://excelsavvy.com/practice","position":2},{"@type":"ListItem","name":"Data validation formula practice in Excel","position":3}]},{"@id":"https://excelsavvy.com/practice/data-validation#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"How do I make a drop-down list that depends on another cell?","acceptedAnswer":{"@type":"Answer","text":"Build the second list with FILTER in a helper cell, for example =FILTER(B2:B8,A2:A8=E1), which returns the items for the category picked in E1. Then set the second drop-down's source to that cell with the spill reference, such as =$E$2#. The list updates whenever the first choice changes. This needs Excel 365 or Excel 2021."}},{"@id":"https://excelsavvy.com/practice/data-validation#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I stop duplicate entries in Excel?","acceptedAnswer":{"@type":"Answer","text":"Select the range, open Data Validation, choose Custom and enter =COUNTIF($A$2:$A$6,A2)=1 for a rule on A2:A6. Excel then rejects any entry that already appears in the list. The $ signs keep the range fixed, while A2 moves with each cell, so every cell is counted against the whole list."}},{"@id":"https://excelsavvy.com/practice/data-validation#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I only allow capital letters in Excel data validation?","acceptedAnswer":{"@type":"Answer","text":"Use a Custom rule with EXACT: =EXACT(A2,UPPER(A2)). EXACT compares the entry with its capital-letter version and cares about case, while a plain = does not, so only entries typed in capitals pass. For a code of exactly five characters, use =LEN(A2)=5 instead."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
