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.

Practice

1. What is the main purpose of Data validation rules in Google Sheets?

easy

A. To control what users can enter in cells to keep data clean

B. To automatically calculate sums and averages

C. To change the color of cells based on values

D. To create charts from data

Solution

1. Step 1: Understand what data validation does

   Data validation sets rules that limit what users can type or select in cells.

2. Step 2: Compare options to the purpose

   Only To control what users can enter in cells to keep data clean describes controlling user input to keep data clean, which is the main goal.

3. Final Answer:

   To control what users can enter in cells to keep data clean -> Option A

4. Quick Check:

   Data validation = control input [OK]

Hint: Data validation controls input, not calculations or colors [OK]

Common Mistakes:

• Confusing data validation with formulas
• Thinking it changes cell colors
• Assuming it creates charts

2. Which of these is the correct way to set a data validation rule for a cell to only allow numbers between 1 and 10?

easy

A. Select the cell, go to Data > Data validation, choose 'Number' and set 'between' 1 and 10

B. Type =IF(A1>0, "Valid", "Invalid") in the cell

C. Use conditional formatting to highlight numbers between 1 and 10

D. Enter a formula =AND(A1>=1, A1<=10) directly in the cell

Solution

1. Step 1: Identify how to set data validation

   Data validation rules are set via the Data menu, not by typing formulas in cells.

2. Step 2: Match the correct method

   Select the cell, go to Data > Data validation, choose 'Number' and set 'between' 1 and 10 correctly describes using Data > Data validation and setting number between 1 and 10.

3. Final Answer:

   Select the cell, go to Data > Data validation, choose 'Number' and set 'between' 1 and 10 -> Option A

4. Quick Check:

   Data validation set via menu = Select the cell, go to Data > Data validation, choose 'Number' and set 'between' 1 and 10 [OK]

Hint: Set rules via Data menu, not by typing formulas [OK]

Common Mistakes:

• Trying to type validation formulas in cells
• Confusing conditional formatting with validation
• Using formulas that only display text, not restrict input

3. You set a data validation rule on cell A1 to allow only dates after 2023-01-01. What happens if a user tries to enter '2022-12-31'?

medium

A. The entry is accepted without any warning

B. The cell automatically changes the date to 2023-01-02

C. Google Sheets shows an error and prevents the entry

D. The cell highlights the date but allows the entry

Solution

1. Step 1: Understand data validation behavior for invalid input

   When a user enters data that breaks the validation rule, Sheets shows an error and blocks the entry by default.

2. Step 2: Apply this to the date rule

   Since '2022-12-31' is before '2023-01-01', the entry is invalid and will be rejected with an error.

3. Final Answer:

   Google Sheets shows an error and prevents the entry -> Option C

4. Quick Check:

   Invalid input triggers error and blocks entry [OK]

Hint: Invalid input triggers error and blocks entry by default [OK]

Common Mistakes:

• Thinking invalid data is accepted silently
• Assuming automatic correction of invalid data
• Confusing highlighting with blocking input

4. You created a data validation rule to allow only text from a list: 'Apple', 'Banana', 'Cherry'. But users can still enter 'Orange'. What is the likely mistake?

medium

A. The list was entered as plain text in the cell, not as a range or list in validation

B. The validation rule was set to 'Show warning' instead of 'Reject input'

C. The cell was formatted as Number instead of Plain text

D. The sheet was not saved after setting the rule

Solution

1. Step 1: Check validation settings for handling invalid input

   Data validation can either reject invalid input or just show a warning but allow it.

2. Step 2: Identify why 'Orange' is accepted

   If 'Show warning' is selected, invalid entries like 'Orange' are allowed with a warning, explaining the issue.

3. Final Answer:

   The validation rule was set to 'Show warning' instead of 'Reject input' -> Option B

4. Quick Check:

   Show warning allows invalid input [OK]

Hint: Use 'Reject input' to block wrong entries, not just warn [OK]

Common Mistakes:

• Confusing warning with blocking input
• Not checking validation input handling option
• Assuming formatting controls validation

5. You want to create a data validation rule that allows users to select only weekdays (Monday to Friday) from a date picker in cell B2. Which custom formula should you use in the data validation rule?

hard

A. =OR(WEEKDAY(B2) = 1, WEEKDAY(B2) = 7)

B. =B2 >= DATE(2023,1,1)

C. =WEEKDAY(B2) > 5

D. =AND(WEEKDAY(B2) >= 2, WEEKDAY(B2) <= 6)

Solution

1. Step 1: Understand WEEKDAY function output

   WEEKDAY returns 1 for Sunday, 2 for Monday, ..., 7 for Saturday.

2. Step 2: Identify weekdays range

   Weekdays are Monday (2) through Friday (6), so the formula must check if WEEKDAY is between 2 and 6.

3. Step 3: Match formula to condition

   =AND(WEEKDAY(B2) >= 2, WEEKDAY(B2) <= 6) uses =AND(WEEKDAY(B2) >= 2, WEEKDAY(B2) <= 6), which correctly allows only weekdays.

4. Final Answer:

   =AND(WEEKDAY(B2) >= 2, WEEKDAY(B2) <= 6) -> Option D

5. Quick Check:

   Weekdays = WEEKDAY 2 to 6 [OK]

Hint: WEEKDAY 2-6 means Monday to Friday [OK]

Common Mistakes:

• Using wrong WEEKDAY numbers for weekdays
• Allowing weekends by mistake
• Not using AND to check range