Instructions and covered skills
Work in a quiet setting and turn off notifications before you begin. Read each scenario carefully, including cell references, date boundaries, and error-message behavior. Select the response that best matches how Excel Data Validation operates. Do not rely on assumptions about formatting, since validation and formatting are separate features. Stay focused on the stated worksheet rule rather than unrelated spreadsheet functions. Review your selections for wording such as blank handling, relative references, and list-source maintenance.
Key Areas
This test focuses on Excel Data Validation as a control for accurate, standardized worksheet entry. Candidates work with validation types including List, Whole Number, Decimal, Date, Time, Text Length, and Custom formulas. They should understand how to define minimum and maximum boundaries, select comparison operators, and apply rules across intended ranges without unintentionally changing the logic for each row.
The assessment also covers controlled dropdown lists and their source ranges. Candidates should recognize when a direct list, named range, table-based reference, or worksheet range is appropriate, and how source changes affect the dropdown. Practical knowledge includes handling blank cells, identifying duplicate values, setting conditional requirements, and using relative and absolute references correctly in custom validation formulas.
Error handling is another key area. Candidates should know the distinction among Stop, Warning, and Information alerts, along with the purpose of input messages. They should be able to diagnose common issues such as validation being copied incorrectly, lists not expanding, invalid values already existing in a range, and rules being bypassed through paste operations.
Recommended Preparation
Prepare by creating a small workbook with fields such as status, region, transaction date, quantity, invoice number, and contact email. Practice creating dropdowns from worksheet ranges and named ranges, then add and remove source values to observe how each method behaves. Build numeric and date rules with clearly defined boundaries, and test valid, invalid, blank, and pasted entries.
Practice custom formulas that enforce uniqueness, require a value when another field is populated, and validate text patterns. Apply each formula to multiple rows and inspect how relative references shift. Configure input messages and each error-alert style, then enter disallowed values to compare the resulting behavior. Finally, use Excel's Circle Invalid Data command and Data Validation dialog to inspect, revise, and remove existing rules.