Data validation rules in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales coordinator at a retail company.

📋 Request: Your manager wants you to create a sales entry sheet that prevents incorrect data entry by using data validation rules.

📊 Data: You have columns for Date, Product Category, Quantity Sold, and Salesperson. The data entry sheet should only allow valid dates, specific product categories, positive whole numbers for quantity, and a list of approved salespersons.

🎯 Deliverable: Create a sales entry sheet with data validation rules applied to each column to ensure data accuracy.

Sample Data

Date	Product Category	Quantity Sold	Salesperson
2024-06-01	Electronics	5	Alice
2024-06-02	Clothing	3	Bob
2024-06-03	Home Goods	7	Charlie
2024-06-04	Electronics	2	Alice
2024-06-05	Clothing	4	Bob
2024-06-06	Home Goods	6	Charlie

1

Step 1: Select the Date column cells where data will be entered (e.g., B2:B100). Apply data validation to allow only dates.

Data > Data Validation > Allow: Date > Data: between > Start date: 2024-01-01 > End date: 2024-12-31

Expected Result

Only dates between 2024-01-01 and 2024-12-31 can be entered in the Date column.

2

Step 2: Select the Product Category column cells (e.g., C2:C100). Apply data validation to allow only a list of specific categories.

Data > Data Validation > Allow: List > Source: Electronics,Clothing,Home Goods

Expected Result

Only 'Electronics', 'Clothing', or 'Home Goods' can be entered in the Product Category column.

3

Step 3: Select the Quantity Sold column cells (e.g., D2:D100). Apply data validation to allow only whole numbers greater than zero.

Data > Data Validation > Allow: Whole number > Data: greater than > Minimum: 0

Expected Result

Only positive whole numbers can be entered in the Quantity Sold column.

4

Step 4: Select the Salesperson column cells (e.g., E2:E100). Apply data validation to allow only a list of approved salespersons.

Data > Data Validation > Allow: List > Source: Alice,Bob,Charlie

Expected Result

Only 'Alice', 'Bob', or 'Charlie' can be entered in the Salesperson column.

5

Step 5: Test the data validation by trying to enter invalid data in each column to confirm errors appear.

Try entering '2025-01-01' in Date, 'Food' in Product Category, '-3' in Quantity Sold, and 'David' in Salesperson.

Expected Result

Excel shows an error message and prevents invalid entries in all tested cells.

Final Result

Date       | Product Category | Quantity Sold | Salesperson
----------------------------------------------------------
2024-06-01 | Electronics      | 5             | Alice
2024-06-02 | Clothing         | 3             | Bob
2024-06-03 | Home Goods       | 7             | Charlie
2024-06-04 | Electronics      | 2             | Alice
2024-06-05 | Clothing         | 4             | Bob
2024-06-06 | Home Goods       | 6             | Charlie

✓Data validation rules prevent incorrect data entry.

✓Only valid dates within 2024 are accepted.

✓Product categories are limited to three specific options.

✓Quantity sold must be a positive whole number.

✓Salesperson names are restricted to approved staff.

Bonus Challenge

Add a data validation rule to the Quantity Sold column that also limits the maximum quantity to 100.

Show Hint

Modify the existing whole number validation to set the data range between 1 and 100.

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