Excel Dropdown List: How to Update It

To update an Excel dropdown, select a cell that has the list, go to Data > Data Validation, and change the entries in the Source box. If the list comes from worksheet cells, edit those cells or expand the source range; if you want new choices to appear automatically, base the list on an Excel Table.

How Excel dropdown lists work

An ordinary Excel dropdown is usually a Data Validation list. The validation rule controls which entries a user can select or type into a cell, while its Source setting tells Excel where the allowed choices come from. That source can be a short list typed directly into the dialog or a range of cells in the workbook.

These are separate parts of the setup. Changing a cell in a source range changes the available choice, but it does not necessarily change which cells have the dropdown rule. Conversely, adding validation to a cell does not create or update the source list. Keeping the source choices in a clear, dedicated column makes later changes easier and reduces the chance of editing the wrong rule.

For Microsoft 365 on Windows, the main route is Data > Data Validation. In the dialog, the Settings tab contains the Allow menu and the Source field. Choose List to work with a dropdown. The In-cell dropdown option should be selected if you want the arrow to appear when a validated cell is active.

Change choices in an existing dropdown

First click a cell whose dropdown you want to change. If you do not know where its choices come from, open Data > Data Validation and inspect Source. A source shown as a series of items separated by commas is a manually typed list. A source that looks like a cell reference points to worksheet cells; a name such as Choices refers to a defined name.

Select a cell with the dropdown. If you need to update several cells, select the intended cells together, provided they use the same validation rule.
Open Data > Data Validation. If Excel reports that the selection contains more than one kind of validation, inspect and update the cells separately.
On the Settings tab, check that Allow is set to List, then edit the Source field.
Click OK and open the dropdown to confirm that the choices are correct.

For example, a manually entered Source might contain Open,In progress,Complete. Add a new choice by changing it to Open,In progress,Waiting,Complete. A typed list is convenient for a few stable options, but it is less suitable for long lists or choices that change often. The source has a 255-character limit when entered directly in the Data Validation dialog, so a cell range is a better option for a longer list.

Editing validation changes the rule for the selected cells; it does not automatically update every dropdown elsewhere in the workbook. Some versions show an option to apply the changes to other cells with the same settings. Use it only if you want those matching cells to share the revised rule. Otherwise, update the other cells or ranges deliberately.

Update a dropdown that uses a cell range

If Source contains a reference such as =$H$2:$H$5, Excel reads the choices from those cells. You can change an existing choice by editing one of the source cells. To add a choice, enter it in an unused cell and revise the validation source so the reference includes it. For example, if the current source covers H2:H5 and the new item is in H6, change the source range to include H6.

Be careful not to overwrite a choice that is still needed by another dropdown. It is often clearer to place source values in a labeled column on a separate worksheet, with one choice per row and no blank rows inside the list. You can then expand the range as choices are added. If you delete a source value, Excel removes it from future dropdown selections, but it does not erase that value from cells where someone selected it earlier.

When you select a range in the Source field, Excel may show an absolute reference with dollar signs. Those signs keep the reference fixed rather than changing it relative to the location of each validated cell. If the list is on another worksheet, a direct cross-sheet reference can be awkward or rejected in the Data Validation dialog. A defined name is a reliable way to refer to that list: create a name for the source range and enter that name in Source, preceded by an equals sign.

Make the dropdown grow with an Excel Table

A Table is useful when you expect to add choices regularly. When you type a new item in the row immediately below a Table, Excel typically expands the Table to include that row. A normal fixed reference such as =$H$2:$H$5 does not necessarily expand with it, so connect the validation rule to the Table column through a defined name.

Put the options in one worksheet column, with a heading such as Option and one choice per row.
Select the heading and choices, then choose Insert > Table. Confirm that the table has headers.
On the Table Design tab, note the Table Name, and use the column heading as the column name. For example, the table might be named Table1 and its choices column Options.
Go to Formulas > Name Manager > New. Give the name a simple label such as Choices. In Refers to, enter the structured reference for the table column. For a table named Table1 with choices in its Options column, the reference is =Table1[Options].
Select the cells that need the dropdown, choose Data > Data Validation, set Allow to List, and enter =Choices in Source. Click OK.

Now add a new option in the next row of the Table and check the dropdown. The defined name refers to the Table column, which expands as the Table grows. Table names and column headings must match the workbook exactly; if you rename either one, check the defined name in Formulas > Name Manager. Remove blank or unwanted entries from the Table if you do not want them to appear as selectable choices.

Use a list stored on another worksheet

Keeping choices on a separate worksheet helps distinguish the workbook’s data from its entry area. It is also common to hide that worksheet after the list is set up. To connect it, open Formulas > Name Manager > New, assign a name such as DepartmentList, and set Refers to as the source range on the other sheet. Then enter =DepartmentList in the Data Validation Source field.

Use a name that begins with a letter or underscore and avoid spaces; names cannot be identical to a cell reference. If you use a name with multiple words, an underscore is a straightforward separator. After creating it, test the dropdown in a cell on the entry sheet. Hiding the source worksheet does not hide the dropdown choices from someone who can use the workbook, and worksheet protection is not a substitute for access control.

What happens to existing entries

Updating the source changes what a user can choose in the future. It does not automatically replace values already stored in validated cells. For example, if you remove the choice Waiting from the source, cells that already contain Waiting keep that value. The cell may then contain an entry that is no longer allowed by the current rule.

To find outdated values, compare the existing entries with the revised list or use Excel’s validation error alerts when users edit cells. In Data > Data Validation, the Error Alert tab lets you control how Excel responds to invalid entries. A Stop alert blocks an invalid value entered through the cell interface; Warning and Information alerts allow the user to proceed after a prompt. These alerts do not clean up old values automatically, so review existing data after a substantial list change.

Common problems and how to fix them

The new choice does not appear: Check the Source field and make sure the cell or range containing the new item is included. If the source uses a defined name, verify it in Formulas > Name Manager.
The dropdown arrow is missing: Select a cell with the rule and confirm that Allow is List and In-cell dropdown is selected. The arrow normally appears when the cell is selected, rather than remaining visible at all times.
There is a blank choice: Look for empty cells inside the referenced range or blank rows in the source Table. Remove them or adjust the source to cover only the intended choices.
Some cells changed but others did not: The cells may have separate validation rules or may not have been included in the selection. Check each relevant range rather than assuming every dropdown in the workbook shares one source.
Excel rejects a source on another sheet: Define a name that refers to the source range, then use the name in the Source field with an equals sign at the beginning.
Old values remain after an option is removed: This is expected. The dropdown source controls future selections; existing cell contents must be reviewed or replaced separately.

Also check for invisible spaces in source values. For instance, a choice with a trailing space can look identical to one without it while behaving like a different text value in comparisons. Keep spelling and spacing consistent, and avoid duplicate choices unless the duplication is intentional.

Choose the right update method

The simplest setup is not always the best one. Choose based on how often the choices change and who maintains the workbook:

Typed list: Best for a few short choices that rarely change. It takes little setup, but every edit requires reopening Data Validation and the source is limited to 255 characters.
Fixed cell range: Best for a list maintained in worksheet cells that changes occasionally. You can edit source values directly, but must expand the reference when the list grows past its original end.
Table column with a defined name: Best for a list that will grow over time. Adding a new Table row can extend the source without revising a fixed range, though the initial setup has more steps.

For a workbook used by several people, add a clear heading to the source column and keep it in a predictable location. This makes it easier for another editor to understand where the dropdown choices come from. If the choices depend on another selection, such as choosing a city after choosing a country, that is a dependent dropdown and needs a more involved design; changing a basic list source alone will not create that relationship.

Excel for Mac and Excel for the web

In Excel for Mac, the basic workflow is similar: select the validated cells and choose Data > Data Validation, then edit the list source. Ribbon labels and dialog layout can vary slightly by release, so look for the Data Validation command on the Data tab. The Table, Name Manager, and Data Validation features are also available in current desktop Excel for Mac, though the exact placement of commands may differ from Windows.

Excel for the web can handle common dropdown lists and offers Data Validation controls, but some workbook features and editing workflows can differ from desktop Excel. If a named range or a more elaborate source is difficult to inspect in the browser, open the workbook in the desktop app and use Formulas > Name Manager and Data > Data Validation there. Save the workbook after editing, then reopen it in the web version to confirm the choices appear as expected.

Frequently Asked Questions

How do I add a new item to an Excel dropdown?
Select a cell with the dropdown and inspect Data > Data Validation > Source. Add the item to the typed list, or put it in the source cells and expand the referenced range. If the source uses a Table column and defined name, add a row to the Table.

Why is my Excel dropdown not updating?
The new item may be outside the source range, or the dropdown may use a different source or defined name than expected. Check the Source field for a representative cell, then verify the named range or Table column if applicable.

Can I update every dropdown in a workbook at once?
Not automatically in every workbook. A validation change applies to the selected cells, and separate dropdowns may have different rules. If the cells share identical settings, Excel may offer to apply the edit to other cells with the same settings; otherwise, update the relevant ranges individually.

Can I make an Excel dropdown list expand automatically?
Yes. A practical approach is to store the choices in an Excel Table, define a name that refers to its choices column, and use that name as the Data Validation source. Adding a row to the Table can then add a choice to the list.

Does removing a dropdown choice remove it from cells that already use it?
No. Removing a source item affects future selections, not existing cell contents. Review the worksheet for values that are no longer valid and replace or clear them as needed.

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.