Practical 13 – Mastering Data Validation

1. Objective

The primary objective of this practical is to enable students to implement robust data validation rules in Excel worksheets. Students will learn to:

·         Define data input criteria for specific cells or ranges.

·         Create dropdown lists using data validation to standardize inputs.

·         Restrict data entry to specific number ranges, dates, or text lengths.

·         Provide informative input messages to guide users.

·         Customize error alerts to prevent invalid data entry effectively.

·         Utilize "Circle Invalid Data" to quickly identify cells that violate rules.

·         Enhance data accuracy and consistency within spreadsheets.

2. Introduction

Data Validation is a powerful Excel feature that allows you to control what can or cannot be entered into a cell. It acts as a gatekeeper, ensuring that data meets predefined rules, thereby maintaining the accuracy and consistency of your spreadsheet. Without data validation, users might enter incorrect numbers, misspelled text, or invalid dates, leading to errors in calculations and analysis. This practical will guide you through setting up various validation rules, making your Excel sheets more robust, user-friendly, and less prone to errors. We will build upon the marksheet from Practical 3, applying data validation to its input fields.

3. Pre-requisites

·         Successful completion of Practical 3.

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic data entry and range selection.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

Ensure you have your completed marksheet from Practical 3 open. We will use this marksheet to apply data validation rules.

Step 5.1: Opening the Marksheet and Preparing for Validation

1.     Open the Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Add a New Row: Insert a new row (e.g., between Alice and Bob’s data) to practice data entry with validation. Right-click on row 5 header, select Insert.

3.     Add a New Student Name: In cell A5, type Frank. Leave the subject marks, total, percentage, grade, and result columns blank for now, as we will enter data there manually.

Step 5.2: Data Validation for Whole Numbers

We want to ensure that subject marks are always between 0 and 100.

1.     Select Target Cells: Select the range B4:F8 (all subject marks cells for all students, including the new row for Frank).

2.     Access Data Validation: Go to the Data tab on the Excel ribbon.

3.     Open Data Validation Dialog: In the Data Tools group, click Data Validation.

4.     Settings Tab:

o    Under Validation criteria, in the Allow dropdown, select Whole number.

o    In the Data dropdown, select between.

o    In the Minimum box, type 0.

o    In the Maximum box, type 100.

5.     Input Message Tab:

o    Check Show input message when cell is selected.

o    In the Title box, type Enter Marks.

o    In the Input message box, type Please enter a whole number between 0 and 100.

6.     Error Alert Tab:

o    Ensure Show error alert after invalid data is entered is checked.

o    In the Style dropdown, select Stop (this prevents invalid data entry).

o    In the Title box, type Invalid Mark Entered.

o    In the Error message box, type Marks must be between 0 and 100. Please try again.

7.     Click OK.

8.     Test the Validation:

o    Click on cell B5 (Frank’s Subject 1 mark). Observe the input message.

o    Try typing 105 and press Enter. An error alert should pop up. Click Cancel.

o    Try typing -5 and press Enter. An error alert should pop up. Click Cancel.

o    Type 80 and press Enter. This should be accepted.

Step 5.3: Data Validation for a List

We want to restrict the Result column to only "Pass" or "Fail" using a dropdown list.

1.     Prepare List Source: In an empty area of your sheet (e.g., M1), type Pass. In M2, type Fail. You can hide these cells later.

2.     Select Target Cells: Select the range J4:J8 (the Result column for all students).

3.     Access Data Validation: Go to the Data tab > Data Tools group > Data Validation.

4.     Settings Tab:

o    Under Validation criteria, in the Allow dropdown, select List.

o    In the Source box, type =$M$1:$M$2 (if you prepared the list in M1:M2). Alternatively, you can directly type Pass,Fail (separated by commas) if your list is short and won’t change.

5.     Input Message Tab:

o    Title: Select Result

o    Input message: Choose Pass or Fail from the dropdown list.

6.     Error Alert Tab:

o    Style: Stop

o    Title: Invalid Result

o    Error message: Please select either "Pass" or "Fail" from the provided list.

7.     Click OK.

8.     Test the Validation:

o    Click on cell J5. Observe the dropdown arrow.

o    Click the arrow and select Pass.

o    Try typing Maybe and press Enter. An error alert should pop up. Click Cancel.

Step 5.4: Data Validation for Dates

Let’s assume you want to record the "Date of Entry" for students, and it must be within the current academic year.

1.     Add a New Column: In cell K3, type Date of Entry.

2.     Select Target Cells: Select the range K4:K8.

3.     Access Data Validation: Go to the Data tab > Data Tools group > Data Validation.

4.     Settings Tab:

o    Under Validation criteria, in the Allow dropdown, select Date.

o    In the Data dropdown, select between.

o    In the Start date box, type 01/08/2025 (assuming a hypothetical academic year start).

