Hands-On Lab: Excel Data Validation Without Formulas

Excel offers a powerful Data Validation feature to restrict user input based on specific criteria. This tutorial shows how to use built-in options — no formulas needed.

📦 Case Study 1 – Whole Numbers Between 1–100

Use Whole number to restrict input between 1 and 100, such as for a Quantity column.

1. Select the column for input (e.g., column B).
2. Go to Data → Data Validation.
3. Select Whole number, set Minimum: 1, Maximum: 100.
4. Click OK.

In this video, we use a slightly different case: an order form where users are only allowed to enter an order quantity between 1 and 5. With this validation, we prevent values that are too small or too large, keeping the form clean and well-controlled.

🎥 Video Tutorial: Decimal Validation with Between

📦 Case Study 2 – Dropdown List

Use List so users can only select from specific values, such as department options: TKJ, RPL, MM.

1. Type the options in another range, e.g., F1:F3.
2. Select the target column (e.g., column C).
3. Go to Data → Data Validation, select List.
4. Set =F1:F3 as the Source.
5. Click OK.

In this video, we discuss the List feature using the same order form. The goal is to restrict user input to a predefined list of departments in a factory. For example, to avoid typing mistakes such as “manupaktur” instead of the correct “manufacture”, we create a List dropdown containing valid department names. This approach keeps the data clean, consistent, and free from typing errors.

🎥 Video Tutorial: Validation with List

📦 Case Study 3 – Date of Birth

Limit dates within a specific range. Example: only allow dates between Jan 1, 1980 and Dec 31, 2010.

1. Select the column for date input (e.g., column D).
2. Go to Data → Data Validation, select Date.
3. Set condition: between. Start date: 1/1/1980, End date: 12/31/2010.
4. Click OK.

📦 Case Study 4 – Phone Number Text Length

Use Text length to ensure phone numbers are between 10 and 13 digits.

1. Select the input column (e.g., column E).
2. Go to Data → Data Validation.
3. Select Text length, set Minimum: 10, Maximum: 13.
4. Click OK.

💬 Add Input Message & Error Alert

You can add an Input Message and Error Alert to help users input data correctly.

1. In the Data Validation window, go to the Input Message tab.
2. Fill in the title and message to show when the cell is selected.
3. Go to the Error Alert tab, choose Style: Stop, and set the error message.