How to Create a Dropdown List in Google Sheets



Updated December 2024: Stop getting error messages and slow down your system with our optimization tool. Get it now at this link
  1. Download and install the repair tool here.
  2. Let it scan your computer.
  3. The tool will then repair your computer.

If you’ve worked with shared Google Sheets, it can be frustrating for people to enter the wrong data. If you want to make sure that only certain data can be added, you can easily restrict entries using drop-down lists. These drop-down lists can also be an easy way to speed up and streamline data entry.

Data validation helps to ensure consistency when entering information. In Google Sheets, this feature allows you to either select data from a list or compare the data you enter with a specific format. The first function, the intra-cell drop-down list, works well when the potential data entry options for a cell are known and include a list that is not too long. The second feature, data validation, works best when the contents of the cell must meet certain conditions (for example, be a valid date or a valid date before or after a specific date, provide a checkbox, and so on).

How to Create a Dropdown List In Google Sheets

  • You must first open your Google Sheets file and select the cell(s) for which you want to use a drop-down list.
  • Next, open the “Data” menu and select the “Data Validation” command.
  • In the “Criteria” drop-down list, select “Range List” or “Item List”.
  • List from a range: a list of values selected in other cells on the same sheet or on another sheet. For example, if you want to use the values in cells B1-B9 of Sheet 2, enter Sheet2!B1:B9 so that the data it contains appears in the drop-down list, or select one of the cells directly in your sheet.
  • Item list: a list of predefined data items. These can be text or numbers, and you enter each value yourself, separated by commas (and without spaces). This option does not allow you to insert data directly from other cells.
  • Here we use the “List of items” option and offer several numeric choices.
  • After entering the data you want to appear in a drop-down list, be sure to check the option “Show drop-down list in cell”, otherwise the values will not appear in the selected cells.
  • You can also choose what happens if someone tries to enter a value that is not in the list. The “Show Warning” option allows you to enter invalid data, but marks it in the sheet (we’ll just take a quick look at the “How”). The “Refuse entry” option prevents them from entering something that is not in your list.
  • And finally, you can check the “Show validation help text” option to give people a hint as to what they can select in the cells. After selecting the option, type the instructions you want.
  • Continue and click “Save” when you are finished.

December 2024 Update:

You can now prevent PC problems by using this tool, such as protecting you against file loss and malware. Additionally, it is a great way to optimize your computer for maximum performance. The program fixes common errors that might occur on Windows systems with ease - no need for hours of troubleshooting when you have the perfect solution at your fingertips:

  • Step 1 : Download PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista – Microsoft Gold Certified).
  • Step 2 : Click “Start Scan” to find Windows registry issues that could be causing PC problems.
  • Step 3 : Click “Repair All” to fix all issues.

Download Now

To completely remove the drop-down list, select the cell(s) you want to change and click Data > Data Validation > Remove Data Validation.

To Create a Dynamic and Automatically Updated Google Sheet Dropdown List

  • First, we will create a drop-down list based on the domain mentioned, as described in a previous session
  • Then, when we create the name, we enter this formula in the “References to” field:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Where:

Sheet1 – the name of the sheet
A – the column in which our drop-down list is located
$A$3 – the cell that contains the first item in the drop-down list

Copy Dropdown Lists in Google Sheets

If you have a drop-down list in one (or more) cell(s) and want to copy it quickly, you can do so with this simple copy and paste trick.

Here are the steps:

  • Copy the cell that contains the drop-down list you want to copy.
  • Select the cell(s) to which you want to copy the drop-down list.
  • Right-click and go to Paste Special -> Paste Data Validation Only.

The drop-down list is then copied to the selected cells.

Note: You can also do the same thing with a simple copy and paste, but this would also copy the formatting.

https://support.google.com/docs/answer/186103?co=GENIE.Platform%3DAndroid&hl=en



Expert Tip: This repair tool scans the repositories and replaces corrupt or missing files if none of these methods have worked. It works well in most cases where the problem is due to system corruption. This tool will also optimize your system to maximize performance. It can be downloaded by Clicking Here

Scroll to Top