Data Validation in Excel | How to become an Expert of MS Excel - Class 6 PDF Download

Introduction

Data validation is an essential feature in Excel that helps ensure the accuracy and consistency of data entered into spreadsheets. By applying validation rules, you can control what type of data is allowed in a cell, set data limits, and provide helpful error messages to users. In this article, we will explore the concept of data validation, its importance, and how to implement it effectively using simple examples and code snippets.

What is Data Validation?

Data validation is a feature in Excel that allows you to define constraints or rules for the data entered into specific cells. These rules can include restrictions on the data type, value range, text length, or even allow only values from a predefined list. By applying data validation, you can prevent errors, ensure data consistency, and improve the overall quality of your spreadsheets.

Benefits of Data Validation

Data validation offers several advantages, including:

• Ensuring data accuracy and consistency
• Preventing data entry errors and typos
• Streamlining data entry processes
• Providing helpful error messages and guidance to users
• Enhancing data integrity and reliability
• Simplifying data analysis and reporting

Types of Data Validation

Let's explore some common types of data validation with simple examples and code explanations.
(a) Whole Number Validation: Whole number validation restricts the entry of decimal values and allows only integers in a specified range.
Example:

• Select the cell(s) you want to apply validation to.
• Go to the "Data" tab and click on "Data Validation."
• In the "Settings" tab, choose "Whole number" from the "Allow" drop-down menu.
• Specify the minimum and maximum values if necessary.
• Optionally, provide an error message to guide users if the input is invalid.

Code Explanation:
To implement whole number validation using VBA, you can use the following code snippet:

Sub ApplyWholeNumberValidation()

    With Range("A1:A10").Validation

        .Delete

        .Add Type:=xlValidateWholeNumber, _

             AlertStyle:=xlValidAlertStop, _

             Operator:=xlBetween, _

             Formula1:="1", _

             Formula2:="100"

        .ErrorMessage = "Please enter a whole number between 1 and 100."

        .ErrorTitle = "Invalid Entry"

    End With

End Sub

(b) Decimal Validation: Decimal validation allows you to specify a range for decimal numbers, ensuring that only valid values are entered.
Example:

• Select the cell(s) you want to apply validation to.
• Open "Data Validation" from the "Data" tab.
• Choose "Decimal" from the "Allow" drop-down menu.
• Set the minimum and maximum values as required.
• Optionally, provide an error message for invalid entries.

Code Explanation:
To apply decimal validation using VBA, you can use the following code snippet:

Sub ApplyDecimalValidation()

    With Range("A1:A10").Validation

        .Delete

        .Add Type:=xlValidateDecimal, _

             AlertStyle:=xlValidAlertStop, _

             Operator:=xlBetween, _

             Formula1:="0", _

             Formula2:="100"

        .ErrorMessage = "Please enter a decimal value between 0 and 100."

        .ErrorTitle = "Invalid Entry"

    End With

End Sub

(c) Text Length Validation: Text length validation allows you to limit the number of characters that can be entered in a cell.
Example:

• Select the cell(s) you want to apply validation to.
• Go to the "Data" tab, click on "Data Validation."
• Choose "Text length" from the "Allow" drop-down menu.
• Specify the minimum and maximum character counts.
• Optionally, provide an error message for invalid entries.

Code Explanation:
To implement text length validation using VBA, use the following code snippet:

Sub ApplyTextLengthValidation()

    With Range("A1:A10").Validation

        .Delete

        .Add Type:=xlValidateTextLength, _

             AlertStyle:=xlValidAlertStop, _

             Operator:=xlBetween, _

             Formula1:="1", _

             Formula2:="10"

        .ErrorMessage = "Please enter a text with 1 to 10 characters."

        .ErrorTitle = "Invalid Entry"

    End With

End Sub

d) List Validation: List validation restricts cell entries to a predefined list of values, providing a dropdown menu for selection.
Example:

• Create a list of valid values in a separate range.
• Select the cell(s) you want to apply validation to.
• Go to the "Data" tab, click on "Data Validation."
• Choose "List" from the "Allow" drop-down menu.
• In the "Source" field, specify the range containing the valid values.
• Optionally, provide an error message for invalid entries.

Code Explanation:
To apply list validation using VBA, you can use the following code snippet:

Sub ApplyListValidation()

    Dim validValues As Range

    Set validValues = Range("A1:A5") ' Range containing valid values

    

    With Range("B1:B10").Validation

        .Delete

        .Add Type:=xlValidateList, _

             AlertStyle:=xlValidAlertStop, _

             Formula1:="=" & validValues.Address

        .ErrorMessage = "Please select a value from the list."

        .ErrorTitle = "Invalid Entry"

    End With

End Sub

(e) Date Validation: Date validation allows you to set specific date restrictions, such as a minimum or maximum date.
Example:

• Select the cell(s) you want to apply validation to.
• Open "Data Validation" from the "Data" tab.
• Choose "Date" from the "Allow" drop-down menu.
• Specify the minimum and/or maximum date as required.
• Optionally, provide an error message for invalid dates.

Code Explanation:
To implement date validation using VBA, you can use the following code snippet:

Sub ApplyDateValidation()

    With Range("A1:A10").Validation

        .Delete

        .Add Type:=xlValidateDate, _

             AlertStyle:=xlValidAlertStop, _

             Operator:=xlBetween, _

             Formula1:="01/01/2023", _

             Formula2:="12/31/2023"

        .ErrorMessage = "Please enter a date between 01/01/2023 and 12/31/2023."

        .ErrorTitle = "Invalid Entry"

    End With

End Sub

Implementing Data Validation in Excel

To implement data validation in Excel:

• Select the cell(s) you want to apply validation to.
• Go to the "Data" tab and click on "Data Validation."
• Choose the desired validation type from the "Allow" drop-down menu.
• Set the specific validation settings (e.g., range, list, text length, etc.).
• Customize error messages and titles if needed.
• Click "OK" to apply the validation.

Sample Problems and Solutions

Problem 1: You want to restrict the entry of scores in a range from 0 to 100.

• Select the cell(s) for score entry.
• Apply whole number validation with a range from 0 to 100.
• Provide an error message for scores outside the specified range.

Problem 2: You have a dropdown list of countries, and you want to allow users to select only from that list.

• Create a list of valid countries in a range.
• Apply list validation to the cell(s) where the country selection will be made.
• Specify the range containing the valid countries as the data source.

Conclusion

Data validation is a powerful feature in Excel that allows you to control and validate data entered into spreadsheets. By using various validation types, you can ensure data accuracy, prevent errors, and streamline data entry processes. Remember to choose the appropriate validation type and customize error messages to guide users effectively. Start implementing data validation in your Excel spreadsheets and enjoy the benefits of accurate and consistent data.