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.
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.
- 1A drop-down source without repeatsThe 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.
- 2A list that depends on the first choicePremiumThe 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.
- 3Weekdays onlyPremiumDeliveries 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.
- 4Block duplicatesPremiumEach 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 $.
- 5Codes in capitals onlyPremiumProduct 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.
Functions in this topic
Read the guide
Questions
How do I make a drop-down list that depends on another cell?
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.
How do I stop duplicate entries in Excel?
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.
How do I only allow capital letters in Excel data validation?
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.