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.

📦 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.

📦 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.