---
title: "Data validation formula practice: drop-down sources and custom rules | PracticeExcel"
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://practiceexcelonline.com/practice/data-validation"
locale: en
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://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RGF0YSB2YWxpZGF0aW9uIGZvcm11bGEgcHJhY3RpY2U6IGRyb3AtZG93biBzb3VyY2VzIGFuZCBjdXN0b20gcnVsZXM%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBFeGNlbCBkcm9wLWRvd24gbGlzdHMgYW5kIGN1c3RvbSBkYXRhIHZhbGlkYXRpb24gcnVsZXMsIGZyb20gYSBzb3J0ZWQgc291cmNlIGxpc3QgdG8gZGVwZW5kZW50IGxpc3RzIGFuZCBkdXBsaWNhdGUgY2hlY2tzLg%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIk9yZGVyIiwiUHJvZHVjdCIsIlByb2R1Y3RzIl19LHsibiI6MiwiY2VsbHMiOlsiNTAwMSIsIlN0YXBsZXIiLCIiXX0seyJuIjozLCJjZWxscyI6WyI1MDAyIiwiQmluZGVyIiwiIl19LHsibiI6NCwiY2VsbHMiOlsiNTAwMyIsIlN0YXBsZXIiLCIiXX0seyJuIjo1LCJjZWxscyI6WyI1MDA0IiwiUGFwZXIiLCIiXX0seyJuIjo2LCJjZWxscyI6WyI1MDA1IiwiQmluZGVyIiwiIl19XSwidGFyZ2V0IjpbMSwyXX19%2Cp_Ii9wcmFjdGljZS9kYXRhLXZhbGlkYXRpb24i%2Cs_xxdCtWx_OccbAHfl.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "Data validation formula practice: drop-down sources and custom rules | PracticeExcel"
  "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RGF0YSB2YWxpZGF0aW9uIGZvcm11bGEgcHJhY3RpY2U6IGRyb3AtZG93biBzb3VyY2VzIGFuZCBjdXN0b20gcnVsZXM%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBFeGNlbCBkcm9wLWRvd24gbGlzdHMgYW5kIGN1c3RvbSBkYXRhIHZhbGlkYXRpb24gcnVsZXMsIGZyb20gYSBzb3J0ZWQgc291cmNlIGxpc3QgdG8gZGVwZW5kZW50IGxpc3RzIGFuZCBkdXBsaWNhdGUgY2hlY2tzLg%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIk9yZGVyIiwiUHJvZHVjdCIsIlByb2R1Y3RzIl19LHsibiI6MiwiY2VsbHMiOlsiNTAwMSIsIlN0YXBsZXIiLCIiXX0seyJuIjozLCJjZWxscyI6WyI1MDAyIiwiQmluZGVyIiwiIl19LHsibiI6NCwiY2VsbHMiOlsiNTAwMyIsIlN0YXBsZXIiLCIiXX0seyJuIjo1LCJjZWxscyI6WyI1MDA0IiwiUGFwZXIiLCIiXX0seyJuIjo2LCJjZWxscyI6WyI1MDA1IiwiQmluZGVyIiwiIl19XSwidGFyZ2V0IjpbMSwyXX19%2Cp_Ii9wcmFjdGljZS9kYXRhLXZhbGlkYXRpb24i%2Cs_xxdCtWx_OccbAHfl.png"
---

> Index of every page on this site: [llms.txt](https://practiceexcelonline.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://practiceexcelonline.com/practice/data-validation/drop-down-source-without-repeats)

FILTER(SORT(UNIQUE(

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://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.com/practice/data-validation/capitals-only)

## **Functions in this topic**

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

## **Read the guide**

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

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.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: drop-down sources and custom rules","url":"https://practiceexcelonline.com/practice/data-validation","@type":["WebPage","CollectionPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/practice/data-validation#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/practice/data-validation"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/practice/data-validation#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://practiceexcelonline.com/practice","position":2},{"@type":"ListItem","name":"Data validation formula practice: drop-down sources and custom rules","position":3}]},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"}]}
```

## Sitemap

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