create a drop-down checklist in Google Sheets

Discover ways to use information validation in Google Sheets to enter data quicker, and with fewer errors.

Screenshot of Google Sheet with drop-down options displayed for selected cell (Drop-down item A, Drop-down item B, Drop-down item C, Drop-down item D)

Picture: Andy Wolber/TechRepublic

Inconsistent information can create issues in Google Sheets. A misspelled phrase, an inaccurate entry, or enter error might end in stories—or kinds—which are messy or deceptive. Particularly in bigger lists, in another way entered names (e.g., “touchscreen” or “contact display screen”), numbers (e.g., “10” versus “ten”), or dates (e.g., “January 2020,” “2020-01,” or “Jan. 5, 2020”) can produce issues.

What’s Scorching at TechRepublic

In case you have a Google Sheet crammed with inconsistently entered information, it could take a while to make cell contents constant. A sequence of kinds (Information | Kind) or find-and-replace (Edit | Discover And Change) actions may help rework a jumble of entries right into a standardized set of cells.

Information Validation helps guarantee consistency as individuals enter data. In Google Sheets, the characteristic both lets individuals choose information from a listing or lets the system examine entered information to a specified format. The primary characteristic, the in-cell drop-down checklist, works properly when the potential information entry choices for a cell are each recognized and comprise a not-too-long checklist. The latter characteristic, Information Validation, works greatest when cell content material should meet specified circumstances (e.g., be a legitimate date, or a legitimate date earlier than or after an outlined date, provide a checkbox, and so forth.).

SEE:  wrap textual content in Google sheets (free PDF) (TechRepublic)

You possibly can add Information Validation to a cell in Google Sheets in a desktop-class browser (akin to Chrome on a laptop computer or desktop, or Safari on iPadOS) or within the Google Sheets Android app. The Google Sheets iOS app helps choice of objects from a listing and information validation on entry, however doesn’t embrace the power so as to add new information validations to a cell.

In each circumstances under, you will must open the Google Sheet you wish to edit and choose the cell (or cells) you wish to modify.

create a drop-down checklist entered in a Google Sheets cell

The next steps provide a listing of choices that an individual might choose inside a cell. Chances are you’ll both enter the checklist of choices with the information validation characteristic, or enter the choices in cells elsewhere in your Google Sheet after which level the information validation characteristic to that vary of cells.

1. In a browser, choose Information | Information validation. Within the Android Google Sheets app, faucet the three-vertical dots menu (higher proper), then Information Validation (Determine A).

Determine A

<a href="http://scorpioncenter.com/wp-content/uploads/2020/01/how-to-create-a-drop-down-list-in-google-sheets-1.jpg" goal="_blank" data-component="modalEnlargeImage" data-headline="

Select Information | Information validation in Google Sheets in a desktop-class net browser to create a drop-down checklist or validate entered information.

” data-credit rel=”noopener noreferrer nofollow”> Information validation menu possibility; (proper) Screenshot of validation settings: Cell vary, Standards, On invalid information, Look.” data-original=”http://scorpioncenter.com/wp-content/uploads/2020/01/how-to-create-a-drop-down-list-in-google-sheets-1.jpg”> Information validation menu possibility; (proper) Screenshot of validation settings: Cell vary, Standards, On invalid information, Look.”>

Select Information | Information validation in Google Sheets in a desktop-class net browser to create a drop-down checklist or validate entered information.

Within the Android Google Sheets app, faucet the three-vertical dots menu (higher proper), then Information Validation (Determine B).

Determine B

<a href="http://scorpioncenter.com/wp-content/uploads/2020/01/how-to-create-a-drop-down-list-in-google-sheets-2.jpg" goal="_blank" data-component="modalEnlargeImage" data-headline="

Within the Google Sheets app on Android, faucet the vertical three-dot menu, then faucet Information Validation to configure drop-down objects or validation choices for chosen cells.

” data-credit rel=”noopener noreferrer nofollow”>3 screenshots: (left) tap vertical three-dot menu, (middle) tap Data validation; (right) Data validation options: Cell range, Criteria, Show dropdown list (slider), Appearance (slider), On invalid data (either: Show warning, Reject input).3 screenshots: (left) tap vertical three-dot menu, (middle) tap Data validation; (right) Data validation options: Cell range, Criteria, Show dropdown list (slider), Appearance (slider), On invalid data (either: Show warning, Reject input).

