Data validation rules in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of a dropdown list validation

You set a data validation rule in cell A1 to allow only the values: Apple, Banana, Cherry. What happens if you enter "Banana" in A1?

A The cell accepts "Banana" without error.

B The cell clears the input and stays empty.

C The cell automatically changes "Banana" to "Apple".

D The cell shows an error message and rejects "Banana".

💡 Hint

Think about what a dropdown list validation does when you enter a valid option.

When you set a dropdown list validation with specific allowed values, entering one of those values is accepted without error.

❓ Function Choice

intermediate

Choosing the right validation for numbers

You want to restrict input in cell B2 to only whole numbers between 1 and 10 inclusive. Which data validation setting should you use?

A Allow: Number; Condition: Between; Minimum: 1; Maximum: 10; Reject input if invalid

B Allow: Text; Condition: Contains; Text: "1,2,3,4,5,6,7,8,9,10"

C Allow: Number; Condition: Greater than; Minimum: 0; Reject input if invalid

D Allow: Date; Condition: Between; Start date: 1; End date: 10

💡 Hint

Think about which validation type fits numbers and how to limit a range.

To restrict input to whole numbers between 1 and 10, use Number validation with Between condition and set min and max to 1 and 10.

🎯 Scenario

advanced

Preventing duplicate entries in a column

You want to prevent users from entering duplicate values in column C. Which data validation custom formula should you use in cell C2 (and apply down the column)?

A =ISUNIQUE(C2)

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

C =COUNTIF(C$2:C2, C2)>1

D =C2<>""

💡 Hint

Think about how to count occurrences of the current cell value in the whole column.

The formula =COUNTIF(C:C, C2)=1 checks if the value in C2 appears only once in the entire column, preventing duplicates.

❓ data_analysis

advanced

Effect of ignoring invalid data

You set a data validation rule on cell D1 to allow only dates after 2023-01-01, but you check the option "Show warning" instead of "Reject input". What happens if a user enters 2022-12-31?

A The cell blocks entry and shows an error message.

B The cell rejects 2022-12-31 and clears the input.

C The cell automatically changes 2022-12-31 to 2023-01-02.

D The cell accepts 2022-12-31 but shows a small warning icon.

💡 Hint

Consider the difference between "Show warning" and "Reject input" options.

With "Show warning", invalid data is allowed but marked with a warning icon. "Reject input" would block it.

🧠 Conceptual

expert

Understanding data validation with INDIRECT function

You have a named range "Fruits" listing Apple, Banana, Cherry. You set data validation in cell E1 with Criteria: List from a range, and enter the formula =INDIRECT("Fruits"). What is the effect?

A Cell E1 allows any text input without restriction.

B Cell E1 shows an error because INDIRECT cannot be used in data validation.

C Cell E1 shows a dropdown with Apple, Banana, Cherry as options.

D Cell E1 shows a dropdown but with empty options.

💡 Hint

Think about how INDIRECT works with named ranges in data validation.

INDIRECT("Fruits") returns the range named "Fruits", so the dropdown shows those values.