Lesson 6.4 – Data Validation (Dropdown Lists)

Data Validation is one of the most important tools for controlling data entry in Excel. It helps you prevent mistakes, standardize inputs, and guide users to enter only valid values. One of the most common uses of Data Validation is creating dropdown lists, which allow users to select predefined options instead of typing manually.

---

1. Why Data Validation Matters

Data Validation improves the accuracy and consistency of your spreadsheets. It helps you:

• Prevent typing errors
• Ensure consistent categories (e.g., “Paid”, “Pending”, “Cancelled”)
• Control numeric ranges (e.g., values between 1 and 100)
• Restrict dates to specific periods
• Create professional, user‑friendly forms

It is essential for business reports, forms, surveys, inventory sheets, and dashboards.

---

2. Where to Find Data Validation

Menu path:

Data → Data Validation

This opens the Data Validation dialog box, where you can choose the type of restriction you want to apply.

---

3. Creating a Basic Dropdown List

Steps:

1. Select the cell or range where you want the dropdown list.
2. Go to Data → Data Validation.
3. In the “Allow” field, choose List.
4. In the “Source” box, type your list of values separated by commas.

Example:

Paid, Pending, Cancelled

Now the selected cells will display a dropdown arrow with the predefined options.

---

4. Creating a Dropdown List from a Range

Instead of typing values manually, you can create a dropdown list from a range of cells.

Steps:

1. Enter your list of values in a column (e.g., A1:A5).
2. Select the cell where you want the dropdown.
3. Go to Data → Data Validation.
4. Choose List.
5. In “Source”, select the range (e.g., =A1:A5).

This method is better for long lists or lists that may change over time.

---

5. Creating a Dynamic Dropdown List (Optional)

If your list may grow or shrink, you can convert it into an Excel Table.

Steps:

1. Select your list of values.
2. Press Ctrl + T to convert it into a Table.
3. Use the Table column as the source for your dropdown.

Excel automatically updates the dropdown when new items are added to the Table.

---

6. Other Useful Data Validation Types

• Whole Number

Restrict input to integers only (e.g., 1 to 100).

• Decimal

Allow decimal values within a range.

• Date

Restrict dates to a specific period (e.g., only future dates).

• Time

Restrict time entries (e.g., between 09:00 and 18:00).

• Text Length

Limit the number of characters (useful for IDs or codes).

• Custom Formula

Create advanced rules using formulas (e.g., prevent duplicates, allow only uppercase text).

---

7. Input Message and Error Alert

Data Validation allows you to guide users with messages.

• Input Message

Appears when the user selects the cell. Useful for instructions (e.g., “Choose a status from the list”).

• Error Alert

Appears when the user enters invalid data. You can choose:

• Stop – Blocks invalid input
• Warning – Allows input but warns the user
• Information – Suggests but does not enforce rules

---

8. Removing Data Validation

Steps:

Data → Data Validation → Clear All

This removes all restrictions from the selected cells.

---

9. Practical Exercise

1. Create a worksheet named Lesson_6_4_Practice.
2. Create a dropdown list with three status values.
3. Create a dropdown list from a range of cells.
4. Convert the list into a Table and test dynamic updates.
5. Apply numeric validation (1–100).
6. Apply date validation (only future dates).
7. Add an input message and an error alert.

---

Internal Links

• Lesson 6.2 – Freeze Panes and Split View
• Lesson 6.3 – Find and Replace / Go To Special
• Lesson 6.5 – Best Practices for Clean Spreadsheets

---

Next Lesson

Lesson 6.5 – Best Practices for Clean Spreadsheets