What a drop-down list does and why you'd use one

A drop-down list in Google Sheets is a cell that shows a small arrow when you click it. Click the arrow and you see a list of choices you set in advance. The person filling out the sheet picks from those choices instead of typing anything they want. This prevents typos, keeps data consistent, and makes filling forms faster.

Drop-downs are most useful when you have a set of correct answers — like "Yes / No / Maybe", or "New York / New Jersey / Connecticut", or "Approved / Pending / Rejected". If someone needs to enter a date or a number that could be anything, a drop-down gets in the way. But when you need the same few options repeated down a column, drop-downs save time and catch mistakes before they happen.

Key Takeaways

  • Select the cell or cells where you want the drop-down, then open the Data Validation menu from the Data menu.
  • Choose "List of items" and type your options separated by commas, or point to a range of cells that already contain your list.
  • You can set the drop-down to reject entries that don't match your list, or just show a warning and let the person type anyway.
  • Copy a cell with a drop-down to other cells, and the drop-down copies with it — you don't have to set it up again for each row.

How to set up a drop-down in a single cell

Start by clicking the cell where you want the drop-down to appear. If you want drop-downs in multiple cells in the same column, select all of them at once by clicking the first cell, holding Shift, and clicking the last cell. You can also click a column header to select the entire column.

Open the Data menu at the top of the sheet and click Data validation. A panel opens on the right side. In the dropdown that says "Criteria", select List of items. Now type the options you want to appear in your drop-down, separated by commas. For example: Approved, Pending, Rejected. Do not add spaces after the commas unless you want spaces in your list.

At the bottom of the panel, you will see two checkboxes. The first, "Show dropdown arrow", is already checked — leave it that way so people can see the arrow. The second, "Reject input", controls what happens if someone tries to type something that is not on your list. Check it if you want to force people to pick from your list only. Leave it unchecked if you want to allow other entries but still show the list as a suggestion.

Click Done. The cell now has a small arrow in the corner. Click it to see your list.

Using a range of cells instead of typing the list

If your list of options already exists somewhere else in the sheet — maybe in column A, rows 1 through 5 — you can point the drop-down to that range instead of typing the options again. This is useful when the same list is used in multiple drop-downs, because if you change the list once, all the drop-downs update automatically.

Select the cell or cells that will have the drop-down. Open Data > Data validation. In the Criteria dropdown, select List from a range. Click in the text box below it and type the range where your list lives — for example, A1:A5 or States!B2:B51 if the list is on a different sheet called States. Click Done.

Now if you add a new option to your original list, the drop-down automatically includes it. If you remove an option, it disappears from the drop-down too. This keeps everything in sync without extra work.

Copying a drop-down to many cells at once

Once you have set up a drop-down in one cell, you can copy it to other cells without setting it up again. Click the cell with the drop-down you want to copy. Press Ctrl+C (or Cmd+C on Mac) to copy it. Select the range where you want the drop-down to appear — for example, click the first cell, hold Shift, and click the last cell. Press Ctrl+V (or Cmd+V) to paste. The drop-down appears in all the selected cells with the same list of options.

This works even if you copy the cell to a different sheet. The drop-down and its list travel with it. If you used a range like A1:A5 instead of typing the list, the range reference updates automatically if you paste to a different location — so if you copy a drop-down from row 1 to row 2, it adjusts to point to the right cells.

What to do if someone enters data that breaks the drop-down

If you left "Reject input" unchecked, a person can type something that is not on your list. The drop-down still appears, but they are not forced to use it. If you later want to enforce the list strictly, you can edit the drop-down: click the cell, open Data > Data validation, check "Reject input", and click Done. From then on, anyone who tries to type a value not on the list will see an error message.

If data is already in the cell and does not match the list, the cell keeps that data even after you turn on "Reject input". The restriction only applies to new entries or edits. If you need to clean up old data, you will have to fix it by hand or use Find and Replace to change it to a value that matches the list.

Making the drop-down show a message when someone clicks it

You can add a title and a message that appears when someone clicks the cell with the drop-down. This is helpful if you want to explain what the list is for or give instructions. Click the cell with the drop-down, open Data > Data validation, and look for the section labeled "Appearance". Type a title in the first box — for example, "Status". Type your message in the second box — for example, "Choose the current status of this request".

When someone clicks the cell, they will see your title and message before they see the list. This is especially useful in shared sheets where multiple people are filling in data and might not know what each column means.

Troubleshooting common drop-down problems

If the drop-down arrow does not appear, open Data > Data validation and check that "Show dropdown arrow" is checked. If it is, the drop-down is set up correctly but the arrow might be hard to see — click the cell and look for a small arrow on the right side.

If you copied a drop-down and the list changed or disappeared, the range reference may have broken. Click one of the cells with the broken drop-down, open Data > Data validation, and check the range in the text box. If it points to the wrong place, fix it by typing the correct range. If you used "List of items" and the list is now empty, you may have accidentally deleted the text — type the options again or switch to "List from a range" and point to where the list actually is.

If you are trying to use a range from a different sheet and it is not working, make sure you typed the sheet name correctly. The format is SheetName!A1:A5 with an exclamation mark between the sheet name and the range. If the sheet name has spaces, put it in single quotes: 'Sheet Name'!A1:A5.

Frequently Asked Questions

Can I have a drop-down that shows different lists depending on what is in another cell?

Yes, but it requires a more advanced setup called dependent drop-downs. You create a helper column with formulas that filter the list based on another cell's value, then point your drop-down to that filtered list. This is beyond the basic steps above, but Google Sheets documentation and tutorials cover it if you search for "dependent drop-down lists".

What happens if I delete the range that my drop-down points to?

The drop-down breaks and shows an error. If you need to move or delete the list, edit the drop-down first to point to a new range, or switch to "List of items" and type the options directly into the validation rule instead of referencing a range.

Can I sort or filter a sheet that has drop-downs?

Yes. Drop-downs stay attached to their cells when you sort or filter. The data moves, but the drop-down moves with it. If you sort by a column with drop-downs, the drop-downs stay in the same rows as their data.

How do I remove a drop-down from a cell?

Click the cell, open Data > Data validation, and click the trash icon in the bottom right of the panel. The drop-down is deleted, but the data in the cell stays. If you want to remove drop-downs from many cells at once, select all of them, open Data validation, and click the trash icon once.

Can I make a drop-down that lets someone pick multiple items from the list?

The standard drop-down only lets you pick one item. If you need multiple selections, you can use a checkbox instead — open Data > Data validation, choose "Checkbox" as the criteria, and each cell becomes a box that can be checked or unchecked. This is different from a drop-down but solves the same problem in a different way.