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.