Google Sheets Drop-Down Lists: How to Create and Edit Them

Google Sheets drop-down lists let you choose from approved options in a cell instead of typing each value by hand. They help keep entries consistent in forms, trackers, and shared spreadsheets. You can enter options directly or pull them from a range of cells; you’ll need permission to edit the sheet to create or change a list.

What You’ll Need

• A Google account and a spreadsheet you can edit.
• The options you want people to choose, either as a short list or in cells on the sheet.
• A current web browser, or the Google Sheets app. Menu labels and layout can vary slightly between the web and mobile versions.

Step-by-Step

1. Select the cell or cells. Open your spreadsheet and click the cell where you want a drop-down. To apply one list to several cells, drag to select a range, such as B2:B50. You can also select an entire column, but consider whether blank header or note cells should have the rule.
2. Open the drop-down settings. In the desktop version, choose Insert > Drop-down. Alternatively, use Data > Data validation > Add rule. If the selected cells already have a rule, choosing a cell and opening Data > Data validation lets you inspect or edit it.
3. Choose how to provide the options. In the rule panel, use the criteria selector to choose Drop-down to type options yourself, or Drop-down (from a range) to use values already in cells. The wording or placement may differ slightly in the mobile app.
4. Enter options directly, if appropriate. Add each choice as a separate item, such as “Not started,” “In progress,” and “Complete.” Use the controls to add more items, remove an item, or change its order. You may also assign colors to options to make statuses easier to scan; color is visual and does not change the underlying value.
5. Set a range as the source, if preferred. For a range-based list, enter or select the cells containing your choices, for example Lists!A2:A5. Keep the source values in one column or row and avoid including a header unless you want it to appear as a selectable choice. Refer to a range in the same spreadsheet; sheet names with spaces may be quoted automatically by Sheets.
6. Decide how invalid entries are handled. In the rule’s advanced options, choose whether to reject an entry that is not one of the listed choices or show a warning and allow it. Rejecting entries keeps the data restricted to the options. A warning is useful when people may need to add an exception. Turn on the option to display the drop-down in the cell if it is available.
7. Save the rule. Click Done. The selected cells now have a drop-down control, often shown as an arrow when you select a cell. Click it and choose an option to test that the list works.
8. Check the result and apply the rule elsewhere. Try selecting a choice and, if you chose warning mode, type a value that is not on the list to confirm the behavior. To reuse the rule, copy a cell with the drop-down, select the destination cells, then use Edit > Paste special > Data validation only if that option is available. This applies the rule without replacing the destination cells’ contents. You can also select a larger range before creating the rule.
9. Edit or remove a list later. Select a cell governed by the rule and open Data > Data validation. Change the criteria, options, source range, or invalid-data behavior, then save. To remove the rule, select it in the validation panel and choose Remove rule. Removing a rule does not necessarily erase values already selected in the cells.

Common Mistakes

• Including a heading in the source range: If “Status” is in the selected range, it will appear as a choice. Start the range below the heading, such as A2:A5.
• Using the wrong source cells: Check that the range points to the intended sheet and includes every option. A misspelled sheet name or an incomplete range can make the list appear empty or omit choices.
• Expecting warning mode to block typing: A warning allows non-list values. Choose the reject option when entries must match the list.
• Overwriting existing data while copying: A normal paste can replace cell contents. Use the data-validation-only paste option where available, or create the rule on the destination range directly.
• Assuming colors are data: Option colors help people read a sheet but do not create a separate status or change what text is stored in the cell.

Tips

• Keep option labels short, distinct, and consistent. Similar choices such as “In progress” and “In Progress” may be treated as different text values.
• Put frequently maintained choices in a clearly labeled section or a dedicated sheet. Update the source range when you add options, and check whether the rule’s range includes the new cells.
• Use a range-based list if options may change over time; edit the source cells rather than rebuilding each drop-down.
• Apply one rule to the full intended input range rather than setting up each cell separately. Avoid applying it to headers or instructions that should remain ordinary text.
• For shared spreadsheets, explain what each choice means and whether exceptions are allowed. This reduces inconsistent entries and confusion.

Troubleshooting

• No drop-down appears: Select the cell and open Data > Data validation to check whether a rule is attached. Confirm you selected the intended range, then add or reapply the rule.
• The list is empty or missing choices: Review the source range and confirm it contains the values you expect. Make sure it does not point to blank cells or the wrong sheet. If you added options, expand the range if it does not include them.
• You can see the list but cannot change it: You may have view-only access, or the sheet or range may be protected. Ask the owner for editing access or permission to change the protected area.
• An invalid value is accepted: Edit the validation rule and choose the setting that rejects input rather than merely showing a warning. Confirm you changed the rule for the cell you are testing.
• A pasted value removes or ignores the list: Reapply the validation rule to the destination cells. When copying rules, use Paste special > Data validation only rather than a standard paste.
• The menu differs on a phone or tablet: Select the cell and look for data validation or drop-down controls in the app’s menus. If the setting is hard to find, open the spreadsheet in a desktop browser, where the full validation panel is easier to use.

Frequently Asked Questions

Can I make a drop-down that uses values from another sheet?
Yes. Choose Drop-down (from a range) and specify the cells containing the options. Check the sheet name and range if the choices do not appear.

Can a cell have more than one selected option?
Google Sheets drop-downs are generally for choosing an individual option per cell. For multiple selections, use separate cells or another data-entry method; do not assume a standard drop-down will store several choices.

Can I change the options without rebuilding the list?
Yes. For a range-based list, edit the source cells. If you add items outside the original range, update the rule so it includes them.

How do I remove a drop-down but keep the cell’s value?
Select the cell, open Data > Data validation, and remove the rule. The existing cell value usually remains, though you can clear it separately if needed.

Why can someone type a value that is not listed?
The rule may be set to show a warning instead of rejecting invalid data, or the cell may not have the rule. Check the rule’s invalid-data setting and the selected cell range.

Do drop-downs work for people viewing a shared sheet?
They can select options if they have permission to edit the relevant cells. Viewers cannot change cell contents, and protected ranges may restrict editors too.

Related

• Share an Excel Report Without Exposing Sensitive Data
• How to Find Your iPhone When It’s Dead
• Fix “Computer Is Configured to Require a Password”