Good to know: you can automate your spreadsheets, too, with formulas.
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 for creating your list.
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.” You should now see drop-down arrows in any cells where you chose to add the list. Users just 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 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.
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.
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.” 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. 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. 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. Image credit: Unsplash 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. 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.
