How to Add a Dropdown List in Google Sheets and Excel

If you want to clean up your data entry process, you'll definitely want to know how to add a drop-down list to Google Sheets and Excel. This allows you to restrict a user's ability to enter data into a specific cell to a set of options. You don't have to worry about typos or someone not knowing what to enter. Plus, it allows you to enter information faster.

Good to know: You can Spreadsheet automation Also using formulas.

Add a drop-down list in Google Sheets

There are multiple ways to create your own value list for your dropdown list in Google SheetsSome users prefer to create a hidden column with values from their existing sheet or from another sheet in the same file. Others prefer to add items later in the process. Either of these options also works for creating your list.

1. Locate single cell Or range of cells Where you want to add your dropdown menu.
2. Select a list "Data".
   
3. Locate Data validation.
   
4. Verify the range is correct. You can set it manually or exit the window and select the range you want first.
5. Define your criteria. By default, they are: List of range. However, you can also use:
   1. List of items - Add each entry in the box separated by commas.
   2. Number - Only digital input is allowed, but you can specify the range.
   3. Text - It only accepts text input based on your criteria, such as containing only certain characters or words, or only URLs or email addresses.
   4. History - Accepts dates only. Custom Formula – Create a custom formula for the dropdown list.
   5. Checkbox - Add a simple checkbox, true/false values, or custom values. In this example, I'm using "List from Range."
6. Enter the data range that includes your list values. I have a few columns of my master data so I can hide the column later.  
7. Specify how to handle invalid input. Choose between Show warning , which warns users that a valid element is being used before allowing them to enter what they want, and “Entry refused” , who rejects anything off the menu.
8. Click "save".

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

Users simply click the arrow to select an option.

You have set the drop-down list to reject input. If someone enters any other value, you will see a warning, and their input will be rejected.

If you ever need to remove or edit data validation, highlight the cell or range using the drop-down menu and go to Data -> Data Validation. Make changes or select Remove Validation.

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 slightly different.

1. Highlight the cell or range to which you want to add the drop-down list.
2. Select a list "Data".
   
3. Locate Data validation.
   
4. Just like with Google Sheets, you have multiple criteria to choose from, including:
   - Any value - Anything goes
   - Integer - Accepts only integers
   - Decimal Decimal fractions allowed
   - existing - Select values from a predefined list or enter your own comma-separated list.
   - the date - Accepts dates only
   - the time - Only accepts time formats.
   - Text length – Only accepts text of a certain length or range.
   - Custom – Use the formula
   Since we're creating dropdowns, the only option you need to worry about is "List".
5. Locate "existing" Enter a range of values (if you have a predefined list on the same sheet or another tab in the workbook), or enter each list item separated by a comma. If you are entering a range, either highlight the range on the sheet or enter it starting with an equals sign (=), such as =E3:E6 or =$E$3:$E$6.
   
6. By default, both options are selected. "ignore the void" و drop-down list in cell. This ensures that the cell(s) are not left blank, and the drop-down list will appear in the cell. You can deselect this if you want.
7. Select tab "Input message"You can leave this blank. (Uncheck “Show message”) Or enter a title and message to let the user know what to expect from them. For example, a drop-down menu allows users to locate the item. Element location و “Choose a location” As my input message. 
8. Select tab Error alertYou can uncheck the box. Show alert To not show any kind of alerts, or choose between “Prohibit” (All entries that exceed the drop-down list values are rejected) , Or “Warning” (Input is accepted, but users are warned to choose a value instead)I usually choose. "Ban". After all, the whole reason for adding a drop-down list is to restrict values. Enter a title and message to let users know what they're doing wrong.   
9. When you are finished 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, it will appear with the arrow.

If you try to enter an incorrect value and set up an error alert, the alert should appear immediately.

If you want to edit or remove the list, highlight the cell or range in which you are using the drop-down list. Go back to Data -> Data ValidationMake changes or select “Clear all” in the tab "Settings". Click on "OK" To clear the drop-down list.

If you want to navigate Excel more easily, use Excel keyboard shortcuts This is amazing.

Frequently Asked Questions

Q1: Can I sort list items alphabetically, by most common use, or any other way I choose?

The answer: Yes. If you manually enter 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 in a column in your spreadsheet, sort your data however you like. The order will change automatically in the drop-down list.

Q2: Why is there a blank space in front of some list items?

The answer: This usually happens when you enter items manually. While each item should be separated by a comma, there should only be a comma between items. For example, if you wanted to have True and False as your list options, you would enter True, False. Don't enter a space after the comma. Otherwise, your list might look a bit odd.

Q3: Is there a limit to the number of items in the list?

The answer: Not true. However, you should try to keep the number of items short enough to make it easy for users to select an item from the list. Long lists are difficult to use.

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

The answer: When adding a drop-down list in Google Sheets and Excel, you might not expect to need additional values, but there are two ways to add new items.

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

If you created a manual list within the Data Validation window, highlight the drop-down list cells and go to Data -> Data Validation. Add items to your existing list and save your changes.