sodcalculator

10 Tech How-To

Excel Custom List Data Validation Drop-Down Guide

Guide 10-56010 · Updated · Step by step

This guide shows you how to create a data validation drop-down list in Excel using a custom list of values that you type yourself. This is useful when you want to restrict entries in a cell to a specific set of options, like product names, statuses, or team members, without linking to a range on the worksheet. You’ll learn the exact steps, common pitfalls, and how to fix issues when the drop-down doesn’t behave as expected.

What You’ll Need

  • Microsoft Excel (2010 or later; the steps are essentially the same across versions).
  • A worksheet where you want the drop-down to appear.
  • A clear idea of the list items you want to use (e.g., “Pending, In Progress, Done”).

Step-by-Step

  1. Select the cell or range where you want the drop-down. Click a single cell, or click and drag to select multiple cells. To select non-adjacent cells, hold Ctrl while clicking.
  2. Open the Data Validation dialog. Go to the Data tab on the ribbon. In the Data Tools group, click Data Validation. (In older versions, it might be under Data > Validation.)
  3. Choose List as the Allow option. In the dialog box, under the Settings tab, click the Allow drop-down and select List.
  4. Enter your list items. In the Source box, type your items separated by commas. For example: Pending, In Progress, Done. Do not use spaces after commas unless you want those spaces to appear in the drop-down (they will be part of the item).
  5. Set other options (optional). Check Ignore blank if you want to allow the cell to be empty. Check In-cell dropdown to show the arrow button next to the cell (this is checked by default).
  6. Add an input message (optional). Click the Input Message tab. Check Show input message when cell is selected and type a title and message, like “Choose a status” and “Select from the list.”
  7. Add an error alert (optional). Click the Error Alert tab. Check Show error alert after invalid data is entered. Choose a style (Stop, Warning, Information) and type a title and error message, like “Invalid entry” and “Please choose a value from the list.”
  8. Click OK. Your drop-down is now active. Click the cell to see the arrow and select an item.

Common Mistakes

  • Using commas incorrectly: If your list items contain commas (e.g., “New York, NY”), you cannot use a comma as a separator. Instead, use a different separator like a semicolon (depending on your regional settings) or use a named range (see Tips).
  • Typing spaces after commas: A space after a comma becomes part of the item. “Pending, In Progress” creates items “Pending” and ” In Progress” (with a leading space). This can cause mismatches if you later use formulas that compare cell values.
  • Overwriting the list when copying cells: If you copy a cell with data validation and paste it over another cell, it will overwrite the validation in the destination. Use Paste Special > Validation to copy only the validation.
  • Using a range that includes blank cells: If you reference a range (e.g., =$A$1:$A$10) and some cells are blank, the drop-down will show blanks. Use a dynamic named range or ensure the range contains only list items.
  • Forgetting to update the list when items change: If you type the list directly in the Source box, you must manually edit the validation rule each time you want to change the items. Consider using a named range for easier maintenance.

Tips

  • Use a named range for easy updates: Instead of typing items in the Source box, create a named range that references a list on a separate sheet. For example, put your items in A1:A5 on a sheet named “Lists”, then go to Formulas > Name Manager > New, name it “StatusList”, and set Refers to =Lists!$A$1:$A$5. In the Source box, type =StatusList. Now you can edit the list in one place and the drop-down updates automatically.
  • Use a table for dynamic ranges: If you convert your list to an Excel Table (Insert > Table), you can reference the table column in the Source box (e.g., =Table1[Status]) and it will automatically expand as you add rows.
  • Remove duplicates: If you reference a range, make sure there are no duplicate values, as they will appear as separate items in the drop-down.
  • Use the “In-cell dropdown” wisely: If you uncheck it, users can still type values, but they won’t see the arrow. This is useful if you want to force typing but still validate against the list.
  • Copy validation to multiple cells: Select the cell with the validation, press Ctrl+C, then select the target range, right-click and choose Paste Special > Validation.

Troubleshooting

The drop-down arrow is not showing.

Make sure the cell has data validation with the “In-cell dropdown” checkbox checked. Also, if the cell is not selected, the arrow only appears when you click on it. If you have multiple cells selected, the arrow may not appear until you click a single cell.

I get an error when I type a value that is not in the list, but I want to allow it.

Change the Error Alert style to “Warning” or “Information” instead of “Stop”. Warning will ask for confirmation, Information will just show a message, but both allow the entry.

My list items contain commas, and the drop-down splits them incorrectly.

Use a different separator, like a semicolon (if your system uses comma as the list separator, semicolon is the alternative). Alternatively, use a named range that references cells containing the items with commas.

The drop-down is not updating when I change the list.

If you typed the list directly in the Source box, you must manually edit the validation rule. If you used a named range, make sure the range is correct and that you didn’t accidentally convert it to a static reference. Also, check that the named range is not misspelled.

I copied a cell with validation, but the validation is gone in the new cell.

Use Paste Special > Validation to copy only the validation, not the cell’s value or formatting.

The drop-down shows blank items.

If you referenced a range, blank cells in that range will appear as blank items. Remove blanks from the range, or use a dynamic named range that only includes non-blank cells.

Frequently Asked Questions

Can I use a formula to generate the list items?

Yes, you can use a formula in the Source box, but it must return a comma-separated list or a reference to a range. For example, you can use =TEXTJOIN(“,”,TRUE,A1:A10) to create a comma-separated list from a range, but this is not directly supported in all Excel versions. A more reliable method is to use a named range with a dynamic formula like =OFFSET(…) to adjust the range size.

How do I remove data validation from a cell?

Select the cell(s), go to Data > Data Validation, and click the Clear All button in the bottom left of the dialog, then click OK.

Can I use a drop-down list in a table column?

Yes, you can apply data validation to a column in an Excel Table. Select the column cells (excluding the header) and follow the same steps. The validation will apply to new rows added to the table.

Why is my drop-down list not showing all items when I use a named range?

Check that the named range refers to the correct cells and that the range does not include blank cells. Also, ensure that the named range is not using a relative reference (e.g., =A1:A5 instead of =Sheet1!$A$1:$A$5). Use absolute references.

Can I make the drop-down list searchable?

Excel’s built-in data validation drop-down is not searchable. For a searchable drop-down, you would need to use ActiveX ComboBox or a third-party add-in. However, you can type the first letter and Excel will jump to the first matching item if the list is sorted.

Does the drop-down work on Excel for Mac?

Yes, the steps are similar. The Data Validation dialog is under the Data tab, and the options are the same. The keyboard shortcuts may differ (e.g., use Command instead of Ctrl).

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →