Microsoft Excel - Excel Data Validation

 Working...

Excel Data Validation

1. Understanding the Need for Excel Data Validation

Data Validation in Excel helps control the type of data entered into a cell.
It ensures data accuracy, consistency, and prevents invalid or incorrect entries.
For example, you can restrict users to enter only numbers, dates, or values from a predefined list.

Use Cases:

• Allow only whole numbers between 1 and 100.
• Accept only dates within a project timeline.
• Limit entries to department names like HR, Finance, Sales.

---

2. Creating an Excel Data Validation List

A drop-down list allows users to pick from set options rather than typing manually.

Steps:

1. Select the cell or range.
2. Go to Data → Data Validation → Settings tab.
3. In Allow, choose List.
4. In Source, type options separated by commas (e.g., Yes,No,Maybe) or refer to a cell range (=$A$1:$A$3).
5. Click OK.

Result:
A drop-down list appears, ensuring only predefined values can be selected.

---

3. Excel Decimal Data Validation

You can restrict entries to specific decimal ranges.

Steps:

1. Select cells → Data → Data Validation.
2. Choose Decimal under Allow.
3. In Data, choose a condition like between.
4. Enter the minimum and maximum values (e.g., 0 and 100).

Example:
Only values between 0.5 and 9.99 are accepted.

---

4. Adding a Custom Excel Data Validation Error

You can show a custom message when users enter invalid data.

Steps:

1. After setting validation rules, open the Error Alert tab.
2. Check Show error alert after invalid data is entered.
3. Choose a Style:
   • Stop (prevents invalid entry)
   • Warning (allows entry after warning)
   • Information (just informs user)
4. Add a Title and Message (e.g., “Invalid Entry: Please enter numbers only.”)

---

5. Dynamic Formulas by Using Excel Data Validation Techniques

You can make dynamic drop-downs that change based on another cell’s selection.

Example:

• Cell A1: Country (India, USA)
• Cell B1: City (changes based on country)

Steps:

1. Create lists for each country (e.g., IndiaList, USAList) and define named ranges.
2. In Cell B1 → Data Validation → Allow: List.
3. In Source, enter formula:

   =INDIRECT(A1)
   

4. Now, when “India” is selected in A1, only Indian cities appear in B1.