Data Validation in Excel Workbooks

Data validation in Excel restricts what users can enter in cells. For accessibility, these restricted cells need clear instructions about what input is expected and allowed.

What This Means

When you use data validation (dropdown lists, date ranges, number limits), users need to understand what they can enter. Without clear instructions, users may struggle to complete forms correctly.

Why It Matters

• Validation errors frustrate users when requirements aren't clear
• Screen reader users need verbal instructions, not just visual cues
• Clear instructions reduce errors for all users
• Good guidance improves completion rates

Common Violations

XLSX-06-001: Data Validation Lacks Input Instructions {#XLSX-06-001}

What's Wrong: A cell with data validation doesn't have input messages or instructions explaining what users should enter.

Impact: Moderate - Users don't know what values are acceptable until they get an error.

How to Identify:

• Select cells in your workbook
• Go to Data > Data Validation
• Check the Input Message tab - is it configured?
• Review error messages for clarity

How to Fix:

Adding Input Messages:

1. Select the cell(s) with validation
2. Go to Data > Data Validation
3. Click the Input Message tab
4. Check Show input message when cell is selected
5. Enter a Title (e.g., "Select a Department")
6. Enter an Input message (e.g., "Choose from the dropdown list of available departments")
7. Click OK

Configuring Error Alerts:

1. In Data Validation, click the Error Alert tab
2. Choose a Style (Stop, Warning, or Information)
3. Enter a clear Title (e.g., "Invalid Date")
4. Enter a helpful Error message (e.g., "Please enter a date between Jan 1 and Dec 31, 2024")
5. Click OK

Types of Data Validation

List Validation (Dropdowns)

Good Input Message: "Select your department from the dropdown list. Click the arrow to see options."

Good Error Message: "Please select a value from the list. You cannot enter custom text."

Date Validation

Good Input Message: "Enter a date between January 1 and December 31, 2024. Format: MM/DD/YYYY"

Good Error Message: "The date must be within the year 2024. Please enter a date between 01/01/2024 and 12/31/2024."

Number Validation

Good Input Message: "Enter a whole number between 1 and 100."

Good Error Message: "Please enter a number from 1 to 100. Decimals are not allowed."

Text Length Validation

Good Input Message: "Enter a description up to 200 characters."

Good Error Message: "Your entry exceeds 200 characters. Please shorten your description."

Best Practices for Input Messages

Be Specific

• State exactly what's expected
• Include format requirements (MM/DD/YYYY)
• Mention any restrictions (numbers only, no decimals)

Be Concise

• Keep messages brief but complete
• One to two sentences is usually enough
• Users won't read lengthy instructions

Be Helpful

• Explain why there are restrictions if not obvious
• Provide examples when helpful
• Tell users how to access options (click arrow for dropdown)

Screen Reader Considerations

Screen readers announce input messages when users navigate to validated cells:

1. User tabs to validated cell
2. Screen reader announces cell content
3. Screen reader announces input message
4. User understands what to enter

Without input messages:

1. User tabs to validated cell
2. Screen reader announces only the cell content
3. User doesn't know about restrictions
4. User tries invalid entry
5. Error message appears (frustrating experience)

Creating Accessible Dropdown Lists

Dropdown lists are the most common validation type:

Source Data Best Practices

• List items should be descriptive
• Avoid abbreviations unless widely known
• Order items logically (alphabetical or by importance)
• Keep list length manageable

Dropdown Configuration

1. Create your list in a named range
2. Select the target cell(s)
3. Go to Data > Data Validation
4. Set Allow to "List"
5. Set Source to your named range
6. Add an input message explaining the dropdown
7. Click OK

Error Prevention vs. Error Messages

Prevention (Better):

• Clear input messages that explain requirements
• Visual indicators near validated cells
• Example values showing expected format

Error Handling (Also Important):

• Clear error messages when validation fails
• Instructions on how to fix the error
• Option to retry without losing work

Document Instructions

For complex validation:

1. Add instructions at the top of the sheet
2. Explain what each validated field expects
3. List any special requirements
4. Provide contact for questions

Example Instructions Cell: "This form requires you to select your department from the dropdown in cell B2, enter your employee ID (6 digits) in cell B3, and select a date within this fiscal year in cell B4."

Using Microsoft's Accessibility Checker

Excel's Accessibility Checker doesn't specifically check for data validation instructions, but you can:

1. Review all data validation settings manually
2. Ensure each validated cell has an input message
3. Test the user experience by tabbing through the form

Best Practices

Do:

• Add input messages to all validated cells
• Write clear, specific error messages
• Explain format requirements
• Test with keyboard navigation

Don't:

• Use validation without instructions
• Write vague error messages ("Invalid entry")
• Assume users will understand restrictions
• Forget to test the complete user experience

Additional Resources

• Microsoft: Apply data validation to cells
• W3C WCAG 3.3.2 Labels or Instructions