On the "Sales Conference" worksheet, configure cells A4:A12 to allow only whole numbers from 1 to 9. Otherwise, display a Stop error with the title "Invalid" and the message "1 to 9".

Excel Data Validation: Restricting Whole Numbers with a Custom Error Overall Goal The task restricts user input in cells A4:A12 so only integers 1–9 are accepted. This is done using Data Validation, Excel's built-in feature for enforcing rules on cell input. The Stop error preven

Manage and format data

Question

On the "Sales Conference" worksheet, configure cells A4:A12 to allow only whole numbers from 1 to 9. Otherwise, display a Stop error with the title "Invalid" and the message "1 to 9".

Explanation

Excel Data Validation: Restricting Whole Numbers with a Custom Error

Overall Goal

The task restricts user input in cells A4:A12 so only integers 1–9 are accepted. This is done using Data Validation, Excel's built-in feature for enforcing rules on cell input. The Stop error prevents invalid data from ever being entered - it's the strictest error style (vs. Warning or Information, which allow the bad value through).

Step-by-Step Walkthrough

1. Select cells A4:A12

You must select the target range before opening Data Validation, because the rule applies to whatever is selected at that moment. Selecting the wrong range means the rule lands in the wrong place.

2. Go to Data tab → Data Validation → Data Validation...

This opens the dialog where all input rules are defined. There's no other way to configure this - formatting tools and formulas cannot enforce input rules the same way.

3. On the Settings tab: set Allow = "Whole number", Data = "between", Minimum = 1, Maximum = 9

Whole number excludes decimals (1.5 would be rejected). If you chose "Decimal," fractions would slip through.
Between with 1 and 9 sets the inclusive range. Using "greater than" or "less than" alone wouldn't bound both ends simultaneously.

4. On the Error Alert tab: set Style = "Stop", Title = "Invalid", Error message = "1 to 9"

Stop style is critical - it blocks the entry entirely and forces the user to re-enter a valid value. Warning and Information styles would allow the bad value anyway.
The Title and Message must match exactly as specified: Invalid and 1 to 9. These are what appear in the dialog box when a rule is violated.
If you skip the Error Alert tab, Excel shows a generic error or none at all - the simulation will mark it wrong.

5. Click OK

Commits the rule. Nothing is saved until you confirm.

What Goes Wrong If Steps Are Skipped

Skipped Step	Consequence
Wrong cell range selected	Rule applied to wrong cells
"Decimal" instead of "Whole number"	Values like 1.5 pass validation
Error Alert tab not configured	Generic error or no error shown
Style set to "Warning" not "Stop"	User can bypass the rule
Title/message text wrong	Simulation marked incorrect

Memory Tip

Think: Select → Validate → Restrict → Alert

"I select my cells, validate the data, restrict to whole numbers 1–9, then set the alert to Stop with the right title and message."

The Error Alert tab is the most commonly forgotten step - remember that without it, the rule is silent or generic.

Topics

#Data Validation#Whole number validation#Error alerts

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice