Chapter 12: Data Validation

Introduction to Data Validation
Data validation in Excel helps ensure that the data entered into a cell meets specific criteria, reducing the chance of errors and maintaining data integrity. This chapter will cover the basics of data validation, focusing on commonly used settings to control data entry, including input messages, error alerts, and identifying invalid data.

Basic Data Validation Options

1. Allow Lists
Purpose: Create a drop-down list of predefined options to choose from, making data entry easier and more consistent.

How to Apply a Drop-Down List:

  1. Select Your Cell(s): Highlight the cells where you want to apply the drop-down list.
  2. Open Data Validation: Go to the Data tab on the Ribbon and click Data Validation in the Data Tools group.
  3. Set Up the List:
    • In the Data Validation dialog box, go to the Settings tab.
    • In the Allow box, select List.
    • In the Source box, enter the list items separated by commas (e.g., Apple, Orange, Banana), or select a range of cells containing the list items.
    • Click OK.

Example:

  • Scenario: Create a drop-down list for selecting product categories in a cell.
  • Steps: Enter the categories into a column on a different sheet or within the same sheet, then use the data validation list to refer to this range.

2. Allow Whole Numbers
Purpose: Restrict data entry to whole numbers within a specified range.

How to Set Up Whole Number Validation:

  1. Select Your Cell(s): Highlight the cells where you want to restrict data to whole numbers.
  2. Open Data Validation: Go to the Data tab and click Data Validation.
  3. Set Up Number Validation:
    • In the Data Validation dialog box, go to the Settings tab.
    • In the Allow box, select Whole Number.
    • Choose the type of validation (e.g., between, greater than, less than) and enter the required values.
    • Click OK.

Example:

  • Scenario: Ensure that only ages between 18 and 65 are entered in a cell.
  • Steps: Set the validation criteria to allow whole numbers between 18 and 65.

3. Allow Decimal Numbers
Purpose: Restrict data entry to decimal numbers within a specified range.

How to Set Up Decimal Validation:

  1. Select Your Cell(s): Highlight the cells where you want to restrict data to decimal numbers.
  2. Open Data Validation: Go to the Data tab and click Data Validation.
  3. Set Up Decimal Validation:
    • In the Data Validation dialog box, go to the Settings tab.
    • In the Allow box, select Decimal.
    • Choose the type of validation and enter the range of acceptable values.
    • Click OK.

Example:

  • Scenario: Ensure that only numbers with up to two decimal places can be entered.
  • Steps: Set the validation criteria to allow decimals with a specific range if needed.

4. Allow Dates
Purpose: Restrict data entry to dates within a specific range.

How to Set Up Date Validation:

  1. Select Your Cell(s): Highlight the cells where you want to restrict data to dates.
  2. Open Data Validation: Go to the Data tab and click Data Validation.
  3. Set Up Date Validation:
    • In the Data Validation dialog box, go to the Settings tab.
    • In the Allow box, select Date.
    • Choose the type of validation and enter the start and end dates if required.
    • Click OK.

Example:

  • Scenario: Ensure that only dates in the year 2024 are entered in a cell.
  • Steps: Set the validation criteria to allow dates between January 1, 2024, and December 31, 2024.

5. Allow Times
Purpose: Restrict data entry to times within a specific range.

How to Set Up Time Validation:

  1. Select Your Cell(s): Highlight the cells where you want to restrict data to times.
  2. Open Data Validation: Go to the Data tab and click Data Validation.
  3. Set Up Time Validation:
    • In the Data Validation dialog box, go to the Settings tab.
    • In the Allow box, select Time.
    • Choose the type of validation and enter the start and end times if needed.
    • Click OK.

Example:

  • Scenario: Ensure that only times between 9:00 AM and 5:00 PM can be entered.
  • Steps: Set the validation criteria to allow times within the specified range.

Input Message and Error Alert

1. Input Message
Purpose: Provide a helpful message to users when they select a cell, guiding them on what data should be entered.

How to Set Up an Input Message:

  1. Select Your Cell(s): Highlight the cells where you want to add an input message.
  2. Open Data Validation: Go to the Data tab and click Data Validation.
  3. Add Input Message:
    • In the Data Validation dialog box, go to the Input Message tab.
    • Check the box labeled Show input message when cell is selected.
    • Enter a title and input message. The title will appear in bold above the message.
    • Click OK.

Example:

  • Scenario: Guide users to enter a date within the year 2024.
  • Steps: Set the title as “Date Entry” and the input message as “Please enter a date within the year 2024.”

2. Error Alert
Purpose: Display an error message if the data entered does not meet the validation criteria.

How to Set Up an Error Alert:

  1. Select Your Cell(s): Highlight the cells where you want to add an error alert.
  2. Open Data Validation: Go to the Data tab and click Data Validation.
  3. Add Error Alert:
    • In the Data Validation dialog box, go to the Error Alert tab.
    • Check the box labeled Show error alert after invalid data is entered.
    • Choose the type of alert: Stop, Warning, or Information.
    • Enter a title and error message. The title will appear in bold above the message.
    • Click OK.

Example:

  • Scenario: Alert users if they enter a number outside the range of 1 to 100.
  • Steps: Set the title as “Invalid Entry” and the error message as “Please enter a number between 1 and 100.”

3. Circle Invalid Data
Purpose: Highlight cells that contain invalid data according to the validation rules, making it easy to identify and correct errors.

How to Use Circle Invalid Data:

  1. Apply Data Validation: Ensure you have set up data validation rules for your cells.
  2. Circle Invalid Data:
    • Go to the Data tab on the Ribbon.
    • Click on Data Validation and select Circle Invalid Data from the dropdown.
    • Excel will highlight cells with data that does not meet the validation criteria.

Example:

  • Scenario: You have a range of cells with date validation set for the year 2024. Use the Circle Invalid Data feature to identify any cells with dates outside this range.

Practice Assignment:

  1. Create a Drop-Down List:
    • Set up a drop-down list for selecting preferred colors (e.g., Red, Green, Blue) in a cell and add an input message guiding users to select a color.
  2. Apply Number Validation:
    • Set up a validation rule to ensure only numbers between 10 and 50 can be entered, and configure an error alert for invalid entries.
  3. Apply Date Validation:
    • Ensure only dates from January 1, 2023, to December 31, 2023, can be entered, with an input message indicating the date range and an error alert for out-of-range dates.
  4. Circle Invalid Data:
    • Use the Circle Invalid Data feature to highlight any cells with data that do not meet the validation rules you’ve set up.

Pro Tip: To quickly add data validation to multiple cells, copy and paste the cells with validation applied. This ensures consistency across your data entry fields. Additionally, use the Circle Invalid Data feature to easily spot and correct errors.

This chapter introduces basic data validation features, including input messages, error alerts, and methods to identify invalid data. At Anjni Computer Education, we emphasize practical Excel skills to enhance your data management and accuracy.

Leave a Reply

Your email address will not be published. Required fields are marked *