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.

Practice

1. What is the main purpose of using Data Validation in Excel?

easy

A. To restrict the type of data users can enter in a cell

B. To format cells with colors and fonts

C. To create charts from data

D. To sort data alphabetically

Solution

1. Step 1: Understand Data Validation purpose

   Data Validation is used to control what data can be entered in cells to avoid errors.

2. Step 2: Compare options with purpose

   Only To restrict the type of data users can enter in a cell describes restricting data entry, which matches Data Validation's purpose.

3. Final Answer:

   To restrict the type of data users can enter in a cell -> Option A

4. Quick Check:

   Data Validation = Restrict data entry [OK]

Hint: Data Validation controls input type, not formatting or sorting [OK]

Common Mistakes:

• Confusing Data Validation with cell formatting
• Thinking it creates charts
• Assuming it sorts data

2. Which of the following is the correct way to set a data validation rule that only allows whole numbers between 1 and 10?

easy

A. Allow: List; Source: 1,2,3,4,5,6,7,8,9,10

B. Allow: Whole number; Data: between; Minimum: 1; Maximum: 10

C. Allow: Decimal; Data: greater than; Minimum: 1; Maximum: 10

D. Allow: Text length; Data: less than; Minimum: 1; Maximum: 10

Solution

1. Step 1: Identify rule for whole numbers 1 to 10

   The rule must allow whole numbers only, between 1 and 10 inclusive.

2. Step 2: Match options to rule

   Allow: Whole number; Data: between; Minimum: 1; Maximum: 10 correctly sets Allow to Whole number and Data to between with min 1 and max 10.

3. Final Answer:

   Allow: Whole number; Data: between; Minimum: 1; Maximum: 10 -> Option B

4. Quick Check:

   Whole number between 1 and 10 = Allow: Whole number; Data: between; Minimum: 1; Maximum: 10 [OK]

Hint: Whole numbers need 'Allow: Whole number' with min and max [OK]

Common Mistakes:

• Choosing Decimal instead of Whole number
• Using List instead of number range
• Confusing Text length with number validation

3. If you apply a data validation rule with the formula =ISNUMBER(A1) on cell A1, what will happen when you enter the text "Hello" in A1?

medium

A. The entry will be rejected with an error message

B. The entry will be accepted without error

C. The cell will automatically convert "Hello" to a number

D. The cell will be cleared automatically

Solution

1. Step 1: Understand the formula =ISNUMBER(A1)

   This formula returns TRUE if A1 contains a number, FALSE otherwise.

2. Step 2: Entering "Hello" in A1

   "Hello" is text, so ISNUMBER returns FALSE, violating the validation rule.

3. Final Answer:

   The entry will be rejected with an error message -> Option A

4. Quick Check:

   ISNUMBER rejects text input = The entry will be rejected with an error message [OK]

Hint: ISNUMBER allows only numbers; text causes rejection [OK]

Common Mistakes:

• Thinking text is accepted by ISNUMBER
• Assuming automatic conversion to number
• Believing the cell clears on invalid input

4. You set a data validation rule with the formula =AND(A1>=1, A1<=5) but users can still enter 10 without error. What is the most likely mistake?

medium

A. The formula was entered as text, not as a formula

B. The cell A1 is formatted as Text, not Number

C. The data validation was applied to the wrong cell

D. The formula uses incorrect logical operators

Solution

1. Step 1: Analyze why validation fails

   If users enter 10 and no error shows, validation is not working on that cell.

2. Step 2: Check common causes

   Most likely, the validation rule was applied to a different cell, not the one users edit.

3. Final Answer:

   The data validation was applied to the wrong cell -> Option C

4. Quick Check:

   Wrong cell validation = The data validation was applied to the wrong cell [OK]

Hint: Always confirm validation applies to correct cells [OK]

Common Mistakes:

• Entering formula as text disables validation
• Ignoring cell format effects
• Assuming formula logic is wrong without checking range

5. You want to create a data validation rule that allows only dates in the current year. Which custom formula should you use?

hard

A. =AND(ISNUMBER(A1), YEAR(A1)=TODAY())

B. =AND(ISDATE(A1), YEAR(A1)=YEAR(TODAY()))

C. =AND(ISNUMBER(A1), MONTH(A1)=YEAR(TODAY()))

D. =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY()))

Solution

1. Step 1: Understand the goal

   Allow only dates in the current year, so check if A1 is a number (dates are numbers) and year matches current year.

2. Step 2: Evaluate options

   =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY())) uses ISNUMBER (correct for dates), and YEAR(A1)=YEAR(TODAY()) which matches current year.

3. Final Answer:

   =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY())) -> Option D

4. Quick Check:

   Check date number and year match = =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY())) [OK]

Hint: Use ISNUMBER and YEAR to validate dates in current year [OK]

Common Mistakes:

• Using ISDATE which is not an Excel function
• Comparing MONTH to YEAR
• Comparing YEAR to full date instead of year only