Advanced Excel Lab Assignment 15
Practical Scenario Questions on Data Validation
1. Create a data validation rule so that students can enter only whole numbers between 1
and 100 in a "Marks" column.
2. In the "Age" column, allow only values between 18 and 60.
3. Restrict a "Date of Birth" field so that the date entered must be before today’s date.
4. Set up validation for an "Exam Date" column so that the date entered must be greater
than or equal to today.
5. Create a drop-down list for the "Department" column with options: CSE, IT, ECE, ME,
CE.
6. In the "Grade" column, allow only entries from the list: A, B, C, D, F.
7. Restrict the "Phone Number" column so that only 10-digit numbers are allowed.
8. For the "Email ID" column, ensure the value must contain ‘@’ symbol using a custom
formula.
9. Restrict a "Percentage" column so that values must be between 0% and 100%.
10. In the "Fees Paid" column, allow only values that are multiples of 500.
11. Create validation in the "Semester" column to accept only odd numbers (1, 3, 5, 7).
12. In a "Joining Year" column, restrict values to 2020, 2021, 2022, 2023, 2024 only.
13. Create a custom validation rule so that "Username" should be at least 5 characters long.
14. In the "Attendance %" column, restrict entries to numeric values greater than 75%.
15. In the "PIN Code" column, allow only exactly 6 digits.
16. Create a drop-down list for "Yes/No" responses in a "Hostel Facility Required" column.
17. In the "Roll Number" column, restrict entries to unique values (hint: use custom formula
with COUNTIF).
18. For the "Exam Time" column, restrict entries between 9:00 AM and 5:00 PM.
19. In the "Project Score" column, allow only values divisible by 5.
20. Add a custom error message that displays “Enter Marks between 0 and 100 only!” when
invalid data is entered in the "Marks" column.
Solutions for Data Validation Practical Scenarios in Excel
1. Whole numbers between 1 and 100
Select the "Marks" column.
Go to Data → Data Validation.
Under Allow, choose Whole Number.
Set Minimum = 1, Maximum = 100.
2. Age between 18 and 60
Select the "Age" column.
Data → Data Validation → Whole Number.
Minimum = 18, Maximum = 60.
3. Date of Birth before today’s date
Select "Date of Birth" column.
Data → Data Validation → Date.
Under Data, choose less than.
Enter =TODAY().
4. Exam Date greater than or equal to today
Select "Exam Date" column.
Data Validation → Date → greater than or equal to.
Enter =TODAY().
5. Drop-down list for Department (CSE, IT, ECE, ME, CE)
Select "Department" column.
Data Validation → List.
Source = CSE, IT, ECE, ME, CE.
6. Grade (A, B, C, D, F)
Select "Grade" column.
Data Validation → List.
Source = A, B, C, D, F.
7. Phone Number (10 digits only)
Select "Phone Number" column.
Data Validation → Custom.
Formula: =AND(ISNUMBER(A1),LEN(A1)=10).
(Replace A1 with first cell in selection).
8. Email must contain @
Select "Email ID" column.
Data Validation → Custom.
Formula: =ISNUMBER(SEARCH("@",A1)).
9. Percentage between 0% and 100%
Select "Percentage" column.
Data Validation → Decimal.
Minimum = 0, Maximum = 1.
Format cells as Percentage.
10. Fees Paid must be multiples of 500
Select "Fees Paid" column.
Data Validation → Custom.
Formula: =MOD(A1,500)=0.
11. Semester only odd numbers (1, 3, 5, 7)
Data Validation → List.
Source = 1,3,5,7.
12. Joining Year (2020–2024 only)
Data Validation → List.
Source = 2020,2021,2022,2023,2024.
13. Username must be at least 5 characters
Data Validation → Custom.
Formula: =LEN(A1)>=5.
14. Attendance % greater than 75
Data Validation → Decimal.
Minimum = 0.76, Maximum = 1.
Format as Percentage.
15. PIN Code = 6 digits
Data Validation → Custom.
Formula: =AND(ISNUMBER(A1),LEN(A1)=6).
16. Hostel Facility Required (Yes/No)
Data Validation → List.
Source = Yes,No.
17. Roll Number must be unique
Select "Roll Number" column.
Data Validation → Custom.
Formula: =COUNTIF($A$1:$A$100,A1)=1.
(Adjust range as needed).
18. Exam Time between 9:00 AM and 5:00 PM
Data Validation → Time.
Start Time = 09:00 AM, End Time = 05:00 PM.
19. Project Score divisible by 5
Data Validation → Custom.
Formula: =MOD(A1,5)=0.
20. Custom Error Message for Marks
In "Marks" column validation (Step 1).
Go to Error Alert tab.
Title: “Invalid Entry”.
Message: “Enter Marks between 0 and 100 only!”.