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.
Content
Add a Drop-Down List in Google Sheets
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 to create your list.
Select a single cell or range of cells where you want to add your drop-down list. Select the Data -> Data Validation.
A new window appears to the right. Click Add rule to add a data validation rule. Verify that the cell range is correct. If it’s not, enter it manually, or exit this window, and highlight your range again.
In the Criteria section, select the type of rule. Dropdown is the default. Manually enter options, or if you already have a list of options elsewhere on your spreadsheet, select Dropdown (from a range), and enter the range. You can also choose a checkbox, different types of text, dates, and more. For my example, I’m using Dropdown (from a range).
Click Advanced options just below your criteria. Select any types of warnings or suggestions to help users know what to enter. Select Done when you’re finished.
In the cells with drop-down arrows that allow you to choose to add the list, click the arrow to select an option.
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.
If you need to remove or edit the data validation, go to Data -> Data validation, select your rule, and make your changes or select Remove rule.
If you want to further customize your lists, automatically change the color of the cell based on the chosen input. Alternatively, use these Google Sheets formulas to automate common tasks.
Add a Drop-Down List in Excel
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.
Highlight the cell or range where you would like to add your drop-down list. Select the Data menu, and choose Data validation in the Data Tools section.
Just as with Google Sheets, there are multiple criteria to choose from, including:
- 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
Since we’re creating drop-down lists, the only option you need to worry about is List.
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.
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.
Select the Input Message tab. 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.
Select the Error Alert tab. Uncheck the Show Alert box to not show any kind of alert Or, select between:
- Stop – users can’t enter anything else
- Warning – users are warned, but can still choose to enter something else
- Information – users can enter anything they like, but are shown a custom message
I usually opt for Stop. 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.
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 select an Input Message, this will appear with the arrow.
If you try to enter an incorrect value and have set up an Error Alert, the alert should display immediately.
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, enter True,False. Don’t enter a space after the comma, 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, 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. All screenshots by Crystal Crowder.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe
Crystal Crowder –
Staff Writer
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.
Leave a comment