Add a Drop-Down List in Excel or Google Sheets

To add a drop-down list, select the cell or cells where choices should appear, then create a list using Excel’s Data Validation settings or Google Sheets’ Dropdown feature. You will need the choices you want to offer; they can be typed into the settings or stored in cells. A drop-down makes entries more consistent, helps prevent spelling differences, and lets people fill in a sheet without guessing what format to use.

What You’ll Need

An Excel workbook or Google Sheets spreadsheet that you can edit.
The list of choices you want people to select, such as Open, In progress, and Done.
For a longer or frequently updated list, a column of source values in the spreadsheet. Keep each choice in its own cell and avoid blank cells within the list.

Step-by-Step

Decide where the choices will go. Open the workbook or spreadsheet and select the cell where the first drop-down should appear. To apply the same list to several cells, select the whole destination range first. In Excel, you can select a continuous range by dragging or by clicking the first cell and Shift-clicking the last. In Google Sheets, drag across the cells or enter the range in the name box above the grid. Make sure you are selecting the cells people will fill in, not the cells that contain your source choices.
Choose how to store the options. For a short, stable list, typing the choices directly into the validation settings is quick. For many options, or options that might change, enter one choice per cell in a spare column. For example, you could put Open, In progress, and Done in cells H2 through H4, with a heading in H1. Keep the source values clear and distinct; a blank source cell can create an empty-looking option. You can hide the source column later, but do not delete the values that the drop-down uses.
Create the list in Excel. In Excel for Microsoft 365 on Windows, select the destination cells and choose Data > Data Validation > Data Validation. In the Data Validation dialog, open the Settings tab, choose List from the Allow menu, and make sure In-cell dropdown is selected. If you want to type short options directly, enter them in the Source box separated by commas, such as Open, In progress, Done. If you prepared choices in cells, use the range selector beside Source and select those cells, then confirm the selection. Click OK to create the list. Excel for Mac has the Data Validation command on the Data tab as well; the dialog is similar, although the layout can differ slightly. In Excel for the web, look for Data Validation on the Data tab.
Create the list in Google Sheets. Select the destination cells, then choose Insert > Dropdown. A rules panel opens, where you can add or rename options and choose a display style. To use values stored in cells instead, change the criteria to Dropdown (from a range) and enter or select the source range. You can also open the same rule controls with Data > Data validation. Click Done to apply the rule. Google Sheets may show the choices as a colored chip, an arrow-style list, or plain text, depending on the display style you choose.
Check what happens when someone enters a different value. Excel’s Data Validation dialog includes an Error Alert tab. Keep the alert enabled if you want Excel to warn or block people who type something outside the list; the Stop style prevents an invalid value from being accepted through ordinary entry. In Sheets, use the invalid-data setting in the validation rule to choose whether to reject an entry or show a warning. Reject input is useful for controlled categories, while a warning can be appropriate when people need flexibility. Add a short instruction or error message if the setting offers one, so users know what to do.
Test the drop-down in a destination cell. Click one of the cells you set up. In Excel, a small arrow appears when the cell is selected; click it and choose an option. In Sheets, click the cell or its dropdown indicator and select a choice. Also try typing an allowed option and, if you enabled strict validation, a value that is not on the list. Confirm that valid entries work and that the invalid-entry behavior matches your intention. Testing one cell before applying a rule to a large form can reveal a source-range mistake early.
Apply the rule to other cells if needed. If you selected a range before creating the rule, the list should already be available throughout that selection. If you made it for only one cell, copy that cell and paste it into other destination cells in Excel, or use the fill handle to extend the rule. In Sheets, copy the validated cell and paste it to the other cells, or edit the rule and adjust its apply-to range. Check that copying the rule has not overwritten existing data. If the cells need different lists, create separate validation rules for each group instead of copying one rule everywhere.
Update or remove the list when the sheet changes. In Excel, select a cell with the rule and reopen Data > Data Validation > Data Validation to edit its source or alert. To remove validation, use the same dialog and choose Clear All, then click OK. In Sheets, select a cell with the rule and choose Data > Data validation to edit or remove the rule from the panel. If the rule covers a range, check its apply-to range before making changes so you do not unintentionally alter unrelated cells.

Common Mistakes

Selecting the wrong cells: It is easy to highlight the source list rather than the cells that need the menu. Before creating a rule, check the selected range and ask whether those are the cells users will fill out. If the arrow appears in the wrong place, undo the change where possible, select the correct destination cells, and apply the validation rule again.

Entering a source list with the wrong separators: A typed list in Excel is separated using the list separator expected by your regional settings. Commas work in many installations, but some use another separator. If Excel treats the entire text as one choice, use a range of cells for the source list instead. This is also easier to maintain when choices contain punctuation or when the list grows.

Leaving gaps or inconsistent values in the source: Blank cells can result in an empty choice, while duplicate values make the menu harder to scan. Check spelling, capitalization, and spacing in the source values. For example, Open and open may be confusing even when a spreadsheet treats them as equivalent for some operations.

