Data validation rules in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Data Validation: Restrict to Whole Numbers Between 1 and 100

You want to create a data validation rule in Excel that only allows whole numbers between 1 and 100 inclusive in cell A1. Which formula should you use in the data validation custom formula box?

A =AND(ISNUMBER(A1), A1>=1, A1<=100)

B =OR(A1>=1, A1<=100, MOD(A1,1)=0)

C =AND(ISNUMBER(A1), A1>=1, A1<=100, MOD(A1,1)=0)

D =AND(A1>1, A1<100, ISNUMBER(A1))

💡 Hint

Remember to check that the number is whole and within the range.

The correct formula checks that the value is a number, is between 1 and 100 inclusive, and is a whole number using MOD(A1,1)=0.

Option C excludes 1 and 100 and does not check for whole numbers.

Option C uses OR which allows invalid values.

Option C does not check if the number is whole.

❓ Function Choice

intermediate

Choose the Correct Data Validation Formula for Date Range

You want to restrict input in cell B2 to dates between January 1, 2023 and December 31, 2023. Which formula correctly enforces this in data validation?

A =AND(ISDATE(B2), B2>=DATE(2023,1,1), B2<=DATE(2023,12,31))

B =AND(ISNUMBER(B2), B2>=DATE(2023,1,1), B2<=DATE(2023,12,31))

C =OR(B2>=DATE(2023,1,1), B2<=DATE(2023,12,31))

D =AND(B2>=DATE(2023,1,1), B2<=DATE(2023,12,31))

💡 Hint

Excel stores dates as numbers, so check for numbers in the date range.

Excel stores dates as serial numbers, so ISNUMBER is used to confirm the input is a date.

Option B uses ISDATE which is not an Excel function and causes an error.

Option B misses checking if input is a number, so text could pass.

Option B uses OR which allows invalid dates outside the range.

🎯 Scenario

advanced

Prevent Duplicate Entries in a Column Using Data Validation

You want to prevent users from entering duplicate values in column C (from C2 downwards). Which data validation formula should you apply to cell C2 and then copy down to enforce this?

A =COUNTIF($C$2:$C$100, C2)=1

B =COUNTIF(C:C, C2)>1

C =COUNTIF($C$2:$C$100, C2)<1

D =COUNTIF($C$2:$C$100, C2)>0

💡 Hint

Count how many times the current value appears in the range.

Option A ensures the value appears only once in the range, preventing duplicates.

Option A allows duplicates because it checks if count is greater than 1.

Option A allows only values not present, which blocks all entries after the first.

Option A allows duplicates because count is always at least 1 for the current cell.

❓ data_analysis

advanced

Analyze the Effect of This Data Validation Formula

What is the effect of applying this data validation formula to cell D5?

=ISNUMBER(D5)*AND(D5>0, D5<100)

A Allows only numbers between 1 and 99 inclusive

B Allows any number greater than 0 and less than 100

C Causes an error because ISNUMBER returns TRUE/FALSE and AND returns TRUE/FALSE, multiplying them is invalid

D Allows only numbers greater than 0 and less than 100, but excludes non-numbers

💡 Hint

Remember TRUE is treated as 1 and FALSE as 0 in Excel calculations.

In Excel, TRUE is 1 and FALSE is 0, so multiplying ISNUMBER(D5) by AND(D5>0, D5<100) returns 1 only if both are TRUE.

This means only numbers greater than 0 and less than 100 are allowed.

Option D is wrong because it excludes decimals and 0.

Option D is incomplete because it does not exclude non-numbers.

Option D is wrong because multiplication of TRUE/FALSE is valid in Excel.

🧠 Conceptual

expert

Identify the Error in This Data Validation Formula

You apply this formula in data validation for cell E1:

=IF(E1>10, TRUE, FALSE)

What will happen when a user enters 5 in E1?

A The entry 5 will be rejected because the formula returns FALSE

B The formula causes a circular reference error because it refers to the cell being validated

C The entry 5 will be accepted because IF always returns TRUE or FALSE which Excel treats as valid

D The entry 5 will be accepted because data validation ignores IF formulas

💡 Hint

Data validation formulas must return TRUE to accept input.

The formula returns TRUE only if E1>10, otherwise FALSE.

When user enters 5, formula returns FALSE, so input is rejected.

Option A is wrong because FALSE means reject input.

Option A is wrong because referring to the cell itself in data validation formula is allowed and not a circular reference.

Option A is wrong because data validation uses the formula result to accept or reject input.