如何在 Excel 中新增下拉式清單（資料驗證）

Tested in: The sample list, the COUNTIF membership check, and the custom quantity formula below were entered and evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not re-run the clicks in Excel this session.

When a drop-down is the right fix

A drop-down is for a short, known set of choices: regions, statuses, product sizes. It cuts down typos (“Nroth”) and later makes SUMIFS and pivot tables much easier, because “East” is spelled one way. It is not a substitute for a proper lookup table if people need to pick from thousands of SKUs — that is a job for a searchable list or a dedicated form.

If you already have one drop-down and need a second list that changes with the first (city depending on state), that is a dependent drop-down. The Sheets-specific version is in Dependent Drop-Down List in Google Sheets That Works on Every Row. This page covers the ordinary, single list.

Example data

A short region list in A1:A5, and an entry table in B1:C5. Rows 2 and 3 are valid. Row 4 uses a region that is not on the list and a negative quantity. Row 5 has a valid region but the quantity was typed as text:

Step 1: Build the list in a range

Type the items in a single column, with a header if you want one, and no blank rows in the middle. Point the drop-down at the items only, not at the header: $A$2:$A$5. The dollar signs keep the source from shifting when someone copies a validated cell down the column.

A named range is worth the extra minute if several columns will use the same list. Select A2:A5, open Formulas > Define Name (Excel) or Data > Named ranges (Sheets), and call it RegionList. The validation source is then:

=RegionList

Keep the list on its own sheet if it will grow. Leaving it to the left of the entry table is fine for a four-item example; it gets messy once someone inserts a row in the middle of the data.

Step 2: Apply Data Validation

In Excel 365

1. Select B2:B10 (leave room for new rows).
2. Open Data > Data Validation. If the button opens a menu, choose Data Validation again.
3. On the Settings tab, set Allow to List.
4. In Source, enter =$A$2:$A$5 (or =RegionList).
5. Leave In-cell dropdown checked.
6. On Error Alert, set the style to Stop, with a short message such as “Pick a region from the list.”
7. Click OK.

In Google Sheets

1. Select B2:B10.
2. Open Data > Data validation.
3. Click + Add rule.
4. Set Criteria to Dropdown (from a range) and select A2:A5.
5. Under Advanced options, choose Reject the input if the value must come from the list. Show a warning lets people type something else and only flags it.
6. Click Done.

Typing the items in the dialog (East,West,North,South) also works for a list that will never change. A range is easier to edit: add “Central” to A6, widen the source to $A$2:$A$6, and every drop-down updates. If you typed the items in the dialog, you have to edit the rule.

Step 3: Catch values that slipped in before the rule

Data validation does not rewrite cells that were already filled. In the example, “Midwest” can sit in B4 after you add the rule; Excel and Sheets will not delete it on their own. In Excel, use Data > Data Validation > Circle Invalid Data to put a red oval around those cells, fix them, then Clear Validation Circles. Sheets has no identical “circle” command; a helper column will do the same job in both programs:

=IF(B2="","",COUNTIF($A$2:$A$5,B2)>0)

On the 12 November 2026 check in Google Sheets, that formula returned TRUE for East and West, and FALSE for Midwest. Drag it down next to the entry column and filter for FALSE.

A leftover space will also fail the check: "East " is not "East". If your list looks right and the drop-down still rejects a typed value, look at TRIM not working and convert text to numbers — the same hidden-character problems show up in validation lists.

A custom rule for numbers, not a list

The quantity column is a different kind of validation. You do not want a drop-down of every possible number; you want “a number, and not negative.” In Excel, set Allow to Custom and use:

=AND(ISNUMBER(C2),C2>=0)

Apply that to C2:C10. In Google Sheets, use a Custom formula is rule with the same formula and Reject the input. I put the formula in a helper column as well, so the test did not depend on the rule UI alone. On the sample rows it returned:

Zero is a legitimate quantity in a lot of logs (“nothing shipped”). If you want to forbid zero as well, change the comparison to C2>0.

Things that usually go wrong

The source range includes the header, so the first item in the drop-down is “Region list.” Point at $A$2:$A$5, not $A$1:$A$5.

The list sits in a column that people sort with the data. The items themselves get shuffled or a blank lands in the middle. Keep the list off to the side or on another sheet.

Someone copies a validated cell and the source turns relative (A2:A5 without $) and starts pointing at the wrong rows further down. Use an absolute range or a name.

Excel and Sheets both treat a comma-separated source as a list of items. If your decimal separator is a comma, a typed list can split in the wrong places; a range avoids that.

A drop-down on a protected sheet will not be editable unless those cells are unlocked first. Unlock the entry cells, then protect the sheet.

After the list is in place

You can look up a rate or a code from the chosen region with XLOOKUP or INDEX/MATCH, the same way you would from any other cell. That is ordinary lookup work, not a special drop-down feature.

For a second list that depends on the first, go to the dependent drop-down tutorial rather than stuffing INDIRECT into this one. Get the simple list solid first: one source range, a hard reject, and a helper column for anything that was typed before the rule existed.