Data validation rules in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a data validation rule that allows only whole numbers.

Excel

Select the cell, then go to Data > Data Validation > Allow: [1]

Drag options to blanks, or click blank then click option'

AList

BText length

CDate

DWhole number

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting 'List' instead of 'Whole number' allows any list values, not numbers.

Choosing 'Text length' restricts text length, not numbers.

Choosing "Whole number" in the Allow dropdown restricts input to whole numbers only.

2fill in blank

medium

Complete the code to set a data validation rule that only allows dates after January 1, 2023.

Excel

In Data Validation, set Allow to Date and set Data to [1] and Start date to 1/1/2023.

Drag options to blanks, or click blank then click option'

Abetween

Bgreater than

Cless than

Dequal to

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'between' requires two dates, but only one is given.

Selecting 'less than' allows dates before the start date.

Using "greater than" with the start date 1/1/2023 allows only dates after that date.

3fill in blank

hard

Fix the error in the formula used for data validation to allow only values less than 100.

Excel

Use the formula: =[1]<100 in the data validation custom formula box.

Drag options to blanks, or click blank then click option'

AA1

B100

CSUM(A1)

DIF(A1)

Attempts:

3 left

💡 Hint

Common Mistakes

Using '100<100' is always false.

Using 'SUM(A1)<100' is unnecessary and may cause errors.

The formula must reference the cell (e.g., A1) to compare its value to 100.

4fill in blank

hard

Fill both blanks to create a data validation rule that allows only text entries with length less than 10.

Excel

Set Allow to Text Length and Data to [1] with Maximum length [2].

Drag options to blanks, or click blank then click option'

Aless than

Bgreater than

C10

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'greater than' allows longer text, not shorter.

Setting max length to 5 is too restrictive.

Choosing "less than" and setting maximum length to 10 restricts text length to fewer than 10 characters.

5fill in blank

hard

Fill all three blanks to create a data validation formula that allows only numbers between 1 and 10 inclusive.

Excel

Use the formula: =AND([1]>=1, [2]<=10, ISNUMBER([3]))

Drag options to blanks, or click blank then click option'

AB2

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Using different cell references causes errors.

Omitting ISNUMBER allows text values.

The formula checks if the cell B2 is a number between 1 and 10 inclusive.