how to add drop down list in google sheets

Learn how to use data validation in Google Sheets to enter information faster and with fewer errors.

Screenshot: Andy Wolber/TechRepublic

Inconsistent data can create problems in Google Sheets. A misspelled word, an inaccurate entry, or input error may result in reports—or sorts—that are messy or misleading. Especially in larger lists, differently entered names (e.g., "touchscreen" or "touch screen"), numbers (e.g., "10" versus "ten"), or dates (e.g., "January 2020," "2020-01," or "Jan. 5, 2020") can produce problems.

If you have a Google Sheet filled with inconsistently entered data, it may take some time to make cell contents consistent. A series of sorts (Data | Sort) or find-and-replace (Edit | Find And Replace) actions can help transform a jumble of entries into a standardized set of cells.

Data Validation helps ensure consistency as people enter information. In Google Sheets, the feature either lets people select data from a list or lets the system compare entered data to a specified format. The first feature, the in-cell drop-down list, works well when the potential data entry options for a cell are both known and comprise a not-too-long list. The latter feature, Data Validation, works best when cell content must meet specified conditions (e.g., be a valid date, or a valid date before or after a defined date, offer a checkbox, etc.).

SEE: How to wrap text in Google Sheets (free PDF)  (TechRepublic)

You can add Data Validation to a cell in Google Sheets in a desktop-class browser (such as Chrome on a laptop or desktop, or Safari on iPadOS) or in the Google Sheets Android app. The Google Sheets iOS app supports selection of items from a list and data validation on entry, but does not include the ability to add new data validations to a cell.

Impressively, you also may add drop-down options to a native-format Excel file with Google Sheets. If you open an XLSX file in Sheets--either in a desktop-class browser or in the Sheets Android app--the Data Validation feature is available, just as it is in a native Google Sheet. Your Excel file will retain its native XLSX format even after you make edits to it within Google Sheets. (If you prefer to add a drop down menu in Excel, see How to add a drop down list to an Excel cell. For more Excel tips, check out 56 Excel tips every user should master.)

In both cases below, you'll need to open the Google Sheet you want to edit and select the cell (or cells) you want to modify.

How to create a drop-down list entered in a Google Sheets cell

The following steps offer a list of options that a person may select within a cell. You may either enter the list of options with the data validation feature, or enter the options in cells elsewhere in your Google Sheet and then point the data validation feature to that range of cells.

1. In a browser, select Data | Data validation. In the Android Google Sheets app, tap the three-vertical dots menu (upper right), then Data Validation (Figure A).

Figure A

Choose Data | Data validation in Google Sheets in a desktop-class web browser to create a drop-down list or validate entered data.

In the Android Google Sheets app, tap the three-vertical dots menu (upper right), then Data Validation (Figure B).

Figure B

In the Google Sheets app on Android, tap the vertical three-dot menu, then tap Data Validation to configure drop-down items or validation options for selected cells.

2. Next to Criteria, select either List From A Range (the default) or List Of Items.

3. If you chose List From A Range, enter the range of cells elsewhere in your Google Sheet that contains the list of items you want to display as drop-down options.

4. If you chose List Of Items, type in the drop-down options exactly as you want them to display, with each item separated by a comma. In the Android Google Sheets app, tap Add on the line below the List Of Items option, then enter your list with each item separated by a comma.

5. Review the On Invalid Data option. While the default is to Show Warning, when non-standard data is selected, you may change it to Reject Input to ensure data in the cell is valid.

6. Optionally, you may select the checkbox next to Appearance then enter text that explains what type of cell input is valid. In the Android Google Sheets app, move the slider below Appearance to the right, then tap Edit, enter text to explain what input is valid, then tap OK. If you don't do this, when a person enters data that doesn't validate, the system displays a standard "There was a problem" message and indicates the cell that violates validation rules (Figure C).

7. Select Save.

Figure C

Enable the Appearance option and add validation help text (top). When people enter incorrect data, this text conveys how the entry may need to be corrected. Without the help text enabled, a generic message displays after an invalid entry (bottom).

How to validate data entered in a Google Sheets cell

1. In a browser, select Data | Data validation. In the Android Google Sheets app, tap the three-vertical dots menu (upper right), then Data Validation.
2. Next to Criteria, select any option other than List From Range or List Of Items. The displayed options vary. On the web, the system offers Number, Text, Date, Custom Formula Is, and Checkbox. In the Android Google Sheets app, you may select from a much longer list of options.
3. You may need to specify one or more values. For example, if you choose to validate that a date occurs within a range, the system will prompt you to enter two date values--one for the start of the range and one for the end of the range. Other options may similarly require you to specify a validation value or formula.
4. Review the On Invalid Data option. Optionally, you may change it to Reject Input (from the default of Show Warning) to ensure valid data entry.
5. In most cases, you will want to add explanatory text to convey possible valid values to people who enter data. To do this, select the checkbox next to Appearance, then enter text that explains what type of cell input is valid. Or in the Android Google Sheets app, move the slider below Appearance to the right, tap Edit, enter text to explain what input is valid, then tap OK. Without this additional information, a person who attempts to enter data may not necessarily know what input will be accepted as valid.
6. Select Save.

What's your practice?

If you use Google Sheets, do you use the Data validation feature to ensure valid data entry—especially on shared sheets? And, if you enter data in the Google Sheets mobile app on Android or iOS, does a drop-down list make data entry faster and more efficient? Let me know how you use Sheets' data validation features, either with a comment below or on Twitter (@awolber).

Google Weekly Newsletter

Learn how to get the most out of Google Docs, Google Cloud Platform, Google Apps, Chrome OS, and all the other Google products used in business environments. Delivered Fridays

Sign up today

Also see

• Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
• DIY: Add drop-down lists to LibreOffice spreadsheets (TechRepublic)
• How to reduce data input and typos in Excel (TechRepublic)
• Google brings new AI, machine learning features to Sheets (ZDNet)
• It takes work to keep your data private online. These apps can help (CNET)
  
• Microsoft Office vs Google Docs Suite vs LibreOffice (Download.com)
  
• How-To Tips: More easy-to-follow tutorials (TechRepublic on Flipboard)

how to add drop down list in google sheets

Source: https://www.techrepublic.com/article/how-to-create-a-drop-down-list-in-google-sheets/

Posted by: flowersdowanceares.blogspot.com

Share this post