How to Add a Drop-Down List in Google Sheets and Excel

How To Add A Drop Down List In Google Sheets And Excel Featured

If you want to clean up data entry on your spreadsheets, you’ll definitely want to learn how to add a drop-down list in Google Sheets and Excel. This lets you restrict a user’s data entry option in a given cell to a set of options. You won’t have to worry about misspellings or someone not knowing what to enter. Plus, it helps make entering information much faster.

Good to know: you can automate your spreadsheets, too, with formulas.

There are multiple ways to create your list of values for your drop-down list in Google Sheets. Some users prefer to create a hidden column with the values in their existing sheet or in another sheet in the same file. Others prefer adding the items later in the process. Any of these options work just as well for creating your list.

  1. Select a single cell or range of cells where you want to add your drop-down list.
  2. Select the “Data” menu.
How To Add A Drop Down List In Google Sheets And Excel Google Data
  1. Select “Data validation.”
How To Add A Drop Down List In Google Sheets And Excel Google Validation
  1. Verify the range is correct. You can adjust it manually or exit the window and highlight your desired range first.
  2. Decide on your criteria. By default, it’s “List from a range.” However, you can also use:
How To Add A Drop Down List In Google Sheets And Excel Google Criteria
  • List of items – add each entry into the box separated by commas.
  • Number – only numerical input is allowed, butyou can specify the range.
  • Text – only accepts text input based on your criteria, such as containing certain letters or words or just URLs or email addresses.
  • Date – accepts only dates.
  • Custom formula – create a custom formula for the drop-down list.
  • Checkbox – adds a simple checkbox, True/False, or custom values.

For this example, I’m using “List from a range.”

  1. Enter your data range that includes your list values. I have mine a few columns over from my main data so that I can hide the column later.
How To Add A Drop Down List In Google Sheets And Excel Google List Range
  1. Select how to handle invalid input. Choose between “Show a warning,” which warns users to use a valid item before letting them enter whatever they want, and “Reject input,” which rejects anything outside of the list.
  2. Click “Save.”

You should now see drop-down arrows in any cells where you chose to add the list.

How To Add A Drop Down List In Google Sheets And Excel Google Listarrow

Users just click the arrow to select an option.

How To Add A Drop Down List In Google Sheets And Excel Google Final

I set my drop-down list to reject input. If someone enters any other value, they’ll see a warning, and their entry will be rejected.

How To Add A Drop Down List In Google Sheets And Excel Google Warning

If you ever need to remove or edit the data validation, highlight the cell or range with the drop-down list and go to “Data -> Data validation.” Make your changes or select “Remove validation.”

If you want to further customize your lists, you can automatically change the color of the cell based on the chosen input.

If you prefer using Excel over Google Sheets, you can still use drop-down lists. The process is similar in Excel, but the data validation options look a little different.

  1. Highlight the cell or range where you would like to add your drop-down list.
  2. Select the “Data” menu.
How To Add A Drop Down List In Google Sheets And Excel Excel Data
  1. Select “Data validation.”
How To Add A Drop Down List In Google Sheets And Excel Excel Validation
  1. Just as with Google Sheets, you have multiple criteria you can choose from, including:
How To Add A Drop Down List In Google Sheets And Excel Excel List
  • Any value – anything goes
  • Whole number – only accepts whole numbers
  • Decimal – allows decimals
  • List – select values from a predetermined list or enter your list separated by commas
  • Date – only accepts dates
  • Time – only accepts time formats
  • Text length – only accepts text of a specific length or range
  • Custom – use a formula

Tip: If you money is getting a little tight, try one of these budget templates for Google Sheets.

Since we’re creating drop-down lists, the only option you need to worry about is “List.”

  1. Select “List” and enter your value range (if you have a predetermined list on the same sheet or another tab in the workbook), or enter each list item separated by a comma. If entering a range, either highlight the range on your sheet or enter them starting with an equal (=) sign, such as =E3:E6 or =$E$3:$E$6.
How To Add A Drop Down List In Google Sheets And Excel Excel Valuelist
  1. By default, the “Ignore blank” and “In-cell drop-down” options are checked. They ensure that the cell(s) aren’t left blank, and the drop-down list appears in the cell. You can uncheck these if you want.
  2. Select the “Input Message” tab. You can leave this blank (uncheck “Show Message”) or enter a Title and Message to let the user know what’s expected of them. For instance, my drop-down list lets users select an item location. I entered “Item Location” and “Choose a location” as my Input Message.
How To Add A Drop Down List In Google Sheets And Excel Excel Message
  1. Select the “Error Alert” tab. You can uncheck the “Show Alert” box to not show any kind of alert, select between “Blocking” (all input beyond the drop-down list values is rejected), or “Warning” (input is accepted, but users are warned to choose a value instead).

I usually opt for “Blocking.” After all, the entire reason to add a drop-down list is to restrict values. Enter your Title and Message to let users know what they’re doing wrong.

How To Add A Drop Down List In Google Sheets And Excel Excel Error
  1. When you’re done with all three tabs, click “OK” to save your changes.

You should see a drop-down arrow in the first cell of your range. If you selected an Input Message, this will appear with the arrow.

How To Add A Drop Down List In Google Sheets And Excel Excel Final

If you try to enter an incorrect value and have set up an Error Alert, the alert should display immediately.

How To Add A Drop Down List In Google Sheets And Excel Excel Warning

If you want to edit or remove the list, highlight your cell or range where the drop-down list is used. Go back to “Data -> Data Validation.” Make your changes or select “Clear All” in the “Settings” tab. Press “OK” to erase your drop-down list.

If you’d like to navigate Excel more easily, use these Excel keyboard shortcuts.

Frequently Asked Questions

Can I sort my list items alphabetically, by most commonly used, or any other way I choose?

Yes. If you manually enter the list items in the Data Validation box, place them in the order you want them to appear.

If you’re using a pre-created list on a column in your spreadsheet, sort your data the way you prefer. It’ll automatically change the order in the drop-down menu.

Why do some of my list items have a blank space in front of them?

This usually happens when you manually enter items. While each item needs to be separated by a comma, there should only be a comma between the items. For example, if you wanted to have True and False as your list options, you should enter True,False. Don’t enter a space after the comma. Otherwise, your list may look a little weird.

Is there a limit on the number of items in the list?

Not really. However, you should try to keep the number of items short enough to make it easy for users to pick an item from the list. Lengthy lists are difficult to use.

What if I want to add more values to my list?

When you add a drop-down list in Google Sheets and Excel, you may not anticipate the need for extra values, but there are two ways to add new items.

First, if you’re using a range, right-click any cell in your range, add a new cell, then enter your new value. This should automatically update your range for the drop-down list. Alternatively, highlight your drop-down list range and go to “Data -> Data Validation” and change the range manually to account for the new items on your list.

If you created a manual list within the Data Validation window, highlight your drop-down list cells and go to “Data -> Data Validation.” Add items to your existing list, and save your changes. You can also link data between your spreadsheets by following this tip.

Image credit: Unsplash

Crystal Crowder
Crystal Crowder

Crystal Crowder has spent over 15 years working in the tech industry, first as an IT technician and then as a writer. She works to help teach others how to get the most from their devices, systems, and apps. She stays on top of the latest trends and is always finding solutions to common tech problems.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox