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

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. 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.
  2. 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.
  3. 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.
  4. 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 $.
  5. 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.