Excel Drop-Down List: Data Validation Steps, Alerts and Limits

The 6 steps on Windows, in Microsoft's order

Microsoft's page covers Excel for Microsoft 365, Excel 2024 and Excel 2021 (Windows and Mac), plus Excel 2019 and 2016 (Windows). The Windows route has these steps:

• Type the entries on a worksheet, for example a new sheet.
• Select the cell that will hold the drop-down.
• On the Data tab, open Data Validation.
• On the Settings tab, set Allow to List.
• In the Source box, select the list range. Leave out the header row. Microsoft's own example is A2:A9 for a Cities list.
• Keep Ignore blank and In-cell dropdown ticked as needed, then click OK.

Two ways to fill the Source box: a range or typed commas

A range is the better choice for anything that changes. Microsoft recommends storing items in an Excel table when possible. Select a cell in the range and press Ctrl+T on Windows to make a table. A drop-down based on a table updates when you add or remove items.

On a Mac, and in Excel for the web, you can instead type values straight into the Source box, separated by commas. In the Mac steps the entries should sit in one column or row with no blank cells. Select the list with the mouse, then press Return or choose Expand.

A worked example: a Status column with 3 choices

Put Open, In progress and Done in Lists!A2:A4, with the heading Status in A1. Select C2:C50 on your tracker sheet. Choose Data > Data Validation, set Allow to List, and click the Source box. Switch to the Lists sheet and select A2:A4, leaving out A1.

Now type Blocked in Lists!A5. Because the Source still ends at A4, the drop-down keeps showing 3 items. If you had made the entries a table with Ctrl+T first and pointed Source at its data column, the new row would join the list.

Typing the same choices into Source would read Open,In progress,Done. That is 20 characters including the commas, far below the 255 character limit, but you would have to edit the Source box in every validated range whenever the list changes.

The 255-character limit applies to typed lists

In Microsoft's VBA documentation for Validation.Add, the Formula1 value must not exceed 255 characters. For a list, Formula1 holds either a comma-delimited list of values or a worksheet reference. A range reference is short, so the list behind it can be long.

The Data Validation page does not state a maximum number of items in a range-based list, so no figure is given here. A worksheet holds 1,048,576 rows, which is the outer edge for a column-based list.

Stop, Warning and Information: only one blocks the wrong entry

The Error Alert tab has 3 styles. Stop blocks entries that are not in the list. Warning and Information show an icon and a message, but they do not block invalid entries.

If you leave the title and text empty, Excel uses the title Microsoft Excel and a generic message that the value is not valid. The Input Message tab lets you show a prompt when the cell is selected, with a title and message of up to 225 characters each. Clearing the check box turns a message off.

Data Validation is greyed out: protected or shared

If the command is unavailable, Microsoft says the worksheet may be protected or shared. Remove protection or stop sharing, make the change, then restore it. The Mac steps give the same reason.

Mac and Excel for the web use different menu names

On a Mac, find the command on the Data tab under Tools, where it is called Data Validation or Validate. Excel for the web uses Data tab > Data Tools > Data Validation. All three use a Settings tab with Allow set to List.

In VBA the type is xlValidateList, and AlertStyle is one of xlValidAlertInformation, xlValidAlertStop or xlValidAlertWarning.

Google Sheets offers chips, arrows or plain text

In Google Sheets, type @ and choose Dropdowns, or use Insert > Data validation, or right-click a cell. Items come from a range of cells or from values you enter, and Add another item adds more. The display style is Chip, Arrow or Plain text.

Allow multiple selections works only with chip-format dropdowns, and Google says it is not possible on mobile. Excel's page has no equivalent setting. Entries not on the list are rejected by default, and the Advanced options can switch that to a warning.

Google's lists follow a source range automatically, and the options run down columns first and then across rows.

Common mistakes

Including the header row in Source adds the heading as a choice. Choosing Warning instead of Stop lets wrong values through. On a Mac, leave no blank cells in the list column or row. If you are staring at a validation dialog and cannot tell which field is wrong, Crade can read your screen and point to it.

Who it suits

A drop-down suits forms, trackers and any column where spelling changes break a COUNTIF or a filter. Typed lists suit 3 to 5 stable options. Ranges and tables suit anything edited weekly. It does not suit free-text fields.