Expecting validation to clean up old entries: Adding a rule does not necessarily replace values already in destination cells. Existing entries may remain even if they are not on the new list. Review those cells after applying the rule, and correct any old values that do not match the permitted choices.

Deleting the source values: A rule based on a cell range still depends on that range. Clearing or moving the source cells can leave the menu empty or incomplete. Keep the source list in a stable place, and check which cells the rule refers to before reorganizing the sheet.

Tips

Use direct typed options for a tiny list that rarely changes. Put options in cells when the list is long, shared across several rules, or likely to be revised. A separate, clearly labeled section or sheet helps other editors understand what feeds the drop-down. In Excel, a source range on another worksheet is easier to manage when you create and use a named range; the Data Validation source field does not accept every cross-sheet reference in the same way as an ordinary cell formula.

Keep option names brief and mutually clear. If a choice needs an explanation, put that explanation in a nearby note or instructions cell rather than making every menu item a paragraph. Consider whether a blank selection is allowed: it can be useful for an unanswered form, but it can also make an incomplete record easy to miss. Use a heading or nearby label to explain what the field means, such as Status or Department.

For a frequently changing source list in Excel, remember that a fixed range does not automatically include new values typed below it. Extend the validation source when you add choices, or use a suitable named range that expands with your data. In Sheets, a range-based dropdown reads from its specified cells, so extend that range when you add more choices. After changing either source, test the destination menu and check that the new options appear in the intended order.

Use validation in combination with clear sheet design, not as a substitute for it. Protecting cells may be appropriate if users should not edit the source choices or formulas elsewhere in the workbook, but protection and validation are different features. Before sharing a form, test it as a regular user would: select options, try entering a custom value, and confirm the sheet remains understandable on the screen size people are likely to use.

Troubleshooting

The drop-down arrow is missing: First click the validated cell; in Excel the arrow generally appears only while the cell is selected. If it still does not appear, check that you created a List validation rule in Excel or a dropdown rule in Sheets and that the destination cell is included in the rule’s range. In Excel, reopen the validation dialog and confirm In-cell dropdown is selected. In Sheets, inspect the rule panel and its apply-to range. Also check whether the cell is protected or whether you have permission to edit the spreadsheet.

The menu shows one long choice instead of several: In Excel, the separator in the Source box may not match your regional list separator. Replace the typed list with values stored in separate cells and select that range as the source. In Sheets, verify that you added separate options rather than typing several choices into one option field. Edit the rule, split the values into individual entries, and click Done.

Some choices are missing: Check that the source range includes every option and does not stop one row too early. If you added choices after setting up the rule, expand the range or update the typed list. In Excel, make sure the source cells have not been moved, cleared, or placed on a different sheet without updating the rule. In Sheets, reopen the rule and confirm the range shown under Dropdown (from a range) covers the new cells.

People can still type other values: The rule may be set to show a warning rather than reject invalid data, or Excel’s error alert may be turned off or set to a non-blocking style. Edit the rule and choose the strictest option that fits your workflow. Remember that pasted data and certain editing methods can behave differently from choosing an item in the menu, so review important entries even when validation is enabled.

The list works in one cell but not another: The rule may apply to only part of the intended range, or the second cell may have a different validation rule. Select the affected cell and inspect its rule. Expand the apply-to range or copy the correctly validated cell to the missing cells, then test again. If different sections need different choices, keep their ranges and rules separate.

Frequently Asked Questions

Can I make a drop-down list with choices from another sheet?
In Google Sheets, a Dropdown (from a range) rule can use a source range on another sheet by entering or selecting its spreadsheet range. In Excel, use a named range for choices stored on a different worksheet, then use that name as the validation source. This avoids problems with direct cross-sheet references in the validation dialog.

Can I allow more than one selection in a cell?
Standard Excel Data Validation lists and Google Sheets dropdowns select one option per cell. Excel does not provide multi-select through the standard validation menu. A multi-select setup in Excel typically needs VBA, while Sheets would need a different workflow or script. Consider using separate columns or checkboxes if multiple choices are required.

Will the drop-down update when I add a new option?
Only if the source range includes the new cell or the rule uses a source that expands. A fixed range will not automatically grow just because you type below it. Update the rule’s source range and test the menu after adding options. For a short typed list, edit the list in the validation settings.

Can I copy a drop-down to another cell?
Yes. Copy a validated cell and paste it into the destination cells, or extend the rule’s range in the validation settings. Check whether the destination cells already contain data, because pasting may replace cell contents as well as the validation rule.

Can I remove a drop-down without deleting the cell’s value?
Yes. Remove the validation rule rather than clearing the cell. In Excel, use Data > Data Validation > Data Validation, choose Clear All in the dialog, and confirm. In Sheets, select the cell and remove its rule from Data > Data validation. The existing cell value should remain; check the result before removing rules from a large range.

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.