o    In the End date box, type 31/07/2026 (hypothetical academic year end).

5.     Input Message Tab:

o    Title: Enter Date

o    Input message: Enter the date of entry within the 2025-2026 academic year.

6.     Error Alert Tab:

o    Style: Stop

o    Title: Incorrect Date

o    Error message: Date must be between 01/08/2025 and 31/07/2026.

7.     Click OK.

8.     Test the Validation:

o    Click on K5. Observe the input message.

o    Type 15/09/2025 and press Enter. This should be accepted.

o    Type 01/01/2025 and press Enter. An error alert should appear.

Step 5.5: Data Validation for Text Length

Ensure student names are between 3 and 20 characters long.

1.     Select Target Cells: Select the range A4:A8.

2.     Access Data Validation: Go to the Data tab > Data Tools group > Data Validation.

3.     Settings Tab:

o    Under Validation criteria, in the Allow dropdown, select Text length.

o    In the Data dropdown, select between.

o    In the Minimum box, type 3.

o    In the Maximum box, type 20.

4.     Error Alert Tab:

o    Title: Name Length Error

o    Error message: Student Name must be between 3 and 20 characters.

5.     Click OK.

6.     Test the Validation:

o    In A5, try typing Fr (2 characters) and press Enter. An error should appear.

o    Type Frankenstein Van Winkle (more than 20 characters) and press Enter. An error should appear.

o    Type Frank and press Enter. This should be accepted.

Step 5.6: Data Validation with a Custom Formula

We want to ensure that the Total Marks (column G) is always the SUM of Subject 1 to Subject 5. While formulas usually ensure this, data validation can serve as an extra check if manual overrides are possible or if there’s external data input.

1.     Select Target Cells: Select the range G4:G8.

2.     Access Data Validation: Go to the Data tab > Data Tools group > Data Validation.

3.     Settings Tab:

o    Under Validation criteria, in the Allow dropdown, select Custom.

o    In the Formula box, type: =G4=SUM(B4:F4)

§  Explanation: This formula is applied to each cell in the selected range. For cell G4, it checks if G4‘s value equals the sum of B4:F4. When applied to G5, it will automatically adjust to check G5=SUM(B5:F5), and so on, due to relative referencing.

4.     Error Alert Tab:

o    Title: Total Mismatch

o    Error message: Total Marks do not match the sum of subject scores.

5.     Click OK.

6.     Test the Validation:

o    Ensure the formula in G4 is =SUM(B4:F4). It should be valid.

o    Temporarily delete the formula in G5 and type 100 (assuming Frank’s actual sum is different). An error should pop up.

Step 5.7: Circling Invalid Data

After setting up validation rules, you can quickly find any existing data that doesn’t conform to your rules.

1.     Introduce Invalid Data (if needed): If you fixed previous errors, manually enter some invalid data in a validated cell (e.g., 105 in B4).

2.     Circle Invalid Data: Go to the Data tab > Data Tools group > Data Validation dropdown > Circle Invalid Data.

o    Observation: Any cells containing data that violate your rules will be circled in red.

3.     Clear Validation Circles: Go to the Data tab > Data Tools group > Data Validation dropdown > Clear Validation Circles.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet:

1.     Grade Dropdown: Apply data validation to the Grade column (I4:I8) to allow only "A", "B", "C", or an empty string "" (for students who might not qualify for a grade yet). Provide an input message and error alert.

2.     Unique Student Names: Apply a custom data validation rule to the Student Name column (A4:A8) to ensure that each student name entered is unique.

o    (Hint: Use the formula =COUNTIF($A$4:$A$8,A4)=1. This formula checks if the count of the current cell’s value within the entire range is exactly 1, meaning it’s unique.)

3.     Minimum Percentage Threshold: Apply data validation to the Percentage column (H4:H8) to ensure that the displayed percentage is always greater than or equal to 0. Provide a suitable error message.

4.     Combined Subject Limit: Apply a custom data validation rule to cells B4:F8 (subject marks) to ensure that for any given student, the sum of their marks in Subject 1 and Subject 2 never exceeds 180.

o    (Hint: For cells B4:F4, the formula might look like =$B4+$C4<=180. Make sure to use mixed references correctly so it applies to each row.)

5.     Test and Report: Intentionally enter invalid data into some of the cells you’ve validated. Use Circle Invalid Data to identify them. Take a screenshot of your marksheet with invalid data circled.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with the Marksheet sheet, including all data validation rules implemented from the procedure and exercises.

·         Provide a screenshot of your marksheet with invalid data circled (as per Exercise 5).

·         Be prepared to demonstrate how to set up, test, and clear data validation rules, and explain the purpose of input messages and error alerts.

·         Be able to explain the difference between a Stop, Warning, and Information error alert style.