How to Use Data Validation in Excel 2021

Edited 1 week ago by ExtremeHow Editorial Team

Microsoft OfficeExcelData ValidationInput ControlSpreadsheetWindowsMacProductivityProfessional

Introduction to data validation

Data validation in Excel is a powerful tool that helps ensure the accuracy and quality of data entered into your worksheet. By setting rules and restrictions, users can limit the type or range of data that can be entered into a cell. This ensures the consistency and correctness of data, which is critical for generating reliable analytical results. This guide will take you through the steps to use data validation in Excel 2021, including setting up text, number, date, and custom validation, as well as providing meaningful feedback messages to guide data entry.

Why use data validation?

Data validation ensures that only the correct type of data is entered, thereby reducing errors and maintaining the integrity of the data. Here are some reasons to use data validation:

• Prevents incorrect data entry by restricting cell input.
• Reduces the risk of calculation errors in formulas involving data.
• Simplifies data entry for users, making Sheets more user-friendly.
• Implements business rules that streamline data processing and analysis.

Access to data validation

To start using Data Validation, open Excel 2021 and follow these steps:

1. Select the cells where you want to apply the validation. You can also select an entire column or row.
2. Go to the top menu and click on the “Data” tab.
3. In the "Data Tools" group, click "Data Validation." A dialog box will appear providing options for customizing your settings.

Types of data validation

Excel 2021 lets you apply different types of data validation depending on your needs. Below are the main types:

1. Whole number verification

This type limits users to entering only whole numbers. You can set minimum and maximum values to further refine the range.

Example: Only allow numbers between 1 to 10.

1. Select the cells you want to apply the validation to.
2. Open the Data Validation dialog box.
3. In the "Allow" drop-down menu, select "Integer."
4. Set "Data" and enter a minimum and maximum value (for example, between 1 and 10).
5. Click OK.

2. Decimal validation

This option is for validating decimal values. Like whole numbers, you can specify a range for acceptable entries.

Example: Allow decimal values between 0.50 and 2.50.

1. Select the cells you want.
2. Open the Data Validation dialog.
3. Select "Decimal" from the "Allow" drop-down menu.
4. Set the range (for example, between 0.50 and 2.50) using the "Data" drop-down.
5. Apply by clicking “OK”.

3. List verification

Use list validation to restrict entries based on a predefined list. This is useful for items like state names, job titles, or any predefined list of options.

Example: Allow selection only from a list of departments: HR, IT, Sales.

1. Click on the cells for verification.
2. Open the Data Validation dialog and select "List" from the "Allow" drop-down.
3. In the "Source" box, type comma-separated list items (for example, HR, IT, Sales) or reference the range of cells where these items are listed.
4. Press OK.

4. Date verification

Restrict date entries to a particular range or to a specific list of acceptable dates.

Example: Allow dates after January 1, 2021.

1. Select the appropriate cells.
2. Access the Data Validation settings and select “Date” from the “Allow” menu.
3. Set criteria such as "greater than" and input the date (for example, 01/01/2021).
4. Finalize by clicking “OK.”

5. Time verification

Similar to date validation, this ensures that only valid time entries are made.

Example: Allow time entries only between 09:00 AM and 05:00 PM.

1. Select the range of cells to verify.
2. Open the Data Validation dialog, select "Time" from the "Allow" options.
3. Specify the time range (for example, between 09:00 and 17:00).
4. Click OK.

6. Verification of text length

Enforce restrictions on the length of text entered into cells, ideal for fields such as ID or license numbers.

Example: Limit input to 6 characters only.

1. Select the target cells and open Data Validation.
2. Select "Text Length" from the "Allow" menu.
3. Define the condition (for example, "equals" 6).
4. Apply by clicking “OK”.

7. Custom validation

For more complex requirements, use custom validation with formulas to specify conditions that must be met for data entry.

Example: Allow only even numbers.

1. Select the cells to verify.
2. Open Data Validation, select "Custom" from "Allow".
3. Enter a formula that reflects your condition, such as =MOD(A1,2)=0 (for cell A1).
4. Click "OK" to finish.

Input message for data guidance

Excel allows you to set a message that appears when the user selects a cell. This is useful for providing instructions or details about data entry.

1. In the Data Validation dialog box, switch to the "Input Message" tab.
2. Check "Show input message when cell is selected".
3. Optional: Enter a title and detailed input message.
4. Click "OK" to save.

Error warning message

When the entered data does not meet the validation criteria, an error alert appears. Set different alert styles to indicate the severity - Stop, Warning, or Information.

1. Open the Data Validation dialog box and switch to the "Error Alert" tab.
2. Check "Show error warning after entering invalid data".
3. Select the type of alert: Prevent (prevents access), Warning (warns but allows access), or Information (informs about the error but does not prevent access).
4. Input the title and message for the alert.
5. Click OK.

Managing data validation

Sometimes, you may need to change or delete validation rules. Follow these steps to manage your validation rules:

1. To review or modify the validation, select the relevant cells and reopen the Data Validation dialog.
2. Make the necessary adjustments and click "OK" to apply the new rules.
3. To remove validation, open the Data Validation dialog and click "Clear All".
4. Click "OK" to confirm the deletion of validation rules.

Things to consider when using data validation

• Data validation can greatly improve the quality of your data, but it is not applied retroactively. Be sure to apply validation rules before data entry begins.
• Be careful when copying and pasting data into validated cells because it may override your existing rules. Excel does not notify when validation is bypassed in this way.
• If you use dependent drop-down lists, make sure that cell references and list items are accurately linked to prevent incorrect data entry.
• Be sure to save your workbook after setting up complex validation rules to avoid data loss.

Conclusion

Data validation in Excel 2021 is an essential tool that helps maintain the accuracy and consistency of your datasets. From setting basic rules like numeric ranges to complex custom formulas, understanding and using data validation strengthens the foundation of your data management process. By implementing validation, you can prevent errors, guide users, and ensure that your data remains reliable for all types of business and analytical needs.

  Share This Article

If you find anything wrong with the article content, you can request an update