# Data Validation in Excel: How to Add Dropdown Lists and Rules

Data validation in Excel controls what a user can type into a cell. You can restrict entries to a dropdown list, a whole number between two limits, a date range, a text length or a custom formula. This article shows the exact steps, a worked example, and the fixes for the problems people hit most often.

## Quick Answer

- Select the cells you want to control, then go to **Data > Data Validation > Data Validation** [1].
- On the **Settings** tab, pick an option from the **Allow** list, such as **List** or **Whole number** [1].
- For a dropdown, type the values in the **Source** box separated by commas, or point to a range on the sheet.
- For numbers, choose the comparison in the **Data** box, then enter the minimum and maximum values [1].
- Use the **Input Message** and **Error Alert** tabs to tell users what is expected and what went wrong [1].

## Before You Start

Data validation works on a cell or a selected range, and it applies to new entries typed after you set it up. If the cells already contain data, Excel does not flag those existing values automatically [2]. You can ask Excel to circle invalid entries with **Data > Data Tools > Data Validation > Circle Invalid Data**, and the circles disappear as you correct each one [2].

Two conditions block the feature. If the sheet is protected or the workbook is shared, the Data Validation command is unavailable and you cannot change the settings [1]. Unprotect the sheet or stop sharing the workbook first.

Plan what "valid" means before you click anything. A rating scale of 1 to 5, an age between 18 and 100, or a status from a fixed list are all easy to express. Vague rules produce vague errors.

## Step by Step

1. **Select the target cells.** Drag over the range, or hold Ctrl and click separate cells. Validation applies to everything selected.
2. **Open the dialog.** Go to **Data > Data Validation > Data Validation** [1].
3. **Choose what to allow.** On the **Settings** tab, open the **Allow** list. Common choices are **Whole number**, **Decimal**, **List**, **Date**, **Time**, **Text length** and **Custom** [1].
4. **Set the condition.** The **Data** box offers comparisons such as **between**, **greater than** and **less than**. For a range, pick **between** and fill in the minimum and maximum [1].
5. **Enter the source for a list.** Type values separated by commas, or reference a range. A dropdown appears when the cell is selected.
6. **Add an input message.** On the **Input Message** tab, check **Show input message when cell is selected**, then type a title and the message text [1].
7. **Add an error alert.** On the **Error Alert** tab, choose **Stop**, **Warning** or **Information** and write the message users see when an entry is rejected [1].
8. **Click OK.** Test the rule by typing a valid value and an invalid one.

If you only need a dropdown and nothing else, the shorter walkthrough in [How to Create a Drop Down List in Excel (Step by Step)](/blog/data-analysis/how-to-create-drop-down-excel) covers the same dialog from the list angle.

## Worked Example

A ten-person survey collects a rating, an age and a free-text comment. The Rating column should only accept 1 through 5, and the Age column should only accept whole numbers from 18 to 100.

|   | A | B | C | D |
|---|---|---|---|---|
| 1 | Respondent | Rating | Age | Comments |
| 2 | Ana | 5 | 28 | Great |
| 3 | Ben | 4 | 34 | Good |
| 4 | Cara | 3 | 22 | Average |
| 5 | Dan | 2 | 45 | Poor |
| 6 | Eve | 1 | 31 | Bad |
| 7 | Finn | 5 | 27 | Excellent |
| 8 | Gus | 4 | 39 | Good |
| 9 | Hana | 3 | 24 | Average |
| 10 | Ivy | 2 | 36 | Poor |
| 11 | Jon | 1 | 29 | Bad |

The interface steps are:

1. Select B2:B11, then go to **Data > Data Validation > Data Validation**.
2. In the **Settings** tab, choose **Allow: List**, **Source: 1,2,3,4,5**, then click OK.
3. Select C2:C11, then go to **Data > Data Validation > Data Validation**.
4. In the **Settings** tab, choose **Allow: Whole number**, **Data: between**, **Minimum: 18**, **Maximum: 100**, then click OK.

After that, each Rating cell shows a dropdown with the five options, and any Age entry outside 18 to 100 is rejected. The Comments column is left unrestricted because free text has no fixed rule.

You can also build a limit from a formula. If the number of children is recorded in cell F1 and you want the minimum entry in the validated cells to be twice that number, select **greater than or equal to** in the **Data** box and enter `=2*F1` in the **Minimum** box [1]. The rule recalculates as F1 changes.

## Other Ways to Do It

**Reference a range instead of typing values.** For a long list, keep the options on a separate sheet and point the **Source** box at that range. This keeps the dropdown maintainable when the list changes.

**Use a custom formula.** The **Custom** option accepts a formula that returns TRUE or FALSE. This is how you enforce rules that the built-in comparisons cannot express, such as requiring an entry to start with a specific prefix.

**Copy validation to other cells.** Copy a validated cell, then paste with **Paste Special > Validation** to transfer only the rule. This avoids overwriting the destination's contents.

