Excel data-validation dropdowns are not alphabetized automatically: sort the cells that supply the list A to Z, then set the validation rule to use that sorted range. In Microsoft 365, a helper list built with SORT can keep the dropdown in order when its source values change.
How Excel validation lists get their order
A data-validation list displays its choices in the order they appear in its source. If the source cells contain Pear, Apple, and Banana in that order, the dropdown shows Pear, Apple, and Banana—not an automatic alphabetical arrangement. Excel treats the validation rule as a link to a list, rather than as a command to sort the choices.
This distinction matters because the source might be a range of worksheet cells, a defined name, or a short list typed directly into the Source box. A typed list such as Red,Green,Blue appears in that sequence. To make it alphabetical, enter it alphabetically yourself. For a longer list, it is usually easier to keep the choices in worksheet cells and sort those cells.
Sorting the source changes what users see in the dropdown; it does not sort the cells where users make selections. It also does not reorder existing selections already entered in those cells. If the list is intended to help users find an item, an A-to-Z source is a useful default, but it is a separate task from sorting the worksheet’s records.
Sort an existing source list A to Z
For a short, stable list, sort the source range once and leave the validation rule pointing to it. On Excel for Microsoft 365 on Windows:
- Locate the cells that contain the allowed choices and select the list range.
- Open the Data tab and choose Sort A to Z in the Sort & Filter group. For a text list, this sorts the selected items in ascending order.
- Select the cells that should have the dropdown, then choose Data > Data Validation.
- On the Settings tab, set Allow to List. In Source, select the sorted source cells or enter their range address, then choose OK.
For example, if the choices are in F2:F8 on the same worksheet, the Source entry can be =$F$2:$F$8. With this layout, Excel displays the seven entries in the order in which they appear in F2:F8. If you later edit a source item, the dropdown reflects the new text, but Excel does not automatically reposition that item; sort the range again.
Take care when sorting a source column that sits beside related data. If each item has associated information in neighboring columns, select the whole table or use its sort controls so rows stay together. Sorting only one column can separate labels from their corresponding details. If the source is a standalone list, sorting just that list is fine.
Keep an updating dropdown sorted with SORT
If the source values change regularly, a formula-generated helper list can avoid repeated manual sorts. In Microsoft 365 for Windows, SORT returns the items from a range in ascending order by default. Put the formula in an empty area with room for its results to spill; do not place it inside an Excel Table.
With the original choices in D2:D8 and the helper list beginning in F2, enter =SORT(D2:D8) in F2. The resulting list spills downward in alphabetical order, and the validation rule should refer to the helper output rather than the unsorted D2:D8. If one of the source values changes, the sorted results recalculate. The source range in this simple example is fixed at seven cells, so values added below D8 are not included unless the formula’s input range is expanded.
To make the input grow more conveniently, turn the source data into an Excel Table using Insert > Table, and give the table a clear name. For instance, assume a table named Table1 has a column named Item, and the formula will go in F2 outside the table. This Microsoft 365 formula creates an alphabetical list of distinct, nonblank values:
=SORT(UNIQUE(FILTER(Table1[Item],Table1[Item]<>"")))
As table rows are added, the structured reference expands with the Item column, and the helper list updates. FILTER excludes blank entries; UNIQUE removes repeated values; SORT arranges what remains. If repeated choices are meaningful in your situation, use a simple sorted list instead of UNIQUE. SORT, UNIQUE, and FILTER are available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.
Connect the spill range to Data Validation
A formula that returns multiple results is an array, while Data Validation’s List source is most dependable when it points to a range or a defined name. A practical approach is to create a name that refers to the helper formula’s spill range, then use that name as the validation source. This also avoids a direct cross-worksheet reference in the Source box, which can be problematic.
- With the sorted results spilling from F2 on a worksheet named Lists, open Formulas > Name Manager and choose New.
- Give the name a clear label, such as SortedItems, and set its Refers to entry to the spill range. With the helper list beginning at Lists!F2, use
=Lists!$F$2#. - Select the cells that need a dropdown and open Data > Data Validation. Set Allow to List and enter
=SortedItemsin Source. - Confirm the dialog and test a dropdown. Add or change a source value to confirm that the helper list and choices update as expected.
The # spill operator refers to the full dynamic array starting at F2, including the number of results currently returned. If the worksheet name contains spaces, Excel’s sheet reference needs apostrophes around it, for example a reference to a sheet named Lookup Lists. Create or edit the name through Name Manager rather than guessing at the syntax. If you use a different Excel version and the spill-reference method is unavailable, use a conventional fixed range or a compatible defined name.
Duplicates, blanks, and sorting details
Alphabetical order and uniqueness are different requirements. If the source contains Apple twice, sorting places both occurrences together; it does not remove either one. Use UNIQUE in a helper formula only if each choice should appear once. Conversely, if duplicate-looking choices have different meanings—for example, two products with the same display name but different codes—removing duplicates may hide a valid option. Consider using distinct labels, such as item name plus code.
Blank cells in a fixed source range can result in empty-looking entries in a dropdown. A formula-based list can filter blanks out, as in the Table1 example, but review what counts as blank if source cells contain formulas that return an empty string. Sorting also follows Excel’s sort behavior and regional settings; it should not be treated as a specialized dictionary or language-collation tool for every character or locale. For ordinary English names, ascending sort is generally the expected A-to-Z order.
Numbers and text can sort differently depending on how values are stored. A value that looks like a number but is stored as text may not sort like a numeric value. If the choices mix numbers, text, punctuation, and accented characters, inspect the resulting source order rather than assuming it matches a custom business sequence. Excel can also use custom sort orders for certain data tasks, but a validation dropdown simply follows the sequence in its source.
Common problems and fixes
- The dropdown is still in the old order: Check that the validation Source points to the sorted range or named spill range, not the original unsorted cells. Then reopen a dropdown to inspect the current choices.
- New entries do not appear: A fixed reference such as F2:F8 includes only those cells. Extend it, use an Excel Table as the input, or use a dynamic formula and spill reference.
- The formula shows a spill error: Clear any values or merged cells in the helper formula’s output area. Dynamic arrays need unobstructed cells below or beside the formula to return all results.
- Excel rejects the Source reference: Use a defined name for a list on another worksheet, and check that the name is spelled correctly. A name used as a list source should refer to the intended range or spill output.
- There are repeated or blank choices: Decide whether duplicates are meaningful, then use UNIQUE if they are not. Filter blank entries if they should not be selectable.
- Only part of a source table moved: Undo if possible and sort the complete related table, not an isolated column whose rows correspond to other data.
On Mac, the same basic method applies: sort the source on the Data tab, then use Data Validation to set Allow to List and identify the source. Ribbon placement and dialog appearance can vary slightly by Excel release. Excel for the web supports common sorting and validation tasks, but some name-management and advanced workbook features may be easier to configure in the desktop app. If a shared workbook behaves differently across platforms, confirm the validation source and test it in the version recipients will use.
Choosing between a fixed range and a helper formula
Use a manually sorted range when the options rarely change and simplicity matters most. It is easy to understand, works in older Excel versions, and does not require a spill formula or defined name. Its tradeoff is maintenance: users must keep the range sorted and expand the validation reference when the list grows.
Use a SORT helper when source values change and a continually alphabetized dropdown is useful. A table-backed formula is especially practical for a growing list, and UNIQUE can make it concise when duplicate entries are unwanted. The tradeoffs are compatibility with older Excel versions, the need for a clear output area, and a small amount of setup in Name Manager. For a sequence that is not alphabetical—such as Low, Medium, High—do not sort A to Z; arrange the source in the meaningful order you want users to see.
Frequently Asked Questions
Does Excel automatically alphabetize a data-validation list?
No. The dropdown follows the order of its source cells or typed entries. Sort the source range, or use a sorted helper list, to display choices A to Z.
Can I sort the dropdown without changing the original list?
Yes. Put a SORT formula in a separate helper area and point the validation rule to the helper results. This keeps the original source order intact while presenting an alphabetical dropdown.
Can Data Validation use SORT directly in the Source box?
Do not rely on entering a SORT array formula directly as the list source. Have SORT return results into worksheet cells, then use that spill range through a defined name or another supported range reference.
How do I keep new list items in alphabetical order?
Use a table for the source data and a SORT-based helper formula in a compatible Excel version. A manually sorted fixed range will not automatically include new rows or reposition newly entered items.
How can I remove duplicate choices?
Use UNIQUE in a helper formula before sorting, such as the Table1 example above. First confirm duplicates are not meaningful separate options; otherwise, give each choice a distinct label instead of removing repeated values.
Related
- Change Block Capitals to Lowercase in Excel
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel File Too Large? How to Make It Smaller in ExcelTo make a large Excel file smaller, first save a copy, then remove unused formatting and data, compress…
- How to Merge Cells in Zoho Sheet Without Losing DataTo merge cells in Zoho Sheet, select a contiguous range, then choose Format > Merge Cells and pick…
- How to Create Outlook Rules in Office 365To create an Outlook rule in Office 365, open Outlook on the web, go to Settings > Mail…
- Excel Slicer Search Box: How to Find ItemsIn Excel for Microsoft 365 on Windows, click the magnifying-glass icon in a slicer’s header to open its…
- Excel Column to CSV: Save One Column CorrectlyTo save one Excel column as a CSV, copy the column or the exact range you need into…
- Excel Merge Cells Shortcut: Keys and TipsIn Excel for Microsoft 365 on Windows, select the cells and press Alt, H, M, C in sequence…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.