Excel Data Validation Allow List: Create and Manage One

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, create an allow list by selecting the cells, choosing Data > Data Validation > Data Validation, setting Allow to List, and entering the permitted values or a cell range in Source. Turn on In-cell dropdown to show a menu, and use the Stop error alert if people should not be able to type other values.

What an Excel allow list does

An allow list is a set of approved entries that Excel uses to guide or restrict what can be entered in a cell. Excel’s Data Validation dialog calls this option List, rather than allow list. It is useful for fields with a known set of choices, such as a department, order status, region, or priority. For example, a status field might permit only Draft, In progress, and Complete.

List validation can display a dropdown arrow in the selected cell, but the dropdown is optional. With In-cell dropdown enabled, a user can select an approved value rather than type it. With the option disabled, Excel can still check typed entries against the list, but there is no visible dropdown menu.

Validation applies to the cells where you set it; it does not automatically create a database-wide rule or clean up values already in the worksheet. It is a practical way to reduce inconsistent entries, not a complete security control. That distinction matters when the workbook is shared or when data is pasted in from other sources.

Create a basic dropdown allow list

For a short list, you can enter the items directly in the validation dialog. To make a dropdown for cells B2:B100 with three approved status choices:

  1. Select B2:B100. Selecting the whole intended input range first makes the same rule apply to every selected cell.
  2. On the ribbon, choose Data > Data Validation > Data Validation.
  3. On the Settings tab, set Allow to List.
  4. In Source, enter Draft,In progress,Complete. If your Excel regional settings use a different list separator, use the separator Excel expects, or use a worksheet range instead.
  5. Leave In-cell dropdown selected if you want users to choose from a visible menu. Choose Ignore blank if an empty cell is acceptable.
  6. Open the Error Alert tab, keep Show error alert selected, and choose Stop as the style if entries outside the list should be rejected during ordinary typing. Add a clear title or message if helpful.
  7. Select OK, then test a permitted value and an unlisted value in one of the cells.

In the dialog, the Input Message tab can show a note when a user selects a cell—for instance, “Choose the current order status.” This explains the expected action without putting instructions in the worksheet itself. The alert message can explain what to do when someone tries to enter an unapproved value.

Use a worksheet range as the source

A range is usually easier to maintain than a comma-separated list. Put the approved choices in H2:H4, one per cell, and select B2:B100 before opening Data Validation. Set Allow to List and use =$H$2:$H$4 as the Source. The dollar signs make the reference absolute, so it continues to point to the same source cells when the rule is applied to other destination cells.

A source range is especially useful when choices contain punctuation, when the list is longer than a few items, or when another person may need to update it. You can label the source column, place it on a separate worksheet, and protect or hide that worksheet if it helps keep the workbook tidy. A direct reference to a range on another worksheet may be rejected in the Source box; a defined name is a common way to use such a list.

To use a defined name, select the source cells and create a workbook-level name, such as AllowedStatuses, from Formulas > Define Name. Then use =AllowedStatuses in the validation Source box. A workbook-level name is often convenient because the validation rule can refer to it from other sheets. Check the name’s scope and its Refers to range if Excel reports that the source is invalid.

Choose and maintain the right source

The simplest source is not always the best one. A direct list is quick to set up, but every edit requires changing the validation rule. A worksheet range separates the approved values from the rule, so an editor can update the source cells more easily. A defined name makes a source on another worksheet easier to reference and gives the list a meaningful label.

  • Use a direct list for a handful of stable, short choices. Excel limits a directly typed list source to 255 characters, so this method is unsuitable for a large catalog.
  • Use a source range when choices may change, when they are numerous, or when they include commas or other punctuation.
  • Use a defined name when the list lives on another sheet or when a readable reference is useful to workbook maintainers.
  • Use an Excel table as the source data when the list will grow. Tables expand as rows are added, but validation may need a defined name that refers to the table’s column rather than a fixed cell range. Test the dropdown after adding an item.

Keep the source list clean: use one choice per cell, avoid unintended blank rows within it, and check for leading or trailing spaces. A blank source cell can appear as an empty choice in the dropdown. If you update the source, test both an existing entry and a newly added one so you know the validation rule still covers the intended values.

What Excel checks—and what it does not

