Dropdown List in Google Sheets: How-To

Dropdown lists in Google Sheets turn a plain cell into a clickable menu of preset options. They prevent typos, speed up data entry, and make filtering and charts more accurate. This guide walks you through creating a dropdown from a list of items you type yourself, from a range of cells, and with color-coded options—plus how to edit or remove them later.

What You’ll Need

• A Google account (free) and access to Google Sheets.
• A spreadsheet open with the cells where you want the dropdowns.
• Optional: a list of items already typed in a column or row (e.g., “Yes”, “No”, “Maybe”) if you want the dropdown to reference that range.

Step-by-Step

Method 1: Create a Dropdown from a Typed List

1. Open your Google Sheet and select the cell (or range of cells) where you want the dropdown.
2. Click Data in the top menu, then choose Data validation.
3. In the panel that opens, under Criteria, click the dropdown arrow and select Dropdown (not “Dropdown from a range”).
4. You’ll see a list with one option row. Type your first item (e.g., “Pending”). Click Add another item to add more options. Repeat until you’ve added all choices.
5. Optionally, click the color palette next to each item to assign a color to that choice (this will show as a colored chip in the cell).
6. Decide what happens with invalid data: choose Show warning (allows typing anything but flags it) or Reject input (blocks anything not on your list).
7. Click Save. Your cell now has a dropdown arrow on the right side.

Method 2: Create a Dropdown from a Range of Cells

1. Type your list of options in a column or row (e.g., in cells A1:A5). You can hide this range later if you don’t want it visible.
2. Select the cell or range where you want the dropdown.
3. Go to Data > Data validation.
4. Under Criteria, choose Dropdown from a range.
5. In the box that appears, either type the range (e.g., Sheet1!A1:A5) or click the grid icon to select it directly on the sheet.
6. Set the invalid-data behavior and click Save.

Method 3: Use a List of Items from a Range (Advanced)

If you have a long list that might grow, use a named range or a table. Go to Data > Named ranges, create a name for your list (e.g., StatusList), then in Data validation choose Dropdown from a range and type the named range. This way, adding items to the named range automatically updates the dropdown.

Editing or Removing a Dropdown

1. Select the cell(s) with the dropdown.
2. Go to Data > Data validation.
3. To edit, change the options or criteria, then click Save.
4. To remove, click Remove validation at the bottom of the panel.

Common Mistakes

• Selecting the wrong range: When using “Dropdown from a range,” make sure the range includes only the option cells, not headers or blank cells—blanks will appear as empty options.
• Not applying to all needed cells: If you select only one cell, the dropdown applies only there. To apply to a whole column, select the entire column (e.g., click the column letter) before opening Data validation.
• Forgetting to update the range: If you add new options to the source range, the dropdown won’t update unless you used a named range or the range is large enough to include the new cells.
• Using “Dropdown” vs “Dropdown from a range”: The former stores options in the validation itself; the latter references another cell range. If you delete the source range, the dropdown breaks.

Tips

• Use color-coded options (e.g., green for “Done”, red for “Overdue”) to make status columns instantly scannable.
• For a dropdown that allows users to type a custom value, choose Show warning instead of Reject input.
• To create a dropdown that pulls from a list on another sheet, reference it like Sheet2!A1:A10 (include the sheet name and exclamation mark).
• If you want the dropdown to appear empty until selected, leave the cell blank—the arrow still shows on hover.
• Use data validation with a dropdown for columns that will be used in filters or pivot tables; consistent values make those features work better.

Troubleshooting

Dropdown not showing the arrow? Make sure the cell is not protected. Go to Data > Protect sheets and ranges and check if the range is protected. If so, remove protection or edit permissions.

Dropdown options are blank? This usually happens when the source range includes empty cells. Check the range in Data validation and adjust it to exclude blanks.

Can’t type anything in the cell? If you set “Reject input” and the typed value isn’t in the list, it will be blocked. Change to “Show warning” if you need flexibility.

Dropdown disappeared after copying? When you copy a cell with a dropdown, the validation usually copies too. If it doesn’t, re-apply the validation to the new cells.

Error: “The range is not valid”? This occurs when the range you typed doesn’t exist or is misspelled. Double-check the sheet name and cell references.

Frequently Asked Questions

Can I make a dropdown that allows multiple selections? No, standard data validation in Google Sheets allows only one value per cell. For multi-select, you’d need a script or an add-on.

How do I create a dropdown with a search box? Native dropdowns don’t have search. You can use a workaround: create a cell with a data validation dropdown and use a separate cell with a formula that filters the list based on typing, but this is complex. Alternatively, use Google Sheets’ built-in “Dropdown chips” feature (available in some regions) which offers search.

Can I use a dropdown to show different values than what’s stored? Not directly. The dropdown stores the displayed text. If you need a code behind a label, use a separate lookup column with VLOOKUP.

Will dropdowns work on mobile? Yes, the Google Sheets mobile app supports dropdowns. Tap the cell and select from the list.

How do I make a dropdown list that updates automatically when I add new items? Use a named range or a table (Ctrl+T) and reference that in the data validation. New rows added to the table will appear in the dropdown.

Can I copy a dropdown to multiple cells at once? Yes, select the cell with the dropdown, copy it (Ctrl+C), then select the target range and paste (Ctrl+V). The validation will be applied to all pasted cells.

Related

• Fix DNS Server Isn’t Responding in Windows 11
• Fix Wi-Fi Keeps Disconnecting on Windows 11
• ERR_CONNECTION_RESET Fix in Chrome