Data validation rules in Google Sheets - 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 errors by restricting the type of data entered in each column.

📊 Data: You have a table with columns: Date, Product Category, Quantity Sold, and Salesperson. The data will be entered daily by different team members.

🎯 Deliverable: Create a Google Sheets sales entry form with data validation rules to ensure correct and consistent data entry.

Sample Data

Date	Product Category	Quantity Sold	Salesperson
2024-06-01	Electronics	10	Alice
2024-06-01	Clothing	5	Bob
2024-06-02	Home Goods	8	Charlie
2024-06-02	Electronics	7	Alice
2024-06-03	Clothing	12	Bob
2024-06-03	Home Goods	4	Charlie

1

Step 1: Select the Date column cells where data will be entered (e.g., A2:A100).

Go to Data > Data validation. Set Criteria to 'Date' and select 'is valid date'.

Expected Result

Only valid dates can be entered in the Date column. Invalid entries show an error.

2

Step 2: Select the Product Category column cells (e.g., B2:B100).

Go to Data > Data validation. Set Criteria to 'List of items' and enter 'Electronics,Clothing,Home Goods'.

Expected Result

Users can only select one of the three categories from a dropdown list.

3

Step 3: Select the Quantity Sold column cells (e.g., C2:C100).

Go to Data > Data validation. Set Criteria to 'Number' and choose 'is a number greater than or equal to' 1.

Expected Result

Only numbers 1 or greater can be entered. Negative numbers or text are rejected.

4

Step 4: Select the Salesperson column cells (e.g., D2:D100).

Go to Data > Data validation. Set Criteria to 'List from a range' and select a range with salesperson names (e.g., F2:F4 containing Alice, Bob, Charlie).

Expected Result

Users can only select a salesperson from the dropdown list to avoid typos.

5

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

Try entering text in Quantity Sold, an invalid date in Date, a category not in the list, or a salesperson not in the list.

Expected Result

Google Sheets shows an error message and prevents invalid data entry.

Final Result

Date       | Product Category | Quantity Sold | Salesperson
----------------------------------------------------------
2024-06-01 | Electronics      | 10            | Alice
2024-06-01 | Clothing         | 5             | Bob
2024-06-02 | Home Goods       | 8             | Charlie
2024-06-02 | Electronics      | 7             | Alice
2024-06-03 | Clothing         | 12            | Bob
2024-06-03 | Home Goods       | 4             | Charlie

✓Data validation rules help prevent mistakes by restricting input types.

✓Dropdown lists make it easy to select valid categories and salespersons.

✓Validating dates and numbers ensures data consistency and accuracy.

Bonus Challenge

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

Show Hint

In Data validation for Quantity Sold, choose 'Number' and set criteria to 'between' 1 and 100.