When someone types into a cell with list validation, Excel can compare the entry with the permitted list and display an error alert for an unlisted value. A Stop alert is the strictest built-in alert style for normal entry: it blocks the attempted value and prompts the user to correct it. Warning and Information alerts allow a user to proceed after acknowledging the message, so they are less restrictive.

Validation is not a guarantee that every cell will always contain an approved value. Copying and pasting, filling cells, or using other workbook operations can introduce values without the usual typing alert, and a paste can also affect the validation rule itself. Existing values are not automatically corrected when you add a rule. Treat the rule as a helpful entry control, then inspect the data if accuracy is important.

To review a sheet, select the relevant cells and check Data > Data Validation to confirm the rule. Excel also provides Circle Invalid Data from the Data Validation menu on supported desktop versions, which can mark values that do not meet their cell rules. If you revise the approved list later, review earlier entries too; removing a choice from the source does not automatically replace old cells that contain it.

Common problems and how to fix them

  • The dropdown arrow is missing. Reopen Data Validation and confirm Allow is List and In-cell dropdown is selected. Also make sure the cell itself is selected; the arrow normally appears when you select a validated cell, not permanently beside every cell.
  • Excel rejects the source. Check that the reference begins with an equals sign, points to valid cells, and uses a defined name for a source on another sheet. If a directly typed list contains many items or commas within item names, move the choices to cells.
  • A newly added choice does not appear. The rule may point to a fixed range that does not include the new row. Expand the source range or use a suitably maintained table-based source and verify the defined name.
  • An unapproved value appears anyway. It may have been pasted or filled into the cell, or the rule may use Warning or Information instead of Stop. Check the rule and inspect the cell contents rather than assuming the dropdown enforced every edit.
  • Some list choices look duplicated. Look for spaces or inconsistent spelling in the source cells. Values that look similar to a person may still be different text, so clean the source and check entries already recorded.

Excel for Mac has the same basic List validation approach, although ribbon layout and dialog details can vary by release; look under Data for Data Validation. Excel for the web also supports data validation, but its controls and available editing options may differ from the Windows desktop dialog. If a workbook will be edited across platforms, test the dropdown and its rule in the versions your collaborators use.

Examples and alternatives

Allow lists suit fields where the valid choices are discrete and known in advance. A support tracker might allow Open, Waiting, and Resolved; a request form might offer Facilities, IT, and Payroll. Keeping those entries consistent makes later sorting, filtering, and summary reporting more reliable than accepting variations such as “In progress,” “In-progress,” and “Working.”

For values that follow a range rather than a fixed set of words, choose a different Data Validation rule. For example, a whole-number rule can restrict a quantity to a permitted interval, and a date rule can limit entries to a date range. If the goal is simply to flag questionable values without preventing entry, conditional formatting or a review column may be a better fit. If data is imported from many systems or requires strict controls, validation should be paired with review or a more structured data-entry process.

For dependent dropdowns—where the choices in one cell depend on another selection—an ordinary fixed list is only the starting point. Such designs need carefully maintained source ranges or names and additional formulas or tools. They are useful for categories and subcategories, but they add complexity; start with a single list if users only need to select among the same approved choices.

Frequently Asked Questions

Can I create an Excel allow list from another sheet?
Yes. Put the choices on the other sheet, create a defined name that refers to those cells, and enter that name with a leading equals sign in the Data Validation Source box. This avoids relying on a direct cross-sheet range reference.

How do I stop users from typing values outside the list?
Set the Error Alert style to Stop and keep Show error alert enabled. This blocks invalid values entered normally, but it is not foolproof against pasting or other workbook operations, so review important data as well.

Can the dropdown list grow when I add a new choice?
It can if the validation source covers the new row. A fixed reference such as H2:H4 will not include H5 automatically. Use an appropriately maintained table-based source or expand the referenced range, then test the dropdown.

Why can someone enter a value that is not on my list?
The rule may allow a warning or informational override, may not cover that cell, or may have been bypassed through paste or fill. Confirm the cell’s rule and alert style, and check whether the validation itself was overwritten.

Does changing the list fix values already entered?
No. Changing or shortening the source list does not automatically clean existing cells. Review the affected data and correct any values that are no longer permitted.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.