Data validation in Excel: how to add, use and remove

This tutorial shows you how to use Excel Data Validation to ensure accurate and consistent data entry. Learn to create rules for numbers, dates, text, and lists, and manage validation settings across your spreadsheet.

Controlling data input is crucial for maintaining data integrity. Data Validation lets you specify allowed data types, ranges, lengths, and lists, preventing errors and inconsistencies. This functionality is available across all versions of Microsoft Excel (365, 2021, 2019, 2016, 2013, 2010, and earlier).

- Understanding Excel Data Validation

• Adding Data Validation
• Data Validation Examples:
  • Numbers (Whole and Decimal)
  • Dates and Times
  • Text Length Restrictions
  • Drop-down Lists
  • Custom Rules
• Editing Validation Rules
• Copying Validation Settings
• Locating Data Validation
• Removing Data Validation
• Tips and Tricks
• Troubleshooting Data Validation Issues

What is Excel Data Validation?

Excel Data Validation restricts user input, enforcing rules on what data can be entered into specific cells. It allows for:

• Numeric or text-only entries.
• Number ranges.
• Specific text lengths.
• Date/time ranges.
• Drop-down lists.
• Validation based on other cells.
• Input messages.
• Error alerts.
• Identifying invalid entries.

An example: A rule limiting input to 4-digit numbers (1000-9999) will display an error if a user enters an invalid value.

How to Implement Data Validation

1. Access the Data Validation Dialog: Select cells, go to the Data tab > Data Tools group, and click Data Validation. (Shortcut: Alt > D > L).

2. Create a Validation Rule: In the Settings tab, define criteria using values, cell references, or formulas. For instance, to allow only whole numbers between 1000 and 9999:

Click OK or proceed to add input messages and error alerts.

3. Input Message (Optional): On the Input Message tab, create a message displayed when a cell is selected.

4. Error Alert (Optional): On the Error Alert tab, choose an alert type (Stop, Warning, Information) and customize the message.

Data Validation Examples

The Settings tab lets you choose predefined settings or custom criteria.

• Numbers: Specify whole numbers, decimals, ranges, etc.
• Dates/Times: Define acceptable date and time ranges.
• Text Length: Limit the number of characters allowed.
• Lists (Drop-downs): Create lists of acceptable values.
• Custom Rules: Use formulas for complex validation logic.

Editing, Copying, and Removing Validation

• Editing: Select validated cells, open the Data Validation dialog, make changes, and optionally apply changes to all cells with the same settings.
• Copying: Copy a validated cell, then use Paste Special > Validation to apply the rule to other cells.
• Removing: Select cells, open the Data Validation dialog, and click Clear All.

Tips and Troubleshooting

• Cell References: Use cell references for dynamic validation criteria.
• Formulas: Utilize formulas for complex conditions (e.g., MIN, MAX).
• Finding Invalid Data: Use Data Validation > Circle Invalid Data to highlight invalid entries.
• Protected Workbooks: Data validation works in protected workbooks, but you can't modify the rules.
• Troubleshooting: Check for formula errors, empty cell references, and manual calculation settings.

This comprehensive guide empowers you to effectively utilize Excel Data Validation, improving data quality and consistency in your spreadsheets.

The above is the detailed content of Data validation in Excel: how to add, use and remove. For more information, please follow other related articles on the PHP Chinese website!