By default, entries into unlocked cells in Google Sheets care freeform. You and any editors you share a Sheet with can type or paste any numbers, letters, or symbols into an unlocked cell.
Users can even enter images into Google Sheets cells.
One of the aspects of spreadsheets that makes them different from database applications is the lack of rigor.
But it is possible to enforce user entry of only specific values in Google Sheets cells by adding an in-cell dropdown list.
Steps for creating and using a dropdown list
Our recommended approach is to first create a new sheet within an existing Google Sheets file. Name the sheet “Dropdown Values.”
We’ll use business income and expense accounts as an example.
Add income and expense values to Column A. We have entered values in the A1 to A15 range.
In a separate sheet within the same file used to track income and expense line items, add a column with a header name of “Account.”
Select the cell under the column header.
Add data validation to this cell by selecting Data > Data validation from the menus.
In the Data validation dialog:
- Select the “List from a range” criteria
- Enter the range using =’Dropdown Values’!$A$1:$A$15
- Click the “Show dropdown list in a cell” checkbox option
- Select the “Reject input” radio button
- Click the “Show validation help text:” checkbox
- Add help text such as “Please select a value from the list”
When you click into the cell, you will see the dropdown list. Make a selection from the list.
If a Sheet editor types something into the cell rather than selecting a value from the list, they will see the following dialog.
Finally, copy cell D2 down as many cells as needed. You can also copy down a cell after making a selection, which is convenient when you have sorted the sheet by Column B, as we have in this example.
You are now ready to use the dropdown list for yourself and other editors of the Google Sheet.
You can create multiple drop-down lists within a single spreadsheet.