**Find cells that already break the rule.** Use **Circle Invalid Data** to mark existing entries that fail the current validation [2].

**Filter the results afterward.** Once the data is clean, [Excel FILTER Function: Syntax and Examples](/blog/data-analysis/excel-filter-function-syntax-examples) shows how to pull matching rows into a separate view.

## Troubleshooting

**The Data Validation button is grayed out.** The sheet is protected or the workbook is shared. Both block changes to validation settings [1].

**The dropdown does not appear.** Check that **Allow** is set to **List** and that the **Source** box is not empty. If you referenced a range, confirm the range still exists.

**Valid entries are being rejected.** Look at the **Data** box. Check that the minimum and maximum are the values you intended, since Excel will not save a **between** rule whose minimum is larger than its maximum. Also check whether the cell is formatted as text, which can make numeric entries fail a number rule.

**Existing bad values are still sitting there.** Validation only checks new typing. Use **Circle Invalid Data** to find them, then fix each one [2].

**You want to remove the rule.** Select the cells, go to **Data > Data Validation**, and in the dialog box press **Clear All**, then OK [1]. A faster route is **Data > Data Tools > Data Validation > Settings > Clear All** [2].

## Common Mistakes

- **Typing the list into the wrong box.** The comma-separated values belong in **Source** on the **Settings** tab, not in the input message. Put them in the wrong field and no dropdown appears.
- **Forgetting that pasting bypasses validation.** Copying a cell and pasting normally overwrites the rule and the value. Use **Paste Special > Validation** when you want to carry the rule across.
- **Assuming old data gets checked.** Excel does not notify you that existing cells contain invalid data [2]. Run **Circle Invalid Data** after you apply a rule to a populated column.
- **Leaving the error alert on the default.** A generic message tells users nothing. Write a specific one, such as "Enter a whole number from 18 to 100."
- **Setting limits that contradict each other.** Excel refuses to save a minimum above the maximum, and limits that are valid but wrong reject good entries. Read both boxes before clicking OK.
- **Building the rule on a protected sheet.** The command is unavailable while the sheet is protected or the workbook is shared [1]. Unprotect first, then apply the rule.

## Limitations

Data validation is a typing guard, not a security feature. Anyone can copy a cell from elsewhere and paste over your rule, and anyone who knows the dialog can clear it. Treat it as a way to reduce input errors in shared workbooks, not as a way to lock data down [2].

It also does not clean data that is already there. Applying a rule to a filled column leaves the existing values untouched, and Excel stays silent about them until you ask it to circle invalid entries [2]. Validation is also per cell or range, so a rule you set on one column does not cover new rows added beyond the end of that range unless you extend the rule or keep the data in an Excel table.

## Frequently Asked Questions

### How do I add a dropdown list in Excel?

Select the cells, open **Data > Data Validation > Data Validation**, set **Allow** to **List**, and type your options in the **Source** box separated by commas [1]. Click OK and each selected cell shows a dropdown arrow when active.

### Why is my data validation not working?

The most common causes are a protected sheet or a shared workbook, both of which disable the Data Validation command [1]. If the rule exists but seems ignored, check whether the value arrived by paste, since pasting replaces the rule along with the cell contents.

### Can I apply data validation to cells that already contain data?

Yes, and the rule applies going forward [2]. Excel does not automatically flag the existing values as invalid, so use **Circle Invalid Data** to highlight entries that break the new rule, then correct them [2].

### How do I remove data validation from a cell?

Select the cells, go to **Data > Data Validation**, press **Clear All** in the dialog box, and click OK [1]. You can also reach the same command through **Data > Data Tools > Data Validation > Settings > Clear All** [2].

### Can I use a formula as a validation rule?

Yes. Choose **Custom** in the **Allow** list and enter a formula that returns TRUE or FALSE. You can also use a formula inside a numeric rule, for example entering `=2*F1` in the **Minimum** box to tie the limit to another cell [1].

## References

1. [Apply data validation to cells | Microsoft Support](https://support.microsoft.com/en-us/excel/get-started/apply-data-validation-to-cells)
2. [More on data validation | Microsoft Support](https://support.microsoft.com/en-us/excel/more-on-data-validation)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)

## Related Articles

- [How to Create a Drop Down List in Excel (Step by Step)](/blog/data-analysis/how-to-create-drop-down-excel)
- [Excel FILTER Function: Syntax and Examples](/blog/data-analysis/excel-filter-function-syntax-examples)
- [How to Freeze Rows and Columns in Excel (Step by Step)](/blog/data-analysis/how-to-freeze-panes-in-excel)
- [How to Use XLOOKUP in Excel (Step by Step)](/blog/data-analysis/how-to-use-xlookup-excel)
- [VLOOKUP in Excel: Formula, Syntax and Examples](/blog/data-analysis/vlookup-excel-formula-syntax-examples)