Within the Google Sheets app on Android, faucet the vertical three-dot menu, then faucet Information Validation to configure drop-down objects or validation choices for chosen cells.

2. Subsequent to Standards, choose both Record From A Vary (the default) or Record Of Gadgets.

3. For those who selected Record From A Vary, enter the vary of cells elsewhere in your Google Sheet that accommodates the checklist of things you wish to show as drop-down choices.

4. For those who selected Record Of Gadgets, kind within the drop-down choices precisely as you need them to show, with every merchandise separated by a comma. Within the Android Google Sheets app, faucet Add on the road under the Record Of Gadgets possibility, then enter your checklist with every merchandise separated by a comma.

5. Assessment the On Invalid Information possibility. Whereas the default is to Present Warning, when non-standard information is chosen, you might change it to Reject Enter to make sure information within the cell is legitimate.

6. Optionally, you might choose the checkbox subsequent to Look then enter textual content that explains what kind of cell enter is legitimate. Within the Android Google Sheets app, transfer the slider under Look to the proper, then faucet Edit, enter textual content to clarify what enter is legitimate, then faucet OK. For those who do not do that, when an individual enters information that does not validate, the system shows a regular “There was an issue” message and signifies the cell that violates validation guidelines (Determine C).

7. Choose Save.

Determine C

<a href="http://scorpioncenter.com/wp-content/uploads/2020/01/how-to-create-a-drop-down-list-in-google-sheets-3.jpg" goal="_blank" data-component="modalEnlargeImage" data-headline="

Allow the Look possibility and add validation assist textual content (high). When individuals enter incorrect information, this textual content conveys how the entry might should be corrected. With out the assistance textual content enabled, a generic message shows after an invalid entry (backside).

” data-credit rel=”noopener noreferrer nofollow”>Screenshots: (Top, left) Shows validation help text checked, arrow points to (Top, right) Message "Enter a date between 1/2010 and 12/2026". (Bottom, left) Shows validation help text not checked, arrow points to (Bottom, right) Message "The data you entered in cell C3 violates the data validation rules set on this cell".Screenshots: (Top, left) Shows validation help text checked, arrow points to (Top, right) Message "Enter a date between 1/2010 and 12/2026". (Bottom, left) Shows validation help text not checked, arrow points to (Bottom, right) Message "The data you entered in cell C3 violates the data validation rules set on this cell".

Allow the Look possibility and add validation assist textual content (high). When individuals enter incorrect information, this textual content conveys how the entry might should be corrected. With out the assistance textual content enabled, a generic message shows after an invalid entry (backside).

validate information entered in a Google Sheets cell

  1. In a browser, choose Information | Information validation. Within the Android Google Sheets app, faucet the three-vertical dots menu (higher proper), then Information Validation.
  2. Subsequent to Standards, choose any possibility apart from Record From Vary or Record Of Gadgets. The displayed choices range. On the net, the system affords Quantity, Textual content, Date, Customized Components Is, and Checkbox. Within the Android Google Sheets app, you might choose from a for much longer checklist of choices.
  3. Chances are you’ll must specify a number of values. For instance, if you happen to select to validate {that a} date happens inside a variety, the system will immediate you to enter two date values–one for the beginning of the vary and one for the tip of the vary. Different choices might equally require you to specify a validation worth or formulation.
  4. Assessment the On Invalid Information possibility. Optionally, you might change it to Reject Enter (from the default of Present Warning) to make sure legitimate information entry. 
  5. Normally, it would be best to add explanatory textual content to convey doable legitimate values to individuals who enter information. To do that, choose the checkbox subsequent to Look, then enter textual content that explains what kind of cell enter is legitimate. Or within the Android Google Sheets app, transfer the slider under Look to the proper, faucet Edit, enter textual content to clarify what enter is legitimate, then faucet OK. With out this extra data, an individual who makes an attempt to enter information might not essentially know what enter will likely be accepted as legitimate.
  6. Choose Save.

What’s your apply? 

For those who use Google Sheets, do you employ the Information validation characteristic to make sure legitimate information entry—particularly on shared sheets? And, if you happen to enter information within the Google Sheets cell app on Android or iOS, does a drop-down checklist make information entry quicker and extra environment friendly? Let me know the way you employ Sheets’ information validation options, both with a remark under or on Twitter (@awolber). 

Additionally see

Leave a Reply

Your email address will not be published. Required fields are marked *