In this lesson
Create a dropdown and whole-number limit, test invalid entries and audit the stored rows.
Example
A small form that also catches pasted errors
The entry form accepts Region and Quantity. Regions are East, North and West; quantity must be a whole number from 1 to 20. A colleague sometimes pastes an old extract, so add a RowCheck formula as well as input validation.
How it works
Data Validation helps with entry, but pasted or existing data still needs checking. An alert during typing does not tell you whether every stored row is valid.
Keep the approved options in a reference range. That makes the dropdown easier to maintain and gives users a readable list instead of relying on color to explain the rules.
Steps
On Entry, enter Region and Quantity in A1:B1. Type ApprovedRegions in H1 and East, North and West in H2:H4.
Select A2:A10 and choose Data > Data Validation. Set Allow to List and Source to =$H$2:$H$4. Enable the in-cell dropdown if offered and add an input message asking users to choose a region.
Select B2:B10. Set validation to Whole number between 1 and 20 and choose a Stop error alert where available.
Enter West and 5 in A2:B2. Then try South and 0, read the alerts and cancel those invalid entries.
Type RowCheck in C1 and enter the formula below in C2. Fill it down for populated rows. IFERROR makes text such as abc return FALSE: AND does not stop INT from evaluating that invalid text.
=IFERROR(AND(COUNTIF($H$2:$H$4,A2)=1,ISNUMBER(B2),B2=INT(B2),B2>=1,B2<=20),FALSE)English function names. Formula settings →
On a practice copy, paste West with abc and check that RowCheck shows FALSE even if the paste bypasses validation. Test North 3.5, West 0 and West 21 as well. Restore valid rows when you finish.
Try it yourself
- Enter East with 12 and North with 3 as two more records. Test North with 3.5 and explain why a numeric value can still fail a whole-number requirement.
- Add a new permitted region South to the reference list and deliberately extend the validation and audit ranges together. Document the change instead of only altering the displayed dropdown.
Show expected result
- West 5, East 12 and North 3 pass the original audit. North 3.5 fails because it is not an integer, even though it is numeric and within the numerical bounds.
- South is rejected under the original three-region rule; it becomes valid only after both the approved range and checking formulas are updated consistently.
- West with abc returns FALSE rather than #VALUE!. The error wrapper makes the audit usable for nonnumeric pasted input; the other conditions still reject fractional, out-of-range and unapproved values.
If something goes wrong
- A pasted row contains South despite the dropdown rule.
- Read RowCheck and correct the stored region. Check the validation rule as well: some paste operations can bypass or replace it.
- A new approved region is missing from the dropdown.
- Check the dropdown source range. Extend both that range and the COUNTIF range in RowCheck so entry and audit use the same list.
Quick check
Try these questions after the exercise. Check the result in your app before marking the lesson complete.
Choose an answer for all 3 questions to check your understanding.
Optional: try it with Copilot
If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.
Review this region dropdown and quantity rule. Give me tests for typing, pasting abc, decimal quantities and values outside 1–20.
More from Microsoft
These Microsoft guides cover the tools used in this lesson.
Loading your progress…